10 replies to this topic

[CHEMSTRONG]

Dear Sir,

 

I have distillation data and specific gravity details for Some Grades of Oil like 7 diff Grades of Oil available with me. I want to fit Curve Fitting mathematical equation for this data to get final equation for each grade of oil. So When user chooses any grade of oil from dropdown, here, i want to select any equation which is fitted for the data for that oil .

 

How to do this in excel ?? Please advice on this from all expert users now.

 

Thanks.

 

Best Regards,

CHEMSTRONG 

[MrShorty]

When I first got into this field, my mentor was one who did not like open ended questions about how to do things (like "here is my problem statement how do I do it in Excel?"). He strongly preferred that make some kind of effort towards at least a proposal of what I thought would be appropriate, and then he would teach me from there. I expect that at least part of this was because putting my proposal together allowed him to really see what I already understood and where my knowledge was lacking so he could focus his teaching where I lacked. I would suggest that a similar thing is going here. We have no idea what your skill level with Excel is, nor any of the details of how you intend to solve the overall problem. Helping us understand what you know and don't know will help us put together a response that will be most helpful to you.

 

That said, a couple of things to look at:

 

Excel has two tools for regressions -- LINEST() for linear and linearizable regression functions where and ordinary least squares (OLS) algorithm is appropriate and Solver for non-linear regression functions and situations where OLS is inappropriate. I expect that the first step is deciding what kind of regression algorithm you want to use for your distillation curve.

 

The other main part is the "dropdown" which sounds like a lookup type of operation. Are you familiar with Excel's lookup function and how to arrange a lookup table in Excel?

[Bobby Strain]

We all should boycott your endless queries. I for one will quit looking at them.

 

Bobby

[CHEMSTRONG]

Dear MrShorty Sir,

 

Thanks a lot for your support uptill now.

 

I know Excel VBA Coding and did it for one task like macro development, excel automation etc., But never did statistical functions in excel etc. 

 

My problem statement is 

 

1) I have Liquid Hydrocarbon oil Distillation data like Percent Distilled Volume Distilled and against that values of Temperature Degree C. 

Eg 10 percent Distilled at 150 DEg C etc. Using these values, and process simulation software , we define distillation data under components tab to get approximate composition of Liquid Oil here. this is composition in the form of NBPs only.

 

Hope you got my my data points here. For set of values of Distillation data , i have got Feed Composition of oil and  components will be around 20 in  numbers.

 

 

 

How to fit this in equation or fit the curve to this data ? Below is 1 set of values , like this i have got multiple values of data sets for which i need to fit that into equation now.

 

 

 

 

Regards,

CHEMSTRONG 

[CHEMSTRONG]

I am not able to paste any values here now.

 

Please advice if yo got my point.

[MrShorty]

I don't see any reason to need VBA for this, standard Excel worksheet functions should do fine for everything (except the Solver part if you decide you need a non-linear regression algorithm). At this point, you have not specified your desired curve fitting algorithm nor your desired regression equation. Without those kind of details, I'm not sure I can say much more than I've already said -- use LINEST() (help file: https://support.micr...&rs=en-us&ad=us) if you have a linear function and you want to use an ordinary least squares algorithm for the regression. Or build a spreadsheet that will use Solver for a non-linear regression algorithm. But I don't know if I can choose between the two approaches for you.

[CHEMSTRONG]

I want to first use simpler method like linear regression here.

Please let me know on this.

 

Regards,

CHEMSTRONG

[MrShorty]

Did you follow my link to Excel's LINEST() function help file? For basic straight line y=mx+b regressions, I have long felt that MSFT's help file does pretty good at explaining how to use LINEST(). There is a short section that gives an example for polynomial regressions. Have a look at the help file and let us know what you are having trouble with.

[breizh]

Hi,

To be frank , I'm very surprised with the query , I should say with all the queries from Chemstrong .  Is he an engineer or a student?

He seems that he has access to plenty of software (aspen, Chemcad ,...) and very little knowledge about excel . Weird to me. 

Back to this simple query, you don't even need to use linest , just to issue a table under excel , select it to get a graph clicking on the icons related to graphs . By clicking on the curve you can get access to different equations (regression)  together with the display of equation , R^2 ,if selected .

Good luck

Breizh 

[SilverShaded]

Don't fit a curve, fit a probabilty plot.

[FRBChemE]

Just what you want may already have been done.  Consult

  "Pseudocomponent Breakdown by Integral Method assuming constant Kw" in Hydroprocessing , January 1994 Page 100.  

This will be easy to find if you are a student and your school has a decent library.  

Unfortunately the method is in the form of a "Basic" program.  However I have a version transformed into Excel Spreadsheet which seem to work OK.  Let me know if your still interested.  Maybe I can post it or E-mail it to you.

 

There is a free Process Simulation program, DWSIM which can do regression for D-86 type of Oil characterizations. This approach is useful if you need to do something with the properties of the oil.