Points behind next rank in a fantasy league

[Guest]

Here I go again! I run a fantasy racing league. I have a formula that
calculates points behind the first place player. How do I create a formula
to show points behind the next player? Here's my formula for the points
behind the leader:
=MAX(AR14,AR25,AR36,AR46,AR56,AR69,AR79,AR89,AR99)-AR14
I also show a player's rank, can (or would) that work to help calculate
this? Here's the rank formula: =RANK(AR14,AR4:AR109)
Thanks in advance!

 

[Guest]

I'll make a couple of suggestions.

Start by pulling your subtotals into a new column. It appears from your
formula that you have them in column AR beneath data, likely grouped by
league participant.

With subtotals in a new column, such as column AS, then you can easily use
the LARGE worksheet function in a manner such as LARGE(AS:AS,1) -
LARGE(AS:AS,2) to get your point separation.

You could use the RANK function which you have already mentioned on column
AS to generate the rank figures for the subtotals in column AS. For example,
perhaps cell AT49 could read RANK(AS49,AS:AS,1). These rank values could
then be used in lieu of the 1 and the 2 in the LARGE functions above.