Non-linear curve fitting problem

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,447
Office Version
  1. 365
Platform
  1. Windows
I am trying to determine the equation that best approximates the values in this table for converting raw scores in bridge to IMPs.

Up ToIMPsUp ToIMPsUp ToIMPs
1004209174017
40149010199018
80259011224019
120374012249020
160489013299021
2105109014349022
2606129015399023
31071490164000+24
3608

<tbody>
</tbody>

I believe it is a logarithmic relationship, but I have some zero data. Should I set those values to something like 0.1 or 0.00001?

I have the data in two long columns in a spreadsheet (not broken into 6 as above). I select these columns and click Insert | Scatter. I get a nice graph of the data.

Next I right-click on one of the data points and select Add Trendline. With the zero values included, both the Exponential and Power options are greyed out, so I have Linear, Logarithmic, Polynomial, and Moving Average available. If I choose Linear, Polynomial, or Moving Average, I see a line on the graph and I am still in the Format Trendline dialog. If I click on Logarithmic, I am taken out of the dialog and there is a legend for a Log line, but no line is on the graph.

With the zero values included, the best fit is a 6th order polynomial. None of the others are even close.

With the zero values excluded, the logarithmic option gives a pretty good fit.

If I replace the zero values with 0.0001, then I can get all of the options, but they are all way off including the logarithmic.

Is there a way to get Excel to choose the "best fit" and work around the zero values?

Thanks
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Watch MrExcel Video

Forum statistics

Threads
1,129,301
Messages
5,635,406
Members
416,856
Latest member
silentir

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