Writing formula for cell differences based off a rank

[Don Jones]

I have a spread sheet that tracks a nascar pool. It adds
up our season points, gives rank, total points & average
finishing position. I am now interested in adding a
formula that will show a persons current number of points
behind the leader. I am confused how to do this cause the
leader can change from week to week. Is it a sum with a
reference to rank then total points?

Help!!

Thank You

Don Jones

 

[Andy Brown]

Is it a sum with a reference to rank then total points?

Say your people are in A1:A6 with points in B1:B6. Try

=IF(B1=MAX($B$1:$B$6),"",MAX($B$1:$B$6)-B1)

in C1:C6.

HTH,
Andy

 

[Tom Ogilvy]

In D1 for example
=max($C:$C)-C1

Then drag down column D.

assume points are in column C.

 

[Don Jones]

Andy look for my email showing you my sheet for help. You
will see at the bottom the cell to be assigned for points
behind the leader. Also look at my rank cells for current
position.

Don