@S-C-K
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?
- 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.
