Excel: Give scores based on range, where max = 1 and min = 10

Question

I have following problem: I want to give scores to a range of numbers from 1-10 for example:

|   | A    | B  |
|---|------|----|
| 1 | 1209 | 1  |
| 2 | 401  | 7  |
| 3 | 123  | 9  |
| 4 | 49   | 10 |
| 5 | 30   | 10 |

(Not sure if B is 100% correct but roughly) I got the B values with

=ABS(CEILING(A1;MAX($A$1:$A$32)/10)*10/MAX($A$1:$A$32)-11)

It seems to work but if I for example take numbers like

|   | A    | B  |
|---|------|----|
| 1 | 100  | 1  |
| 2 | 90   | 2  |
| 3 | 80   | 3  |
| 4 | 70   | 4  |
| 5 | 50   | 6  |

But I want 50 to be 10.

I would like to have it scalable so I can do it with a 1-10 or 1-100 or 5-27 or whatever scale and with however many numbers in the list and whatever numbers to score from.

Thanks!

Answer 1

Use this formula:

=$E$1 + ROUND((MIN($A:$A)-A1)/((MAX($A:$A)-MIN($A:$A))/($E$1-$E$2)),0)

It is scalable. You put the max and min in E1 and E2.