JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,676
- Office Version
- 365
- Platform
- 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
[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