Non-linear curve fitting problem

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,687
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.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Up To[/TD]
[TD="align: center"]IMPs[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Up To[/TD]
[TD="align: center"]IMPs[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Up To[/TD]
[TD="align: center"]IMPs[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]420[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1740[/TD]
[TD="align: center"]17[/TD]
[/TR]
[TR]
[TD="align: center"]40[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]490[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1990[/TD]
[TD="align: center"]18[/TD]
[/TR]
[TR]
[TD="align: center"]80[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]590[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2240[/TD]
[TD="align: center"]19[/TD]
[/TR]
[TR]
[TD="align: center"]120[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]740[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2490[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]160[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]890[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2990[/TD]
[TD="align: center"]21[/TD]
[/TR]
[TR]
[TD="align: center"]210[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1090[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3490[/TD]
[TD="align: center"]22[/TD]
[/TR]
[TR]
[TD="align: center"]260[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1290[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3990[/TD]
[TD="align: center"]23[/TD]
[/TR]
[TR]
[TD="align: center"]310[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1490[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4000+[/TD]
[TD="align: center"]24[/TD]
[/TR]
[TR]
[TD="align: center"]360[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Forum statistics

Threads
1,223,892
Messages
6,175,236
Members
452,621
Latest member
Laura_PinksBTHFT

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
Back
Top