Subtract based on ranking

[Captain13]

Hello,

First of all thank you for your assistance in advance your help is much appreciated.

I am trying to use a formulate in order to subtract values in a column based on their lower value in another.

e.g Column A   Rank 

        500            4

        1000          3

        2000          2

        3000          1

i am trying to subtract 4 from 3 to give me the difference and 2 from one to give me the difference.

more or less like a conditional subtracting values lower rank from the next higher rank such as : 

ranking 4 value - ranking 3 value and ranking 2 value - ranking 1 value.

thank you again for your help.

[#2 · Accepted solution]

@Captain13 

If the values are sorted on rank it's simply a matter of entering

=A3-A2

in a cell in row 3, then fill down.

If the values are not sorted on rank, let's say the values are in A2 and down, and the ranks are in B2 and down.

Enter the following formula in a cell in row 2, e.g. in C2:

=IF(B2=MAX($B$2:$B$100),"",A2-INDEX($A$2:$A$100,MATCH(B2+1,$B$2:$B$100,0)))

and fill down.

[#3]

@Captain13 

=INDEX($A$2:$A$7,MATCH(D2,$B$2:$B$7,0))-INDEX($A$2:$A$7,MATCH(D2-1,$B$2:$B$7,0))

You can try this formula.

 

[#4]

Thank you that helped and answered my question!!! have a good weekend!

[#5]

Thank you for your response, this solution also helped.. have a good weekend!