# Curve fitting: non-standard

#### PatrickW

##### New Member
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).

Thanks

PatrickW

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### wigi

##### Well-known Member
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

##### New Member
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
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

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!

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.

PatrickW

#### wigi

##### Well-known Member
That's exactly how I'd have it set up as well. Congratulations.

#### PatrickW

##### New Member
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!

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.

PatrickW

#### PatrickW

##### New Member
Erk - sorry about the multi post.

Replies
1
Views
614
Replies
0
Views
350
Replies
0
Views
755
Replies
0
Views
1K
Replies
4
Views
1K

1,130,174
Messages
5,640,590
Members
417,152
Latest member
DayTimeSeby

### 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.

### Which adblocker are you using?

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

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