Number Formatting Problems

[nirvehex]

Hi,

In column AJ I have numbers formatted as general which come from a formula. For example AJ3 = =IF(BJ3>Y3,L3,LEFT(AM3,FIND("-",AM3,1)-1)) which results in "1".

In column L I have numbers formatted as general which also come from a formula. For example L3 = =OFFSET(Table2[@Quantity],-1,0,1,1) which results in "1".

However, when I write a formula to compare the two, AJ3=L3 it says false. Why is this? I have tried formatting them as numbers, text, general, all to the same result. I don't get it!

Any help would be awesome.

Thanks!

 

[VoG]

Try

=AJ3+0=L3

 

[mikerickson]

The formula in AJ3 might be returning a string and L3 a number.

 

[nirvehex]

Well, ideally what I need to do is write this formula and have it return an accurate answer:

=IF(AJ3=L3,"Qty Same",IF(AJ3>L3,"Qty Increase","Qty Decrease"))

Right now it is saying "Qty Increase" no matter how I format these numbers it seems.

 

[VoG]

Try

=IF(AJ3+0=L3,"Qty Same",IF(AJ3+0>L3,"Qty Increase","Qty Decrease"))

 

[nirvehex]

That worked! Thanks. What exactly does that do that it made it work?

 

[VoG]

Your AJ3 formula returns text whereas your L3 formula returns a number. Adding 0 to AJ3 result coerces it into a number making the comparison possible.

 

[mikerickson]

Alternatly

=CHOOSE(SIGN(AJ3-L3)+2, "Less", "Equal", "More")

 

[Rick Rothstein]

And one more way...

=LOOKUP(SIGN(A13-B13),{-1,0,1},{"Less","Equal","More"})

 

[Rick Rothstein]

And one more way...

=LOOKUP(SIGN(A13-B13),{-1,0,1},{"Less","Equal","More"})

Whoop! I forgot to change the addresses I used for testing to the OP's actual addresses...

=LOOKUP(SIGN(AJ3-L3),{-1,0,1},{"Less","Equal","More"})

Plus, I thought of another way...

=TRIM(MID("Less EqualMore",5*(2+SIGN(AJ3-L3))-4,5))