Forum Discussion

Peter1550's avatar
Peter1550
Copper Contributor
Jun 07, 2019
Solved

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

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    You may try this formula:
    =MIN(ABS(RIGHT(A2)-RIGHT(B2)),
    RIGHT(A2)+10-RIGHT(B2),
    RIGHT(B2)+10-RIGHT(A2),
    5)
    • Peter1550's avatar
      Peter1550
      Copper Contributor

      Twifoo 

       

      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 

      • Twifoo's avatar
        Twifoo
        Silver Contributor
        I’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.

Resources