T
thorshammer
I have a spreadsheet which assigns a class value to a competitor based on
that person's average score. As I add new scores in a row for each person
and their average changes, the person's "class" is automatically changed.
Here is the formula I am using....
=IF(B7>197.9,"Master",IF(B7>189.9,"Expert",IF(B7>169.9,"Sharpshooter",IF(B7>139,"Marksman",IF(B7>0,"In Training")))))
The problem is that, according to the rules in the league I am running, a
person's "class" may ONLY go UP, never down, even if his average drops. For
example, when a person moves "up" to the rank of "Master", he can never drop
down to "Expert" again even if his subsequent scores bring his average down.
So, I need the formula to somehow check whether the new average will "move"
the player up or down, and ONLY update their classification if it jumps to a
higher rank. It should do nothing otherwise.
I thought of assigning a par value to each class such as "Master=5",
Exper=4", etc., and then having the formula base it's evaluation on that
number, but I haven't been able to figure out how to do it.
Any help?
P.S.
Here is an example of the format of the current spreadsheet:
Classification Running Average Name Score1 Score2 Score3
Master 199 Jack 200 199
200
Expert 196 Jane 197 198
194
that person's average score. As I add new scores in a row for each person
and their average changes, the person's "class" is automatically changed.
Here is the formula I am using....
=IF(B7>197.9,"Master",IF(B7>189.9,"Expert",IF(B7>169.9,"Sharpshooter",IF(B7>139,"Marksman",IF(B7>0,"In Training")))))
The problem is that, according to the rules in the league I am running, a
person's "class" may ONLY go UP, never down, even if his average drops. For
example, when a person moves "up" to the rank of "Master", he can never drop
down to "Expert" again even if his subsequent scores bring his average down.
So, I need the formula to somehow check whether the new average will "move"
the player up or down, and ONLY update their classification if it jumps to a
higher rank. It should do nothing otherwise.
I thought of assigning a par value to each class such as "Master=5",
Exper=4", etc., and then having the formula base it's evaluation on that
number, but I haven't been able to figure out how to do it.
Any help?
P.S.
Here is an example of the format of the current spreadsheet:
Classification Running Average Name Score1 Score2 Score3
Master 199 Jack 200 199
200
Expert 196 Jane 197 198
194