Forum Discussion
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.
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.
4 Replies
=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.
- Thank you for your response, this solution also helped.. have a good weekend!
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.
- Thank you that helped and answered my question!!! have a good weekend!
