How to set the coefficient b for a power function

[eengel1]

Hello,

I am wondering: does anyone know a way to manually set the coefficient b for a power function best fit line instead of it automatically setting itself for a set of data? I am specifically talking about a function in the form of y = a*x^b. I have a set of data where I already know the relationship between the two quantities, so I already know what the power b is. I need to fit the function such that it is a best fit in terms of the power b that I know, not what Excel automatically tries to set b as when it calculates best fit. Any help would be greatly appreciated. Thanks.

[#2]

@eengel1 

I have attached a spreadsheet that solves the problem by using Excel Solver function and minimizing the error sum of squares.

[#3]

@Pvberg 

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.

[#4]

@eengel1 

(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."