Scoring values from 0 to 10
(Disclaimer: I am not a mathematician)
Say you need a formula that attributes a “good” price of £150 with a score of 10, and a bad price of £300 a score of 0.
Let’s call £150 b for best and £300 w for worst, with an input price p and output Score:
Score := (10 * (w - p)) / (w - b)
This formula also works if your “best” score is higher than your “worst” score, as it would be if you’re counting something like pay or RAM instead of cost.
I find this technique useful in Excel to give normalised scores to some data:
- Column B has prices
- I’ve decided my hoped-for price is £5995 and my limit for worst is £8995
- Column C has the formula
=(10*(8995-B1))/(8995-5995) - I flash fill the formula down
- £6450 in Column B would score 8.48
- £7295 would score 5.67
If I have multiple different columns to rate, I create a Score column next to each of them, then my final score for the row is just AVERAGE(C1,E1,G1) etc.
This will probably help me more than it helps you, but I hope it helps someone anyway…
Rest and be well! 🌻