Forum Discussion
Advanced formula to show number movement
Hi,
I am looking for a formula to represent the difference between digit movement, the lowest difference would be 0 and the highest 5 the result would be taken from 2 columns eg.
A B
7 16 = that would equal the difference between digits 7 and 6 (first digit in column "B" is always ignored) in this case the result = 1
example 2
A B
2 19 = 3 (in this case the difference is 3 digit movement in direction that would not go beyond 5 otherwise the result would be 7 which is above 5 and as I mentioned before the highest diference is 5
In conclusion:
we take in consideration digits between 0-9 so for example:
(0) (0) = movment difference 0, (0) (9) = 1, (1) (9) = 2 , (2) (9) = 3, (3) (9) = 4, (4) (9) = 5 (5) (9) = 4, (0) (5) = 5 , (0) (6) = 4 and if there are two digits in a column the first one is always ignored so here is the example:
(0) (19) = 1, (1) (29) = 2 , (2) (59) = 3, (3) (49) = 4, (34) (9) = 5 (15) (39) = 4, (10) (55) = 5 , (50) (56) = 4
I hope I have managed to explain this properly.
What would be the formula to do this in Excel?
Many Thanks,
Regards,
Peter
- You may try this formula:
=MIN(ABS(RIGHT(A2)-RIGHT(B2)),
RIGHT(A2)+10-RIGHT(B2),
RIGHT(B2)+10-RIGHT(A2),
5)
8 Replies
- PeterBartholomew1Silver Contributor
= ABS( MOD(A-B+5,10) - 5 )
- TwifooSilver ContributorYou may try this formula:
=MIN(ABS(RIGHT(A2)-RIGHT(B2)),
RIGHT(A2)+10-RIGHT(B2),
RIGHT(B2)+10-RIGHT(A2),
5)- Peter1550Copper Contributor
Hi Twifoo,
Thanks for your replay this formula works! but how to modify it so it automatically applies to all desired cells for example and columns?
Thanks
- TwifooSilver ContributorI’m replying via mobile phone so I will examine your sample file tomorrow. Anyway, if your data and formula start in A3 and G3, I believe the formula works when entered in G3, provided that row references are modified from 2 to 3.
I am not yet sure of that until I test the formula on your sample file tomorrow.
I don't quite understand but I pulled together the attached file demo which may point you in the right direction?
- Peter1550Copper Contributor
Hi Wyn,
Thanks for your replay I have attached an example sheet to show what result I am looking for