3 variables fitting

[mohandespower2010]

I am an electrical engineering. I have data sets that describe the relation between three variables. I want to do a fit to define a function that will describe the three variables:

y = f(x1,x2)

How would I go about doing this? Thanks in advance for any help.

Ahmed

[MrShorty]

If f() is "linear" (in the broader sense that you may have learned in a linear algebra course), then you can use the LINEST() function to perform a least squares regression: https://support.office.com/en-us/art...a-fa7abf772b6d

If f() is "non-linear", and you cannot manipulate it to make it linear, then you will need to use non-linear regression techniques. This will involve computing an "objective function", then calling Solver to optimize the objective function by changing the functions parameters.

Do you know what kind of function f() should be?

[Kaper]

Have you choosen exponential trendline because of some theory behind or your supervisor suggestion?
Especially for the second graph (Salinity) linear trend seems to be very obviuos choice.
Try displaying for your trendlines equations and Rsquared values. They are much better (closer to 1) in case of linear trend.

[mohandespower2010]

Thanks MrShorty for your attention,
The function is non-linear.
How can i compute the objective function? and i don't have any idea about the solver in excel or how to use it

If f() is "linear" (in the broader sense that you may have learned in a linear algebra course), then you can use the LINEST() function to perform a least squares regression: https://support.office.com/en-us/art...a-fa7abf772b6d


If f() is "non-linear", and you cannot manipulate it to make it linear, then you will need to use non-linear regression techniques. This will involve computing an "objective function", then calling Solver to optimize the objective function by changing the functions parameters.

Do you know what kind of function f() should be?

[MrShorty]

The first step in computing the objective function is to choose one. A typical "least-squares" objective function will be sum[(known_y-equation_y)^2] or, in Excel, the SUMXMY2() function: https://support.office.com/en-us/art...4-e2ecee23b299 You will start by "guessing" some reasonable parameters for f, then use Solver to minimize the sum of squares by changing those parameters.
My typical spreadsheet will look like:

Please Login or Register  to view this content.

Calling Solver should be as easy as following instructions here: https://support.office.com/en-US/art...d-078f96d1652c

Let us know what you have trouble understanding or where you get stuck.

[mohandespower2010]

Thanks Mr Kaper

Simplifying my question ,
I need to make the three curves in the graph (either PVC CONTENT in EPDM OR Salinity) to be one curve with one function represents all the three curves

Have you choosen exponential trendline because of some theory behind or your supervisor suggestion?
Especially for the second graph (Salinity) linear trend seems to be very obviuos choice.
Try displaying for your trendlines equations and Rsquared values. They are much better (closer to 1) in case of linear trend.

[MrShorty]

To update others, after a few PM's here's where we are at:

1) These numbers are "breakdown voltages" vs. PVC/EPDM ratio at different salinities. I'm not sure what the salinity is referring to (I would guess the insulator/polymer is in contact with an aqueous salt solution, but not sure). If there are any electrical engineering types on the forum who would know how breakdown voltages are typically modeled, it would help us understand what to use for the regression function.

2) In the last PM, you indicated that you did not care what kind of function was used. So, in order to get things moving, let's start with the simplest function that comes immediately to mind -- a plane. y=m1*x1+m2*x2+b. You will need to become familiar with the LINEST() function, since chart trendlines cannot handle 3D data like this: https://support.office.com/en-us/art...a-fa7abf772b6d Note that example 3 specifically shows a multi-variable regression, so you should become familiar with how they did this example.

3) The LINEST() function needs to have your data arranged in a list rather than a table. This should look something like:

Please Login or Register  to view this content.

4) Enter the LINEST() function. Remember that this is an array function, so it needs to be committed with ctrl-shift-enter.

5) Evaluate regression to see if it is adequate.

This should give us a starting place.