Using Logical Operators - All of th results are True, but they shouldnt be

[clrichert]

Hello Everyone,

I have spent way too much time trying to figure this out, lol...and I appreciate in advance for any and all help you can provide.
[TABLE="width: 889"]
<tbody>[TR]
[TD="colspan: 9"]I am using this formula [=IF(A2>0,"MPY","")] to give me either a blank, or the text "MPY". The issue is that cell "A2" also has a formula. The results are always "TRUE" even when the cell returns no value. Is there a work-around for this issue?

Thanks again!

A2 Begins at 1.00
[TABLE="width: 616"]
<colgroup><col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" span="11"> <tbody>[TR]
[TD="class: xl71, width: 141, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , colspan: 2"]February[/TD]
[TD="class: xl71, width: 136, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , colspan: 2"]March[/TD]
[TD="class: xl71, width: 136, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , colspan: 2"]April[/TD]
[TD="class: xl71, width: 136, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , colspan: 2"]May[/TD]
[TD="class: xl71, width: 136, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , colspan: 2"]June[/TD]
[TD="class: xl71, width: 136, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , colspan: 2"]July[/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"]1.00[/TD]
[TD="class: xl74, bgcolor: transparent"]MPY[/TD]
[TD="class: xl73, bgcolor: transparent"]0.36[/TD]
[TD="class: xl74, bgcolor: transparent"]MPY[/TD]
[TD="class: xl76, bgcolor: transparent"]-[/TD]
[TD="class: xl74, bgcolor: transparent"]MPY[/TD]
[TD="class: xl73, bgcolor: transparent"]-[/TD]
[TD="class: xl74, bgcolor: transparent"]MPY[/TD]
[TD="class: xl73, bgcolor: transparent"]0.14[/TD]
[TD="class: xl74, bgcolor: transparent"]MPY[/TD]
[TD="class: xl73, bgcolor: transparent"]0.36[/TD]
[TD="class: xl74, bgcolor: transparent"]MPY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 13"]
[TABLE="width: 1112"]
<tbody>[TR]
[TD]This is the formula to get my number:[/TD]
[/TR]
[TR]
[TD]IF(ISERROR(VLOOKUP('All-in-One'!$A$3,Coupon_Table,6,0)),"",IF(LEN(VLOOKUP('All-in-One'!$A$3,Coupon_Table,6,0))=0,"-",VLOOKUP('All-in-One'!$A$3,Coupon_Table,6,0)))[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]This is the formula to get my text:[/TD]
[/TR]
[TR]
[TD]IF(A2>0,"MPY","")[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]The months that have returned with a "-" have no data to return, it is only there to make it easier to read the table.[/TD]
[/TR]
[TR]
[TD]I would like for the "MPY" to populate only when a value has been returned to the left of its cell.[/TD]
[/TR]
[TR]
[TD]As is stands now, it will always populate. I understand why, the order of text value vs. numerical value, but how to get around this issue is beyond me.[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col><col span="12"></colgroup>[/TABLE]

Hopefully I have explained this adequately. Thank again!

 

[clrichert]

Sorry everyone, I didn't realiaze that the font show up like that...here it is in black.

[TABLE="width: 635"]
<colgroup><col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" span="11"> <tbody>[TR]
[TD="class: xl75, width: 167, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , colspan: 2"]February[/TD]
[TD="class: xl75, width: 136, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , colspan: 2"]March[/TD]
[TD="class: xl75, width: 136, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , colspan: 2"]April[/TD]
[TD="class: xl75, width: 136, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , colspan: 2"]May[/TD]
[TD="class: xl75, width: 136, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , colspan: 2"]June[/TD]
[TD="class: xl75, width: 136, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , colspan: 2"]July[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]1.00[/TD]
[TD="class: xl72, bgcolor: transparent"]MPY[/TD]
[TD="class: xl71, bgcolor: transparent"]0.36[/TD]
[TD="class: xl72, bgcolor: transparent"]MPY[/TD]
[TD="class: xl74, bgcolor: transparent"]-[/TD]
[TD="class: xl72, bgcolor: transparent"]MPY[/TD]
[TD="class: xl71, bgcolor: transparent"]-[/TD]
[TD="class: xl72, bgcolor: transparent"]MPY[/TD]
[TD="class: xl71, bgcolor: transparent"]0.14[/TD]
[TD="class: xl72, bgcolor: transparent"]MPY[/TD]
[TD="class: xl71, bgcolor: transparent"]0.36[/TD]
[TD="class: xl72, bgcolor: transparent"]MPY[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

To Excel, a text value is greater than a number value... since your dash is a text value, it is considered greater than 0. Maybe this formula will work for you...

If all numbers are always greater than 0...
=IF(ISTEXT(A2),"MPY")

If number can be negative, zero or positive...
=IF(ISTEXT(A2),"",IF(A2>0,"MPY",""))

 

[joeu2004]

Crossposted in https://www.excelforum.com/excel-fo...he-results-are-true-but-they-shouldnt-be.html. Although Rick's solution is not unlike mine (I used IFERROR or ISNUMBER), I think leelnich's response in the crossposted thread is best:

=IF(N(A2)>0, "MPY", "")

 

[Rick Rothstein]

I think leelnich's response in the crossposted thread is best:

=IF(N(A2)>0, "MPY", "")

I would agree, although that gave me an idea (not sure why) for a completely different approach (only one function call instead of two)...

=TEXT(A2,"\MP\Y;;;")

 

[clrichert]

That's just incredible. Those both work perfectly, thanks guys!!

 

[Gerald Higgins]

joeu2004, Rick (and indirectly, leelnich) - NEAT !

Rick - I understand basically how your solution works - essentially it's number formating - but what is the function of the " \ " characters ?
Is it to prevent interpretation of the values as Months / Years ?

 

[Rick Rothstein]

Rick - I understand basically how your solution works - essentially it's number formating - but what is the function of the " \ " characters ?
Is it to prevent interpretation of the values as Months / Years ?

Yes, exactly. You have two choices for embedding text into the format pattern argument... quote the text (which in a VB text string means you use two quote marks on each side of the text) or backslash the individual meta-characters that the TEXT function uses for its own purposes. If you have a lot of text, then (double-double) quote it, with only two meta-characters, I find it easier (and shorter) to backslash them. Way, way back in my UNIX days, working with Regular Expressions, the backslash (when used to remove the special meaning of a meta-character) was called the "escape character"... I am not sure if that term is still used or not.

 

[Gerald Higgins]

Thanks Rick !