Here's a challenge...

[thorshammer]

I have a spreadsheet to list individual scores and show running averages for
each competitor.
Column B - competitor's name
Column C - competitor's running average (This autimatically updates as new
scores are posted)
Column D and above - competitors' individual scores.

Column A contains an 'IF' function which checks the result of Column C and
assigns an arbitrary "class" or "rank" depending on the number. For example,
if the number in column C is 100, the competitor is assigned the rank of
"marksman". If the number is 110, the competitor is assigned the rank of
"expert". If it's 120, "master", etc.

My problem is that the rules of the league I'm running state that once a
competitor achieves a rank, they can NEVER drop down from that rank. They
can only go up. Right now, the formula I have in Column A keys on the number
in Column C and automatically changes the rank up or down as the person's
average changes.

How can I modify or create a formula to check the number in Column C and
ONLY reasign a new rank if the average indicates it should jump UP, but do
nothing if the average went down after new scores were added.

Here's an example of what it currently looks like:

A B C D E F
1Rank Name Average Score1 Score2 Score3
2Master Jim 120 120 120 120
3Expert Dave 110 110 110 110

Here's what the formula in column A looks like...
=IF(C2>120,"Master",IF(C2>110,"Expert",IF(C2>105,"Sharpshooter",IF(C2>90,"Marksman",IF(C2>0,"In Training")))))

I thought of perhaps adding some columns way out to the right of the
spreadsheet and using them to assign arbitrary numeric values to each rank
and then creating a formula to increment the numeric value by one if the
current average jumps up enough to merit change, but do nothing if it
doesn't, but I can't figure out how to make it work.

Any Ideas?

 

[John C]

As I stated on your other post, your error occurs because your AVERAGE in
column C is not a running average, but a current average. The idea of columns
off to the right can work for you. Say your scores are in columns D:BA, that
totals 50 columns. Then, for example, give a little space, start in colmn
CA2, and type this formula:
=IF(C2="","",AVERAGE($C2:C2))
Then copy to the right the next 50 columns (so your range in this example is
CA2X2. This will 'capture' the average after each score is entered.
Then, in C2, your AVERAGE, your formula would be =MAX($CA2:$DW2)

 

[thorshammer]

Ok, I am now starting to see where my confusion is. The problem is that I
misled you. What I was considering to be a running average is not what you
are explaining. the formula that I am using to calculate the average in
column C is actually taking the last 3 most recently entered scores and
averaging them. Here is the formula I'm using for that column:
=AVERAGE(INDEX(1:1,MATCH(10^10,1:1)-2):JC1)

Since the rules of the league require that the rank classification must
always be based on the average of the competitor's 3 most recent scores, I
assumed that was the same as "running" average. I realize now that it is not.

Perhaps I can still adapt your method ??

 

[John C]

This formula should get rid of circular references if no data is entered, or
only 1 score is entered.
=IF(COUNT(D1:E1)=0,0,IF(COUNT(D1:E1)<2,AVERAGE(D1:E1),AVERAGE(INDEX(1:1,MATCH(10^10,1:1)-2):IV1)))

Since your formula goes all the way across the tab, I suggest a second tab,
set up as follows, assuming your first tab is called Data
Data2 is the name of the second tab:
Whichever row starts with competitor names, say row 1:
A1: =Data!B1 ... this just pulls the competitor's name across
B1: =IF(COUNT(Data!D1:F1)=0,0,AVERAGE(Data!D1:F1)) ... copy this formula
across as far as needed.
Your main Data tab can be just as is, except when you are looking up the
ranking, instead of referencing the current average, you reference the MAX
value of the same row on Data2...
....MAX(Data2!1:1)...
for your vlookup.

Hope this helps.

 

[thorshammer]

Ok, I am now starting to see where my confusion is. The problem is that I
misled you. What I was considering to be a running average is not what you
are explaining. the formula that I am using to calculate the average in
column C is actually taking the last 3 most recently entered scores and
averaging them. Here is the formula I'm using for that column:
=AVERAGE(INDEX(1:1,MATCH(10^10,1:1)-2):JC1)

Since the rules of the league require that the rank classification must
always be based on the average of the competitor's 3 most recent scores, I
assumed that was the same as "running" average. I realize now that it is not.

Perhaps I can still adapt your method ??