Number Formatting Problems

nirvehex

Active Member
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!

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Try

=AJ3+0=L3

mikerickson

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

nirvehex

Active Member
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

Legend
Try

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

nirvehex

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

VoG

Legend
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

MrExcel MVP
Alternatly

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

Rick Rothstein

MrExcel MVP
And one more way...

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

Rick Rothstein

MrExcel MVP
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))

Replies
12
Views
582
Replies
5
Views
144
Replies
11
Views
520
Replies
3
Views
230
Replies
3
Views
271

1,190,800
Messages
5,982,984
Members
439,810
Latest member
phobo3s

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back