if statement gives a value for blank cells-CodePudding

I am categorizing my excel output by the following if formula: =IF(M13>0,8;">80%";IF(M13>0,5;"80%-50%";IF(M13>0,5;"50%-20%";"<20%")))

My data has some blank cells, and I would like my formula to return a blank cell in this case. At the moment, however, the formula returns a <20% value (see picture). How can I fix this?

CodePudding user response：

Add a check for blank cells:

=IF(M12="";"";IF(M12>0,8;">80%";IF(M12>0,5;"80%-50%";IF(M12>0,2;"50%-20%";"<20%"))))

Here is the formula version where the formula separator is , and the decimal separator is .:

=IF(M12="","",IF(M12>0.8,">80%",IF(M12>0.5,"80%-50%",IF(M12>0.2,"50%-20%","<20%"))))

CodePudding user response：

The answers and proposed solutions here work in your specific case. I wanted to provide a more generalized approach to prevent this problem with any nested IF() structure, that is not unique to just your use case.

The potential problem with starting your formula with a IF(M13=""...) condition is that it is not generalized. You always want to skip this cell if it has a space, or a non-printable character, or a text value, not just if it is blank. Any non-numeric or out-of-range value should result in a "".

Thus the generalized form of your formula could be:

=IF(ISNUMBER(M13); IF(M13>0,8; ">80%";IF(M13>0,5; "80%-50%";IF( M13>0,2; "50%-20%"; "<20%")));"")

Or, for readability, I have added spaces and alt-returns as non-breaking carriage returns to the exact same formula as well:

=IF(ISNUMBER(M13);
IF(M13>0,8; ">80%";
IF(M13>0,5; "80%-50%";
IF( M13>0,2; "50%-20%"; "<20%"
)));"")

The US English notation for this would be:

=IF(ISNUMBER(M13), IF(M13>0.8, ">80%",IF(M13>0.5, "80%-50%",IF( M13>0.2, "50%-20%", "20%"))),"")

IF you have Excel 2019 or later

A better approach is to use the recent IFS() function as a way of creating the IF-THEN-ELSEIF logical construct instead of nested IF()s. Here it is in the same alt-return notation for readability:

=IFS(
NOT(ISNUMBER(M13));"";
M13>0,8; ">80%";
M13>0,5; "80%-50%";
M13>0,2; "50%-20%";
TRUE   ; "<20%"
)

Which without the alt-returns is:

=IFS(NOT(ISNUMBER(M13));"";M13>0,8;">80%";M13>0,5;"80%-50%";M13>0,2; "50%-20%";TRUE;"<20%")

The conditions are evaluated and tested in order, so you create one final "TRUE" condition/result pair that means "if you made it past all the other tests and still ended up here, then the result is (a) numeric and (b) less than or equal to 0,2."