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.

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 February March April May June July 1.00 MPY 0.36 MPY - MPY - MPY 0.14 MPY 0.36 MPY <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> </tbody>
This is the formula to get my number: 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))) This is the formula to get my text: IF(A2>0,"MPY","") The months that have returned with a "-" have no data to return, it is only there to make it easier to read the table. I would like for the "MPY" to populate only when a value has been returned to the left of its cell. 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. <tbody> </tbody><colgroup><col></colgroup>

<tbody>
</tbody><colgroup><col><col span="12"></colgroup>

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.

February	March	April	May	June	July
1.00	MPY	0.36	MPY	-	MPY	-	MPY	0.14	MPY	0.36	MPY

<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> </tbody>

 

[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 !