Calculating an Average point value based on selected cost

[R3MCC]

Hi,
If I have a table shown below. I want to calculate the numeric rank of the item selected. For the examples below, I would expect row 1 to return something over a ranking of 3 as the selected value ended up higher than the largest option given. Where as line two's selection was in the middle of 2 and 3 so I would expect 2.5. Line 3 would be close to maybe 2.4 and then line 4 would be 2. Any thoughts on a formula or equation to use to do this?
[TABLE="width: 465"]
<tbody>[TR]
[TD]Option 1 [/TD]
[TD]Option 2[/TD]
[TD]Option 3[/TD]
[TD]Selected item[/TD]
[TD]Scale [/TD]
[/TR]
[TR]
[TD] 11,050 [/TD]
[TD] 35,350 [/TD]
[TD] 49,950 [/TD]
[TD] 55,164 [/TD]
[TD] 3.5 [/TD]
[/TR]
[TR]
[TD] 8,000 [/TD]
[TD] 12,000 [/TD]
[TD] 20,000 [/TD]
[TD] 16,000 [/TD]
[TD] 2.5 [/TD]
[/TR]
[TR]
[TD] 35,000 [/TD]
[TD] 50,000 [/TD]
[TD] 75,000 [/TD]
[TD] 60,000 [/TD]
[TD] 2.4 [/TD]
[/TR]
[TR]
[TD] 22,200 [/TD]
[TD] 34,680 [/TD]
[TD] 66,000 [/TD]
[TD] 34,680 [/TD]
[TD] 2.0 [/TD]
[/TR]
</tbody><colgroup><col span="3"><col><col></colgroup>[/TABLE]

 

[cerfani]

maybe there is a better way but this seems to work the way you described...

=IF(D2>=C2,2+D2/C2,IF(D2>=B2,2+(D2-B2)/(C2-B2),IF(D2>=A2,1+(D2-A2)/(B2-A2),D2/A2)))

that is the formula for the scale assuming the table starts in A1

Result:

[TABLE="width: 317"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD]Option 1[/TD]
[TD]Option 2[/TD]
[TD]Option 3[/TD]
[TD]Selected item[/TD]
[TD]Scale[/TD]
[/TR]
[TR]
[TD="align: right"]11,050[/TD]
[TD="align: right"]35,350[/TD]
[TD="align: right"]49,950[/TD]
[TD="align: right"]55,164[/TD]
[TD="align: right"]3.104384384[/TD]
[/TR]
[TR]
[TD="align: right"]8,000[/TD]
[TD="align: right"]12,000[/TD]
[TD="align: right"]20,000[/TD]
[TD="align: right"]16,000[/TD]
[TD="align: right"]2.5[/TD]
[/TR]
[TR]
[TD="align: right"]35,000[/TD]
[TD="align: right"]50,000[/TD]
[TD="align: right"]75,000[/TD]
[TD="align: right"]60,000[/TD]
[TD="align: right"]2.4[/TD]
[/TR]
[TR]
[TD="align: right"]22,200[/TD]
[TD="align: right"]34,680[/TD]
[TD="align: right"]66,000[/TD]
[TD="align: right"]34,680[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[R3MCC]

Thank you!