SOLVED
Home

Advanced formula to show number movement

%3CLINGO-SUB%20id%3D%22lingo-sub-677314%22%20slang%3D%22en-US%22%3EAdvanced%20formula%20to%20show%20number%20movement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677314%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%3EHi%2C%20%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EI%20am%20looking%20for%20a%20formula%20to%20represent%20the%20difference%20between%20digit%20movement%2C%20the%20lowest%20difference%20would%20be%200%20and%20the%20highest%205%20the%20result%20would%20be%20taken%20from%202%20columns%20eg.%3CBR%20%2F%3EA%26nbsp%3B%26nbsp%3B%26nbsp%3B%20B%3CBR%20%2F%3E7%26nbsp%3B%26nbsp%3B%26nbsp%3B%2016%20%3D%20that%20would%20equal%20the%20difference%20between%20digits%207%20and%206%20(first%20digit%20in%20column%20%22B%22%20is%20always%20ignored)%20in%20this%20case%20the%20result%20%3D%201%3CBR%20%2F%3Eexample%202%3CBR%20%2F%3EA%26nbsp%3B%20B%3CBR%20%2F%3E2%26nbsp%3B%2019%20%3D%203%20(in%20this%20case%20the%20difference%20is%203%20digit%20movement%20in%20direction%20that%20would%20not%20go%20beyond%205%20otherwise%20the%20result%20would%20be%207%20which%20is%20above%205%20and%20as%20I%20mentioned%20before%20the%20highest%20diference%20is%205%3CBR%20%2F%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EIn%20conclusion%3A%3CBR%20%2F%3Ewe%20take%20in%20consideration%20digits%20between%200-9%26nbsp%3B%20so%20for%20example%3A%3CBR%20%2F%3E(0)%20(0)%20%3D%20movment%20difference%200%2C%26nbsp%3B%20(0)%20(9)%20%3D%201%2C%26nbsp%3B%20(1)%20(9)%20%3D%202%20%2C%26nbsp%3B%20(2)%20(9)%20%3D%203%2C%26nbsp%3B%20(3)%20(9)%20%3D%204%2C%26nbsp%3B%20(4)%20(9)%20%3D%205%20(5)%20(9)%20%3D%204%2C%20(0)%20(5)%20%3D%205%20%2C%20(0)%20(6)%20%3D%204%26nbsp%3B%20and%20if%20there%20are%20two%20digits%20in%20a%20column%20the%20first%20one%20is%20always%20ignored%20so%20here%20is%20the%20example%3A%3CBR%20%2F%3E(0)%20(19)%20%3D%201%2C%26nbsp%3B%20(1)%20(29)%20%3D%202%20%2C%26nbsp%3B%20(2)%20(59)%20%3D%203%2C%26nbsp%3B%20(3)%20(49)%20%3D%204%2C%26nbsp%3B%20(34)%20(9)%20%3D%205%20(15)%20(39)%20%3D%204%2C%20(10)%20(55)%20%3D%205%20%2C%20(50)%20(56)%20%3D%204%3CBR%20%2F%3EI%20hope%20I%20have%20managed%20to%20explain%20this%20properly.%3CBR%20%2F%3EWhat%20would%20be%20the%20formula%20to%20do%20this%20in%20Excel%3F%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EMany%20Thanks%2C%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3ERegards%2C%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EPeter%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-677314%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677446%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20formula%20to%20show%20number%20movement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677446%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356428%22%20target%3D%22_blank%22%3E%40Peter1550%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20don't%20quite%20understand%20but%20I%20pulled%20together%20the%20attached%20file%20demo%20which%20may%20point%20you%20in%20the%20right%20direction%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677467%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20formula%20to%20show%20number%20movement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677467%22%20slang%3D%22en-US%22%3EYou%20may%20try%20this%20formula%3A%3CBR%20%2F%3E%3DMIN(ABS(RIGHT(A2)-RIGHT(B2))%2C%3CBR%20%2F%3ERIGHT(A2)%2B10-RIGHT(B2)%2C%3CBR%20%2F%3ERIGHT(B2)%2B10-RIGHT(A2)%2C%3CBR%20%2F%3E5)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677551%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20formula%20to%20show%20number%20movement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677551%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Wyn%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20replay%20I%20have%20attached%20an%20example%20sheet%20to%20show%20what%20result%20I%20am%20looking%20for%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677596%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20formula%20to%20show%20number%20movement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677596%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Twifoo%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20replay%20this%20formula%20works!%20but%20how%20to%20modify%20it%20so%20it%20automatically%20applies%20to%20all%20desired%20cells%20for%20example%20and%20columns%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677717%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20formula%20to%20show%20number%20movement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677717%22%20slang%3D%22en-US%22%3EI%E2%80%99m%20replying%20via%20mobile%20phone%20so%20I%20will%20examine%20your%20sample%20file%20tomorrow.%20Anyway%2C%20if%20your%20data%20and%20formula%20start%20in%20A3%20and%20G3%2C%20I%20believe%20the%20formula%20works%20when%20entered%20in%20G3%2C%20provided%20that%20row%20references%20are%20modified%20from%202%20to%203.%3CBR%20%2F%3EI%20am%20not%20yet%20sure%20of%20that%20until%20I%20test%20the%20formula%20on%20your%20sample%20file%20tomorrow.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677725%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20formula%20to%20show%20number%20movement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677725%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20alright%2C%20I%20have%20managed%20to%20create%20a%20template%20for%20all%20desired%20rows%20and%20it%20works%20and%20the%20formula%20works%20as%20supposed%20to.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eonce%20again%20%3CSPAN%3ETwifoo!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EMany%20Thanks%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EPeter%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677742%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20formula%20to%20show%20number%20movement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677742%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356428%22%20target%3D%22_blank%22%3E%40Peter1550%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%20ABS(%205-MOD(A-B-5%2C10)%20)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677743%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20formula%20to%20show%20number%20movement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677743%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356428%22%20target%3D%22_blank%22%3E%40Peter1550%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%20ABS(%20MOD(A-B%2B5%2C10)%20-%205%20)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677751%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20formula%20to%20show%20number%20movement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677751%22%20slang%3D%22en-US%22%3EI%E2%80%99m%20delighted%20to%20know%20that.%3C%2FLINGO-BODY%3E
Peter1550
Occasional 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?

 

 

 

 

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.
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies