• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compare alphanumeric data and show up or down

K

KapardhiSarma

New Member
Hi Team,

I have an issue with the formula i am using. I have a duration of days for two months in two different columns like 22d and 44d. I used a formula to find out if there is a change in the duration or not. My formula below.

=IF($A1>$B1,"Up","Down"). But there are few cases where there is an upward change, but it is still showing down. I doubt this is because of the alphanumeric characters in the columns.

Please help me how to handle this situation.

Example: A3: 174.13d, B3: 77d, but the formula is still showing the result as "Down" instaed of "Up"

Thanks,
Kapil
 
Hi KapardhiSarma, the formula you are using will not work.
Try the string AA in both A and B, it will still show Down.
 
Last edited:
Dear Kapil

It is going wrong because it is doing a string comparision as opposed to a numerical comparison between the two values. In a string comparision 77 is more than 177 (in the same way that gg is more than agg). To overide this you could use the formula =IF(LEFT($A1,LEN($A1)-1)+1-1>LEFT($B1,LEN($B1)-1)+1-1,"Up","Down")
 
Hi KapilSharma,

If you want to compare based on 13d & 77d, try using below formula
In G6 : 134.13d
In G7: 77d
in G8: =IF(MID(G7,IFERROR((SEARCH(".",G7,1)+1),1),SEARCH("d",G7,1)-IFERROR(SEARCH(".",G7,1)+1,1))>MID(G6,IFERROR((SEARCH(".",G6,1)+1),1),SEARCH("d",G6,1)-IFERROR(SEARCH(".",G6,1)+1,1)),"UP","DOWN")

Advise if I understood it wrong.

Regards!
 
Jake Collins said:
Dear Kapil

It is going wrong because it is doing a string comparision as opposed to a numerical comparison between the two values. In a string comparision 77 is more than 177 (in the same way that gg is more than agg). To overide this you could use the formula =IF(LEFT($A1,LEN($A1)-1)+1-1>LEFT($B1,LEN($B1)-1)+1-1,"Up","Down")
Click to expand...


Thank you Jake for the response and help here.
I have one more doubt. Would this handle negative values as well?
There are some negative values say -12d and 0d. Would the formula handle this?

Thanks,
Kapil
 
@KapardhiSarma , as you mentioned values are in cell A1 and B1 order, just editing @Somendra Misra 's formula a bit.

=IF(MID(B1,IFERROR((SEARCH(".",B1,1)+1),1),SEARCH("d",B1,1)-IFERROR(SEARCH(".",B1,1)+1,1))>MID(A1,IFERROR((SEARCH(".",A1,1)+1),1),SEARCH("d",A1,1)-IFERROR(SEARCH(".",A1,1)+1,1)),"UP","DOWN")
 
Hi ,

Basically the numeric value is being separated by the following :

=LEFT($A1,LEN($A1)-1)+0

The same effect is also possible using just one function :

=SUBSTITUTE(A1,"d","")+0

The above will work provided the only suffix being used is d. One advantage is that if the data does not contain a suffix , the second formula will still return the correct result.

Narayan
 
You must log in or register to reply here.
Back
Top