Jun 07 2019 04:19 AM
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
Jun 07 2019 05:33 AM
I don't quite understand but I pulled together the attached file demo which may point you in the right direction?
Jun 07 2019 05:37 AM - edited Jun 07 2019 05:38 AM
SolutionJun 07 2019 06:27 AM
Hi Wyn,
Thanks for your replay I have attached an example sheet to show what result I am looking for
Jun 07 2019 06:41 AM
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
Jun 07 2019 07:12 AM
Jun 07 2019 07:16 AM
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
Jun 07 2019 07:27 AM
= ABS( MOD(A-B+5,10) - 5 )
Jun 07 2019 05:37 AM - edited Jun 07 2019 05:38 AM
Solution