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 =...
excel
Formulas and Functions
  • Twifoo's avatar
    Twifoo
    Jun 07, 2019
    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
Jun 07, 2019

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
Jun 07, 2019
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.
  • Peter1550's avatar
    Peter1550
    Copper Contributor
    Jun 07, 2019

    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 

    • Twifoo's avatar
      Twifoo
      Silver Contributor
      Jun 07, 2019
      I’m delighted to know that.

Resources