SOLVED

Subtract based on ranking

Occasional Contributor

Subtract based on ranking

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.

4 Replies
best response confirmed by Captain13 (Occasional Contributor)
Solution

Re: Subtract based on ranking

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.

Re: Subtract based on ranking

``=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.

Re: Subtract based on ranking

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

Re: Subtract based on ranking

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