Scoring system with a locked multiplying factor

[Npking4]

Using the information below, I trying to create a locked multiplying factor that I can apply to the "%" column and it returns the "score" column. Examples: 2.26% x (same multiplying factor) = 1.87 or 2.06% x (same multiplying factor) = 1.97
[TABLE="width: 377"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]

<colgroup><col style="width:48pt" span="2" width="64"> <col style="width:48pt" width="64"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4425;width:91pt" width="121"> </colgroup><tbody>
[TD="class: xl67, width: 64"][TABLE="width: 192"]
<colgroup><col style="width:48pt" span="2" width="64"> <col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 64"]Score[/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl68, width: 64"]%[/TD]

[TD="class: xl67, align: right"]2.00[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.00%[/TD]

[TD="class: xl67, align: right"]1.99[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.02%[/TD]

[TD="class: xl67, align: right"]1.98[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.04%[/TD]

[TD="class: xl67, align: right"]1.97[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.06%[/TD]

[TD="class: xl67, align: right"]1.96[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.08%[/TD]

[TD="class: xl67, align: right"]1.95[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.10%[/TD]

[TD="class: xl67, align: right"]1.94[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.12%[/TD]

[TD="class: xl67, align: right"]1.93[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.14%[/TD]

[TD="class: xl67, align: right"]1.92[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.16%[/TD]

[TD="class: xl67, align: right"]1.91[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.18%[/TD]

[TD="class: xl67, align: right"]1.90[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.20%[/TD]

[TD="class: xl67, align: right"]1.89[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.22%[/TD]

[TD="class: xl67, align: right"]1.88[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.24%[/TD]

[TD="class: xl67, align: right"]1.87[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.26%[/TD]

[TD="class: xl67, align: right"]1.86[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.28%[/TD]

[TD="class: xl67, align: right"]1.85[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.30%[/TD]

[TD="class: xl67, align: right"]1.84[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.32%[/TD]

[TD="class: xl67, align: right"]1.83[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.34%[/TD]

[TD="class: xl67, align: right"]1.82[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.36%[/TD]

[TD="class: xl67, align: right"]1.81[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.38%[/TD]

[TD="class: xl67, align: right"]1.80[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68, align: right"]2.40%[/TD]

</tbody>

[/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 121"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD="class: xl70"][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl68, align: right"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[FranzV]

You can use a scatter plot to find an equation that approximately describes the relationship between two variables, in this case Score and %. It happens that your data fits a perfectly straight line with the form

y = m·x + b

where:
y is the resulting variable
m is the slope
x is the input variable
b is the intercept

You can substitute for

Score = m·(%) + b

or

Score = -50*(%) + 3

You can do this in Excel like

Excel 2016 (Windows) 64 bit

	A	B	C
Score	%	UsingFormula

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"] y = m·x + b [/TD]
[TD="align: center"] m [/TD]
[TD="align: center"] b [/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-50[/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"] 2.00[/TD]
[TD="align: right"]2.00%[/TD]
[TD="align: right"] 2.00[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"] 1.99[/TD]
[TD="align: right"]2.02%[/TD]
[TD="align: right"] 1.99[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"] 1.98[/TD]
[TD="align: right"]2.04%[/TD]
[TD="align: right"] 1.98[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"] 1.97[/TD]
[TD="align: right"]2.06%[/TD]
[TD="align: right"] 1.97[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"] 1.96[/TD]
[TD="align: right"]2.08%[/TD]
[TD="align: right"] 1.96[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"] 1.95[/TD]
[TD="align: right"]2.10%[/TD]
[TD="align: right"] 1.95[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"] 1.94[/TD]
[TD="align: right"]2.12%[/TD]
[TD="align: right"] 1.94[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"] 1.93[/TD]
[TD="align: right"]2.14%[/TD]
[TD="align: right"] 1.93[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"] 1.92[/TD]
[TD="align: right"]2.16%[/TD]
[TD="align: right"] 1.92[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"] 1.91[/TD]
[TD="align: right"]2.18%[/TD]
[TD="align: right"] 1.91[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"] 1.90[/TD]
[TD="align: right"]2.20%[/TD]
[TD="align: right"] 1.90[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"] 1.89[/TD]
[TD="align: right"]2.22%[/TD]
[TD="align: right"] 1.89[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"] 1.88[/TD]
[TD="align: right"]2.24%[/TD]
[TD="align: right"] 1.88[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"] 1.87[/TD]
[TD="align: right"]2.26%[/TD]
[TD="align: right"] 1.87[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"] 1.86[/TD]
[TD="align: right"]2.28%[/TD]
[TD="align: right"] 1.86[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"] 1.85[/TD]
[TD="align: right"]2.30%[/TD]
[TD="align: right"] 1.85[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"] 1.84[/TD]
[TD="align: right"]2.32%[/TD]
[TD="align: right"] 1.84[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"] 1.83[/TD]
[TD="align: right"]2.34%[/TD]
[TD="align: right"] 1.83[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"] 1.82[/TD]
[TD="align: right"]2.36%[/TD]
[TD="align: right"] 1.82[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"] 1.81[/TD]
[TD="align: right"]2.38%[/TD]
[TD="align: right"] 1.81[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"] 1.80[/TD]
[TD="align: right"]2.40%[/TD]
[TD="align: right"] 1.80[/TD]

</tbody>

​

Using the following formula in column C:

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C5[/TH]
[TD]=m*B5+b[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


You can use Named Ranges for m and b or type the values directly in the formula.
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]b[/TH]
[TD]=Hoja2!$C$2[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]m[/TH]
[TD]=Hoja2!$B$2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I hope it helps.