SOLVED

Advanced formula to show number movement

Copper Contributor

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

8 Replies

@Peter1550 

 

I don't quite understand but I pulled together the attached file demo which may point you in the right direction?

 

 

 

 

best response confirmed by Peter1550 (Copper Contributor)
Solution
You may try this formula:
=MIN(ABS(RIGHT(A2)-RIGHT(B2)),
RIGHT(A2)+10-RIGHT(B2),
RIGHT(B2)+10-RIGHT(A2),
5)

@Wyn Hopkins 

 

Hi Wyn,

 

Thanks for your replay I have attached an example sheet to show what result I am looking for 

@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 

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.

@Twifoo

 

That's alright, I have managed to create a template for all desired rows and it works and the formula works as supposed to.

 

once again Twifoo!

Many Thanks, 

 

Regards,

Peter 

@Peter1550 

= ABS( MOD(A-B+5,10) - 5 )

I’m delighted to know that.
1 best response

Accepted Solutions
best response confirmed by Peter1550 (Copper Contributor)
Solution
You may try this formula:
=MIN(ABS(RIGHT(A2)-RIGHT(B2)),
RIGHT(A2)+10-RIGHT(B2),
RIGHT(B2)+10-RIGHT(A2),
5)

View solution in original post