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(C2120,"Master",IF(C2110,"Expert",IF(C2105," Sharpshooter",IF(C290,"Marksman",IF(C20,"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?