If function is interpreting a "-" as a value greater than a number. Why?

**yumyumdimsum** · 01-07-2013, 02:04 AM

I have a simple If formula that looks like

Formula:

Please Login or Register  to view this content.

My value in C5 is a dash, not a number. But the result of the If formula is positive as if the dash was a value greater than 5000, resulting in it displaying the contents of B1.

I'm using an IFERROR function in C5 to return a "-" if it is an error. Is there something else I can use besides a number? For example, I can't say IFERROR return a 0 as that would mess with the rest of my sheet. I'd like it to return something with no value like a simple blank space or a dash. A simple blank space also has the same result as the dash though.

Sample attached.

**FDibbins** · 01-07-2013, 02:18 AM

try changing the formula to this...
=IF(AND(ISNUMBER(C5),C5>5000),B5,"-")

**benishiryo** · 01-07-2013, 02:18 AM

hi again. not sure if i really understood what you need and how putting a zero in the IFERROR mess up the rest. does this help?
=IF(AND(C5>5000,C5<>"-"),B5,"-")

**:) Sixthsense :)** · 01-07-2013, 02:23 AM

I am unable to understand your expectation

Can you please describe what is your outcome?

**Ace_XL** · 01-07-2013, 02:44 AM

Maybe..

Please Login or Register  to view this content.

**yumyumdimsum** · 01-07-2013, 02:57 AM

Gosh I must not have explained that very well.. apologies. The outcome I wanted in the sample I uploaded, was for column D to return what was in the corresponding cell from column B, only if the corresponding cell from Column C had a value greater than 5000.

The problem was that, even though I didn't have a value greater than 5000 in column C, in fact I had a "-" which is not even a numeric value, the formula from column D was still interpreting it as a value greater than 5000.

FDibbins suggestion of using the ISNUMBER function remedied the problem. Although I'm still not sure why a dash can be considered a numeric value - at least from now on I know I should use the ISNUMBER function.

Thanks all for your help.

**:) Sixthsense :)** · 01-07-2013, 03:15 AM

The way you written the formula made the difference.

The "-" is not at all considered as Numeric Value.

Just put this formula in D5 cell and see what's happening, your formula stays as it is but just added another if to ensure whether D5 is Number.
=IF(ISTEXT(C5),"Cell Has Text Value",IF(C5>5000,B5,"-"))

In your formula only two conditions True or False. C5>5000 will get True or False. Here C5 is the Text Data "-" so will return False. So it should not be meant as "-" is considered as Numeric and it is >5000 value.

Hope it's clear to you

**vlady** · 01-07-2013, 03:18 AM

maybe???

Formula:

Please Login or Register  to view this content.

**Pepe Le Mokko** · 01-07-2013, 04:00 AM

The problem was that, even though I didn't have a value greater than 5000 in column C, in fact I had a "-" which is not even a numeric value, the formula from column D was still interpreting it as a value greater than 5000.

If you use the TYPE(cell_reference) function, you’d find that a text field returns a 2 and a number (or date) cell returns a 1. All equality comparisons are identity comparisons in Microsoft Excel, which means they compare type and value. Inequality comparisons, compare type and return a value based on the type number and never compare value when the types differ. There are three other types: (1) A logical value is 4; (2) An error message is 16; and (3) An array is 64.