SOLVED

changing scales

Copper Contributor

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!

11 Replies
To help us help you, an example is very useful.

@Jan Karel Pieterse   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!

 

SCK_0-1693233003245.png

 

@S-C-K 

Wouldn't that be

 

=score/maximum_points*100

@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.

@Hans Vogelaar 

Wouldn't that be =score/maximum_points*100

 

I think you got it, Hans. At least for the situation that @S-C-K now has finally described. My own flight of fantasy took me off in a different direction altogether, which was intriguing, but apparently irrelevant.

Yes, that is the formula I'm using to do it manually. I was hoping I could find a way to do this more quickly given that I have multiple projects with various totals to convert. Thanks for your help!

@S-C-K 

You could use

 

=score/SUM(all_scores)*100

best response confirmed by S-C-K (Copper Contributor)
Solution

@S-C-K 

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., 

mathetes_0-1693238308965.png

 

and then a lookup formula that accesses the table

=(F2/VLOOKUP(E2,$A$2:$B$5,2,0))*100

mathetes_1-1693238357491.png

See the attached example

 

Thank you!
This will do it! Thanks so much!
This is helpful. How would I adjust for scales that are inverted? Specifically, I'm trying to normalize a scale that runs from 1-3 where 1 is max and actuals would come in at decimals between the range of 1-3.
1 best response

Accepted Solutions
best response confirmed by S-C-K (Copper Contributor)
Solution

@S-C-K 

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., 

mathetes_0-1693238308965.png

 

and then a lookup formula that accesses the table

=(F2/VLOOKUP(E2,$A$2:$B$5,2,0))*100

mathetes_1-1693238357491.png

See the attached example

 

View solution in original post