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?

Option 1	Option 2	Option 3	Selected item	Scale
11,050	35,350	49,950	55,164	3.5
8,000	12,000	20,000	16,000	2.5
35,000	50,000	75,000	60,000	2.4
22,200	34,680	66,000	34,680	2.0

<tbody>
</tbody><colgroup><col span="3"><col><col></colgroup>

 

[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:

Option 1	Option 2	Option 3	Selected item	Scale
11,050	35,350	49,950	55,164	3.104384384
8,000	12,000	20,000	16,000	2.5
35,000	50,000	75,000	60,000	2.4
22,200	34,680	66,000	34,680	2

<colgroup><col span="4"><col></colgroup><tbody>
</tbody>

 

[R3MCC]

Thank you!