• 460K Members
• 10.5K Online
• 557K Conversations
SOLVED

## Advanced formula to show number movement

Occasional Contributor

# 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

8 Replies

# Re: Advanced formula to show number movement

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

Solution

# Re: Advanced formula to show number movement

You may try this formula:
=MIN(ABS(RIGHT(A2)-RIGHT(B2)),
RIGHT(A2)+10-RIGHT(B2),
RIGHT(B2)+10-RIGHT(A2),
5)

# Re: Advanced formula to show number movement

Hi Wyn,

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

# Re: Advanced formula to show number movement

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

# Re: Advanced formula to show number movement

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.

# Re: Advanced formula to show number movement

@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

# Re: Advanced formula to show number movement

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

# Re: Advanced formula to show number movement

I’m delighted to know that.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies