A formula to auto-update a classification ONLY if it goes UP

[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

 

[John C]

You will need to calculate a 'running' average on your sheet that you are
entering data on. Say, for example, your running average is in column C, with
competitor in column A, and there score for that entry in column B.
C2: =SUMPRODUCT(--(A$2:A2=A2),(B$2:B2))/COUNTIF(A$2:A2,A2)
This will keep the running average for the competitor.
Then, your B7 below on your other tab, B7 would b equal to:
=MAX(IF(Data!$A$2:$A$100=A7,Data!$C$2:$C$100))
Assuming A7 is the competitor you are evaluating. B7 will now be equivalent
to the competitors highest 'average'.

 

[thorshammer]

I'm not sure if that answers my question or not.
I already have a formula calculating the running average for each competitor
with a scope of column N to column
BA>>>=IF(ISERROR(AVERAGE(N7:BA7)),"NSR",(AVERAGE(N7:BA7)))

Now I need to have the spreadsheet assign the class to each competitor
automatically based on the result of this formula but ONLY if the current
average indicates a 'jump' in rank. If it indiacates a 'drop' in rank, it
should do nothing.
Does your 2nd formula below accomplish this?

 

[John C]

If this is your formula in B7, then it is not a 'running' average, it is
always a current average. From your formula, it appears as though the
person's scores are entered in cells from N7:BA7. Real simple example would
be as follows:
N77 = 100, 125, 150, 75
Current average = 112.5
A running average would have multiple results, say in cells N8:O8, and they
would be: 100, 112.5, 125, 112.5
Then you would take the maximum value from row 8, which would give you
always your highest average.