Help with Multiple Level Regression

**rhuseman89** · 01-07-2016, 01:55 PM

I need help with a Regression Problem I have. I have two independent variables (Amperage and Diameter) and one dependent variable (Temperature). I would like to be able to have one equation that predicts the dependent variable. None of the relations ships are linear, but they are some what exponential. I have limited amount of data collected, but it should yield enough to make a prediction. I have attached both a graph showing the results and the data. The results do not have to be to a certain confidence level, as I am only looking for a ball park equation. I am looking for advice on using excels regression tool. Ex. I would like to predict the temperature of a .175 diameter at 30 amperage with the equation.

**MrShorty** · 01-07-2016, 02:20 PM

Is there a specific part of this kind of regression problem that you need help with? Here's how I work through most of my regression problems:

1) Choose the desired regression equation. This is often the most difficult part. It seems like you are not even sure what kind of equation you want. Even for "rough" estimations, I like to research the equations that go into the phenomenon I am trying to analyze so I at least have an idea of what kind of equation should be used. This page may be useful to you: https://wiki.xtronics.com/index.php/...tive_heat_loss

2) Once the desired equation is chosen, then I choose a regression algorithm.
2a) If the equation is "linear" (in the broader linear algebra sense), then I will usually use the LINEST() function to perform the regression.
2b) If the equation is "non-linear" and I cannot or choose not to linearize it, then I will set up a Solver model to find the parameters of the equation.

3) Evaluate regression to see if it will be suitable.

With that overview, what part do you need help with? If you need help with (1), I can probably only help by putting your search terms into Google, which I assume you can do, too. Once you decide on an equation, I should be able to help with step 2.

**rhuseman89** · 01-07-2016, 03:45 PM

MrShorty,

Well, theoretically I think it would all be based off of
Newton's Law of Cooling. Q = h SA (T2-T1),
Newton's Law of Heating Q = R I²,
surface area of a circle SA = PI D,
Ohms Law V = I R,
area of a circle A = PI R² and
resistance of a wire R = p L / A.

Assumptions that can be made:
h = constant for free convection of air
p = constant for steel conductivity
T2 = constant room temperature and should be 70 F

Solving for T1 = T2 + (I²*(SQRT((DIA/2)²*PI)/PI)/(2*h*PI).

I think that is the equation I could use to do Step 1 in your process. I'm sorry for making this super complex. How do I take that to excel and do step 2?

**MrShorty** · 01-07-2016, 04:16 PM

1) Identify your variables in this equation and kind of separate them out.
T1=T2+I^2*SQRT((DIA/2))^2*SQRT(pi)/pi/2/h/pi (double check my algebra, if needed)

making some substitutions: y=T1 x=I^2*DIA and this equation will simplify to:
y=m*x+b where m is that mix of constants attached to I^2*Dia and b is T2.

This suggests that a possible "1st guess" at the regression function might be T1=T2+m*(I^2*Dia).

Now, you will need to rearrange your nice table into a nice list:

Please Login or Register  to view this content.

With the data in a list like this, enter the LINEST() function (or use the SLOPE() and INTERCEPT() functions https://support.office.com/en-us/art...__toc309306716 ) to get m and b.

Then, compute the estimated T1's from the regression and see if you like the results. If you like the results, then you are done. If not, then you go back to step 1 and rethink.

It might be worth considering whether to fix T2 (b) at 70 or not. If you decide to do this, your y substitution becomes T1-70=y, and T2 is removed from the right side equation, yielding y=m*x as your regression equation.