Excel IF statement help to return a blank

October 5, 2015 at 16:46:05
Specs: Windows 7
This formula is a working formula that returns the correct value based on what is entered in C6
=IF(C6<=56000000,"Small",IF(C6>=125000000,"Large","Medium"))

However, when C6 is zero or blank the formula returns the value Small; I want it return nothing ""

How can I adjust formula to return blank when C6 is zero or blank


See More: Excel IF statement help to return a blank

Report •


#1
October 5, 2015 at 16:54:14
You will need to use the OR() function if you want to check for both Blank and Zero,
one is not equivalent to the other.

Something like this should work:

=IF(OR(C6="",C6=0),"",IF(C6<=56000000,"Small",IF(C6>=125000000,"Large","Medium")))

MIKE

http://www.skeptic.com/


Report •

#2
October 5, 2015 at 17:25:02
There is difference between 0 and Blank, so you need to be careful. The reason why C6 is blank can make a difference in what your formula returns.

For example, this formula will return a blank cell for 3 conditions of C6:

1 - C6 contains a 0 (e.g. You entered 0 in the cell)
2 - C6 contains a formula that evaluates to 0 (e.g. =A1-A1)
3 - C6 is physically empty (e.g. You selected C6 and hit the Delete key)

=IF(C6=0,"",IF(C6<=56000000,"Small",IF(C6>=125000000,"Large","Medium")))

However, if C6 is blank due to a formula that returns a blank cell, then the cell will be blank, but it won't equal to 0.

e.g. If C6 contains this formula and A1 = 4, then C6 will be blank:

=IF(A1=4,"",A1)

However, the Nested IF formula above will not consider C6 to be equal to 0 and therefore won't return a blank cell.

If you really want to check for both 0 and a blank C6 that is blank for any reason, then try this:

=IF(OR(C6=0,C6=""),"",
IF(C6<=56000000,"Small",
IF(C6>=125000000,"Large","Medium")))

In this case, you are actually checking for 0 or "". If either one of those is true, the Nested IF will return a blank cell.

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


Report •

#3
October 5, 2015 at 18:18:17
Thank you DerbyDad03, I read youra nalysis and used this formula and it worked like a champ:

=IF(C6=0,"",IF(C6<=56000000,"Small",IF(C6>=125000000,"Large","Medium")))


Report •

Related Solutions

#4
October 5, 2015 at 19:13:14
=IF(C6=0,"",IF(C6<=56000000,"Small",IF(C6>=125000000,"Large","Medium")))

This formula DOES NOT check for all possible conditions of cell C6

If Cell A1 contains the number 4
and
In cell C6 you enter the formula: =IF(A1=4,"",A1)

Then cell C6 is Blank but it DOES NOT equal Zero.

So the formula above will return the word LARGE

The formula both me and DerbyDad03 offered as a solution is:

=IF(OR(C6="",C6=0),"",IF(C6<=56000000,"Small",IF(C6>=125000000,"Large","Medium")))

MIKE

http://www.skeptic.com/


Report •

#5
October 5, 2015 at 20:16:09
It's possible that the "simpler" formula works for the OP because C6 is a manual entry cell and will never be blank as the result of a formula.

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

message edited by DerbyDad03


Report •

#6
October 5, 2015 at 20:46:53
DerbyDad,

It's possible that the "simpler" formula works for the OP because C6 is a manual entry cell and will never be blank as the result of a formula.

This is true, but how long is never?
At some time in the future, some one else may determine that the use of a formula in C6 is desired.
Using the "simpler" formula could result in an error, and they would never know it.

Best to cover ALL the bases, just in case.

MIKE

http://www.skeptic.com/


Report •

#7
October 6, 2015 at 06:05:31
Mike, I respectively disagree.

How often do you write formulas that are more complex than required on the off chance that other formulas or input cells will change in the future?

I can certainly see covering all bases in cases such as our example of a formula that might return a 0 or a blank, but to prepare for a cell to go from a manual entry to a formula seems like a bit of overkill.

For that matter, why don't we prepare for the situation where C6 is no longer the input cell? At some time in the future, some one else may determine that the use of a C6 is no longer desired.

How far back do we look, trying to cover all possible future changes to formulas throughout the spreadsheet?

If someone makes a change to a spreadsheet, it is their responsibility to be aware of the impact by testing all possible outcomes of that change. I don't see how we can prepare for the gazillions of possible changes that could impact any given formula.

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


Report •


Ask Question