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

--
John C


"thorshammer" wrote:

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