## Featured Articles

Check out the latest featured articles.

## New Article

Product Viscosity vs. Shear

## Featured File

Vertical Tank Selection

## New Blog Entry

Low Flow in Pipes- posted in Ankur's blog

3

# Distillation Data Curve Fitting

9 replies to this topic
|

### #1 CHEMSTRONG

CHEMSTRONG

Gold Member

• Members
• 308 posts

Posted 29 April 2022 - 08:17 AM

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

### #2 MrShorty

MrShorty

Gold Member

• ChE Plus Subscriber
• 507 posts

Posted 29 April 2022 - 11:53 AM

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?

### #3 Bobby Strain

Bobby Strain

Gold Member

• Members
• 3,180 posts

Posted 29 April 2022 - 11:12 PM

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

Bobby

### #4 CHEMSTRONG

CHEMSTRONG

Gold Member

• Members
• 308 posts

Posted 30 April 2022 - 10:40 PM

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

### #5 CHEMSTRONG

CHEMSTRONG

Gold Member

• Members
• 308 posts

Posted 30 April 2022 - 10:42 PM

I am not able to paste any values here now.

### #6 MrShorty

MrShorty

Gold Member

• ChE Plus Subscriber
• 507 posts

Posted 02 May 2022 - 11:28 AM

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.

### #7 CHEMSTRONG

CHEMSTRONG

Gold Member

• Members
• 308 posts

Posted 02 May 2022 - 07:08 PM

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

Please let me know on this.

Regards,

CHEMSTRONG

### #8 MrShorty

MrShorty

Gold Member

• ChE Plus Subscriber
• 507 posts

Posted 03 May 2022 - 10:56 AM

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.

### #9 breizh

breizh

Gold Member

• 5,381 posts

Posted 03 May 2022 - 11:23 PM

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

Gold Member

• Members
• 147 posts

Posted 09 May 2022 - 10:37 AM

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