You’d think they’d make it easier

I am often in need of the separate components of regression function. For instance, differences in utility functions can be easily measured by comparing the exponents of a the functions’ power regressions. Excel can calculate the power regression and place the line on a chart. For some reason though, they did not give an easy way to get that data in a formula. If I felt like programming my own functions I imagine =powreg(x’s,y’s,[desiredDataToReturn]) would definitely be in there. Since it’s not I’m usually forced to go to Google. Unfortunately this appears to not be to wide-spread of a problem, or any easy solution exists that I am not aware of, because I am always at a loss even after a Google search. Of course Excel help is useless. They can’t bother to document that functions have more uses than they appear (=ln() is also an array function? I guess you need to buy a book to learn these things).

At first I was forced to look up and convert the power law equations into an Excel function. If you are looking for practice with Excel, have fun; here’s the equation for the exponent in a power function:

Power law equation for exponent

It would be easy if Excel had a better way to manage parentheses

Just kidding. Here’s the formula: =(count(x array)*sum(ln(x array)*ln(y array))-sum(ln(x array)*sum(ln(y array))/(count(x array)*sum(power(ln(x array),2))-power(sum(ln(x array),2)). Then hit CTRL+Shift+Enter NOT just enter. Quick explanation, ctrl+shift+enter enters the sums and natural logs as array functions which is how it calculates xi instead of just x. You can check the result against a trendline inserted in a chart.

Since that way is really no fun and typing that out (or even now copy and pasting and then editing the arrays) is excruciating, I decided to see what else Excel has to offer. The =linest() function looks like it’s no help at first since it only does a straight line regressions, but then I thought, “What if I just change the space the regression is playing in?” To skip some 12th grade calculus (I really don’t even know if what I’m doing is calculus), I’ll simply say a linear regression of on a log scale is a power regression in the linear scale. Simply put take the natural log of all your coordinates when plugging them into your formula.

=linest(ln(x array),ln(y array),false,false)

This formula gives you a power regression where =linest(x array, y array, false,false) gives you the linear regression. If you want the exponent, you could do the ctrl+shift+enter approach I mentioned above, but if you like using Excel arrays in a more user-friendly format, use the =index() function. The exponent is the first index (or 0 since it’s a 0-based array) and the coefficient is the second index.

=index(linest(ln(x array),ln(y array),false,false),0)

This gives you the exponent. Below is the coefficient. You have to inverse the coefficient (take it out of the log scale and back to the linear scale) first though.

=exp(index(linest(ln(x array),ln(y array),false,false),1)

Easy enough. I suppose programming a separate formula for this and every other regression style is superfluous, but why couldn’t Microsoft just put this in their Help document. It literally adds maybe 2 lines, as opposed to the 100 lines you’ve read here if you go this far.


Single Post Navigation

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: