Forum Discussion
How to set the coefficient b for a power function
I have attached a spreadsheet that solves the problem by using Excel Solver function and minimizing the error sum of squares.
Hmm...that's significantly more complicated than what I was thinking of... I was talking more about if there was a feature for the power trendline that lets you change the value of b to what you want it to be, then adjusts the value of a and chi-square accordingly. Is there something like that or can it only be done using data manipulation? I figure it's Excel, so there has to be an easier way than that...
Also yes, you are correct in your assumptions that it would need to be a least-squares fit.
(Not sure this posted, second attempt, sorry if it is redundant)
Hello:
There is no methodology that I know of that makes statistical sense. Of course you can do anything you want but that is quite different from following statistical protocol. Thus there is no Excel function that does that, nor should there be. What I suggested made no statistical sense but would solve the problem you ask about. There are probably a million ways to make an intercept once you stop being concerned about statistical protocol. Once you modify the output from a statistical procedure none of the statistical mean a thing. They are based on the function being estimated by following a set procedures.
Also I don't why you would use chi-square for anything associated with regression and correlation perhaps you meant R-squared which would have no meaning once you changed the "b" to some arbitrary amount. Since you "know the correct b" why not just solve for "a" by: a=Y/(X^b). Just choose any pair of Y & X. Since you know "b" it should produce the correct "a."
