Curve fitting: non-standard

[PatrickW]

Hi, all

I'm trying to use Excel to create a trendline for data that approximates a shifted-power law equation, of the form y=a*(x-b)^c.

The built-in formulae in Excel 2007 aren't able to give adequate results, so I would really like to find a method within Excel of obtaining the coefficients a, b and c.

At the moment I'm stuck with two scenarios - one unacceptable, and the other horribly inelegant, and prone to fail:

1) export data to a third-party application, and try and obtain coefficients from there - not acceptable, since I'm trying to create a spreadsheet that will produce the result of manipulating the curve simply from a single user-input (i.e. the graph information itself)

2) brute-force estimating generic ranges for a, b and c and calculating formula results from inputting these into the formula, and performing a least-squares fit on the resulting data compared with the original - inelegant, since I have coarse arrays of 20 elements in each of a, b and c, yielding thousands of datapoints; and prone to fail, since the estimated arrays could well not be appropriate for the input data

Is there a formula-based solution to this problem? Failing which, is there a VBA solution that will be able to produce coefficients for a shifted-power law equation for any data input (obviously assuming reasonable input data).

I'd appreciate your help - and please ask if I've been unclear, or more information is required.

Thanks

PatrickW

 

[wigi]

Hi there

Assuming b=0, this is rather trivial:

y=a*x^c + error

<==>

ln=ln(a)+c*ln(x) + error

<==>

ln=A+c*ln(x) + error

Hence, estimate A and c using the normal regression functions builtin in Excel after rescaling y and x.

With b<>0, I don't have a straightforward solution.

Wigi

 

[PatrickW]

Hi, Wigi

Thanks for your response - but unfortunately, b<>0, otherwise I'd be perfectly happy getting the coefficients for the resulting straightforward power law equation built-into Excel.

The 3rd-party generated coefficients are:
a = 237.8
b = 120.1
c = -0.32

which fit my data like a glove.

Furthermore, depending on the test methodology used in the experiment, a and b could vary -50% to +100% of the stated values, so the brute-force method I stated earlier needs to become massively unwieldy to be able to get the accuracy required, and also the range of applicability to the different test-types that generate the different input data.

Thanks for your speedy response, though!

 

[wigi]

Hi

Can you use the Solver functionality to find a, b and c above?

If yes, the other experiments could be obtained by programming the Solver in VBA.

 

[PatrickW]

Hi, wigi

I was originally hoping for a formula-based analytical solution to obtain a, b and c, but I've reached the stage where, if the end user needs to click a button that automatically runs and solves the Solver function, that's good enough for me!

Your suggestion works perfectly -

For clarity, what I've done is to set up guess values for a, b and c in 3 cells in the spreadsheet.

Next to the input experimental data for x and y (time and distance) I input a column that calculates new Y values from the same x values, but using the formula Y=a*(x-b)^c with the guessed constants.

Next, I take the difference between the y and Y, square the difference, to ensure the numbers are all positive, then take the sum of all of those differences.

Finally, I have a small piece of VBA that runs Solver, setting the sum cell to a minimum by changing a, b and c.

Works wonderfully.

Thanks for your help!

PatrickW

 

[wigi]

That's exactly how I'd have it set up as well. Congratulations.

 

[PatrickW]

Hi, wigi

I was originally hoping for a formula-based analytical solution to obtain a, b and c, but I've reached the stage where, if the end user needs to click a button that automatically runs and solves the Solver function, that's good enough for me!

Your suggestion works perfectly -

For clarity, what I've done is to set up guess values for a, b and c in 3 cells in the spreadsheet.

Next to the input experimental data for x and y (time and distance) I input a column that calculates new Y values from the same x values, but using the formula Y=a*(x-b)^c with the guessed constants.

Next, I take the difference between the y and Y, square the difference, to ensure the numbers are all positive, then take the sum of all of those differences.

Finally, I have a small piece of VBA that runs Solver, setting the sum cell to a minimum by changing a, b and c.

Works wonderfully.

Thanks for your help!

PatrickW

 

[PatrickW]

Erk - sorry about the multi post.

Any admins lurking - please delete.