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(B7197.9,"Master",IF(B7189.9,"Expert",IF(B71 69.9,"Sharpshooter",IF(B7139,"Marksman",IF(B70," 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