# Non-linear curve fitting problem

#### JenniferMurphy

##### Well-known Member
I am trying to determine the equation that best approximates the values in this table for converting raw scores in bridge to IMPs.

 Up To IMPs Up To IMPs Up To IMPs 10 0 420 9 1740 17 40 1 490 10 1990 18 80 2 590 11 2240 19 120 3 740 12 2490 20 160 4 890 13 2990 21 210 5 1090 14 3490 22 260 6 1290 15 3990 23 310 7 1490 16 4000+ 24 360 8

<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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Replies
9
Views
441
Replies
9
Views
576
Replies
1
Views
410
Replies
2
Views
340
Replies
7
Views
21K

1,129,407
Messages
5,636,107
Members
416,898
Latest member
imsorrymen

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