# FW: Performance (curve) anxiety

1 post / 0 new

Bill,

If you have enough data points you should be able to generate similar curves in excel using the LINEST command. It spits out the same trendline coefficients that you would get if you graph data then add a trendline.

It's set up like this :

=LINEST(y-values,x-values,true,true)

This will spit out the m and b in y=mx+b.

Cubic looks like this

=LINEST(y-values,x-values^{1,2,3),true,true)

This will give you y=ax^3+bx^2+cx+d

You can also use it to do bi-quadratic, which looks like this:

=linest(y-values,x1-values^{0,0,1,2)*x2-values^{1,2,0,0),true,true)

The form here is : y = a*x1+b*x1^2+c*x2+d*x2^2+e

If you want to add a x1*x2 term you change the x value definition to : x1-values^{0,0,1,1,2)*x2-values^{1,2,1,0,0).

I think it's the same idea for bi-linear but I haven't tried it.

I was looking into how to do this a couple weeks ago and there is a decent amount of info on excel msg boards on how to generate complex regressions. Also, it has to be entered as a array, and the 2nd true statement allows it to spit out statistics about the curve fit so you can check the R2 value.

Hope that helps.

Brendan Hall, PE, LEED AP BD+C

Offline
Joined: 2011-09-30
Reputation: 1