Click here for important information about Computing.net.

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

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

There is difference between 0 and Blank, so you need to be careful. The why C6 is blank can make a difference in what your formula returns.reasonFor 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

reason, then try this:any=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.

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")))

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

DOES NOTcheck for all possible conditions of cell C6If 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

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

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 elsemay determine that the use of a formula in C6 is desired.

Using the "simpler" formulacouldresult in an error, and they would never know it.Best to cover ALL the bases, just in case.

MIKE

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 elsemay 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.

Ask Your Question

Weekly Poll

Do you think Intel's new hybrid chips will better compete with Apple/ARM?

Yes (14) | ||

No (14) | ||

I don't know (15) |

Discuss in The Lounge

Poll History