Forum Discussion
changing scales
I'm trying to convert various scoring scales to a common 100 point scale. I can do it manually, line by line, but can't find a formula to do it for me. Ideas? Thank you!
I was hoping I could find a way to do this more quickly given that I have multiple projects with various totals to convert.
Easily done: I suggest a table that shows the max available for each project, e.g.,
and then a lookup formula that accesses the table
=(F2/VLOOKUP(E2,$A$2:$B$5,2,0))*100
See the attached example
- To help us help you, an example is very useful.
JKPieterse The project below was scored on 120 points. I need to convert 59/120 to a 100 point scale. Some projects were scored out of 95 points, etc. I'm thinking a data table would do this for me (I did the below conversion manually) but haven't figured it out yet. Thank you for any assistance you can provide!
I'm trying to convert various scoring scales to a common 100 point scale. I can do it manually, line by line, but can't find a formula to do it for me. Ideas? Thank you!
Your question is intriguing, in part because there are so many different scales, to begin with, but also various scenarios when, for example, there's a tie when you reach the normal max. One would need to know the rules for this conversion under all circumstances. Here are some scenarios I came up with right off the bat in trying to do this.
- The winning score in PingPong as normally played: first to 21 by a margin of 2.
- So converting to 100 would be =(actual score)*(100/21)
- The winner with 21 would get 100; the loser with 14 would get 66.7
- But what would you do if the game was tied repeatedly until it got to 27-25. Does the 27 get converted to 100 and the 25 appropriately below OR does the 27 get converted to 128.6 and the 25 to 119.0?
- So converting to 100 would be =(actual score)*(100/21)
- And then there's a game like Scrabble: there is no a given end point; instead the person with the highest score wins. So for any given game it would be easy to take the winner's 321 and make it the standard of 100 and calculate all the others by reference to it; e.g., =(123/321)*100
- Or a sport like Bowling: there's a theoretical perfect game of 400. If that were deemed to be the 100 on your "standard scale" it means that a consistent amateur winner--i.e., someone who does not score a perfect game--would show up with lower scores despite winning consistently, that the consistent winner at PingPong who gets his 100 because that's the nature of that scoring system.
A single formula could be written to take account of the nature of the game, some sexy IF/THEN conditional, but let's begin with your responses to what I've written already.
Can I ask why you're asking for this to begin with? As I noted above, it's an intriguing question, but part of the intrigue is that games aren't scored within a consistent conceptual framework. And as I write that, it occurs to me that you may not be talking of scores in games, per se (!!!), so maybe I've taken an assumption that isn't applicable to begin with. So please DO tell us what kinds of scores you're talking an about.
And, once that question has been answered, since you are able to do it manually, line by line, give us the examples. I've written some from my hypothetical trying to standardize ping pong and scrabble, but that's what raised the questions above. Your actuals--since you've done them--may just need to to translated to Excel.
- The winning score in PingPong as normally played: first to 21 by a margin of 2.
