Curve fitting: non-standard

PatrickW

New Member
Joined
Oct 23, 2008
Messages
41
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Hi there

Assuming b=0, this is rather trivial:

y=a*x^c + error

<==>

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

<==>

ln(y)=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

New Member
Joined
Oct 23, 2008
Messages
41
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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

New Member
Joined
Oct 23, 2008
Messages
41

ADVERTISEMENT

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

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
That's exactly how I'd have it set up as well. Congratulations.
 

PatrickW

New Member
Joined
Oct 23, 2008
Messages
41
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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,992
Messages
5,622,033
Members
415,874
Latest member
JockPC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top