Real time score based on currrent rank

I need my spreadsheet to show point totals based on current rankings. This is a scoring sheet that will be used for 3 rounds of scores with the score and rankings updated realtime. I can't figure out how to calculate the "points from 1st" column. With everything changing with each input, all I can figure is it will take some massive formula to make it work. Help! My brain is mush.


Player Score Rank points from 1st
A 50 3 -25
B 75 1 -
C 65 2 -10

If scores are in B2:B10 then in D2 copied down

=MAX(B$2:B$10)-B2

If you don't want the zero to display against the top score then format these cells as

0;0;

The problem is that in Round One, B2 may contain the top score. But in Round Two B10 may have the highest cumulative score and so on in Round Three. I have to look at this at a glance and update the announcers with how many points are needed for "Player X" to move into 1st place. Sorry if I wasn't clear with my problem. Be gentle, I am a first time poster to any forum.

The formula I suggested should calculate the difference correctly no matter where the highest score is, or if it moves.

How do your scores update, I'm assuming column D (containing the suggested formula) won't be affected by the updating, so the formula should update to the correct amount when column B changes

I have 16 players (individual scoring broken into 2 groups - 8 amateurs & 8professionals, and their are 8 teams of two that make of the ProAm group). The amateurs compete against amateurs and professionals against professional for individual honors. Each player has 5 scoring opportunities per Round and there are 3 Rounds. Their individual & team scores are cumulative. So both data sets are updating real time. I can look at a glance at anytime and see their current team and individual rankings. Bottom line is that their totals/rankings change constantly and I need to know how many points they need to get to first place at anytime for the three groups: Amateur, Professional, and Team. I hope this makes sense.

So I presume my suggestion didn't work.

I can't really visualise how your data is set out, can you explain the layout or post a spreadsheet?

Are you using VBA, how are the scores being updated?

Example Scoring.xlsx Here is the score sheet.

You could use a formula like the one I suggested.

In D42 use this formula copied down to D49

=MAX(B$42:B$49)-B42

and similarly in D52 copied down to D59

=MAX(B$52:B$59)-B52

Then AB6 just has the formula =D52 and similar for other cells in AB column....or you could use a VLOOKUP to pick up the value based on the player shown in column A, e.g. in AB6

=VLOOKUP(A6,A$42:D$59,3,0)

copy down column and delete thye #N/As

Sorry I didn't get back sooner. We had our event last week and your recommendations worked perfectly in the score sheet! Thanks so much!