Solved Excel - If greater or less than zero then YES or NO

October 6, 2017 at 09:01:09
Specs: Windows 7
I am sure I am making it harder than it is. I ham banging my head on a wall after 2 hours of trying to figure this out with only failing attempts trying multiple formulas and conditional formatting

I want cell F12 to have the result of the word YES or NO based on cell E12
If E12 is less than zero (negative amount) then F12 should be NO
If E12 is greater than zero (positive amount) then F12 should be YES

OR

If E12 is highlighted red then F12 should be NO
iF E12 is highlighted green then F12 should be YES

I prefer the 1st option but am willing to color code to get the result I need.

Please help


See More: Excel - If greater or less than zero then YES or NO

Reply ↓  Report •

#1
October 6, 2017 at 09:33:41
The formula method is pretty straight forward, with one minor issue: You specified NO for less than zero and YES for greater than zero, but it is not clear what you want as a result when E12 equals zero.

This formula should meet your requirements and return a blank cell if E12 = 0

=IF(E12<0,"NO",IF(E12>0,"YES",""))

These formulas will return NO only when E12 is less than zero.

=IF(E12<0,"NO","YES")
=IF(E12>0,"YES","NO")

Basing the result in F12 on the color of E12 is a bit more difficult. There are 2 ways (that I know of) to have a specific value (string or number) appear in a cell based on the format of another cell.

1 - Use a macro. The macro could monitor the fill color (or condition that set the fill color) of E12 and then put YES or NO in F12 based on what it sees.

2 - Use Conditional Formatting to hide part of a string by using 2 rules. Here's what I mean:

First, put Yes No in F12 and manually change the font color of each word so that it looks like this:

YES NO

Then use these 2 Conditional Formatting rules on F12:

=E12<0 (Fill with Red)
=E12>0 (Fill with Green)

Based on the value in E12, the Conditional Formatting fill color of F12 will hide either the Green word (NO) or the Red word (YES).

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
October 6, 2017 at 09:38:00
✔ Best Answer
See how this works:

=IF(E12<0,"No","Yes")

If E12 is less than Zero, then NO

If E12 is not less than Zero, IE any positive number including Zero
then the answer must be YES

If you want Zero to be included in the NO answers then this:

=IF(E12<=0,"No","Yes")

If E12 is less than or equal to Zero, then NO

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#3
October 6, 2017 at 09:48:46
Time to pick a nit. ;-)

re: any positive number including Zero

Perhaps you meant to say any positive number and Zero.

Unless you live is France, positive numbers don't typically include zero.

Here is an interesting discussion regarding the designation of zero as a positive, negative or unsigned number:

https://math.stackexchange.com/ques...

(Just having some fun here!)


message edited by DerbyDad03


Reply ↓  Report •
Related Solutions


Ask Question