SWITCH() does not appear to distinguish between value 0 and blank

[TomCon]

Find it truly hard to believe that Excel could treat a blank as if it were the value 0, for purpose of logic matching, but it appears to be so to me. Wonder if there is a workaround if you actually want to distinguish a blank cell from a cell with the value 0. Ideas?
Here are 3 SWITCH() formulas.
(1) =SWITCH(A1,,"zero/",1,"one",2,"two","else")
(2) =SWITCH(A1,0,"zero/",1,"one",2,"two","else")
(3) =SWITCH(JA1,"","zero/",1,"one",2,"two","else")

Now play with it by putting different values in A1.
If you put value 1 you get "one" as expected.
If you put value 2 you get "two" as expected.
If you put value 3 you get "else" as expected.
If you put value 0 you get "zero/" with formula(1) and formula(2). For formula(3) you get "else"
If you have cell A1 as blank, you get identical to the above, "zero/" for formula(1) and formula(2) and "else" for formula(3).

Hence it appears that excel is recognizing the blank cell as if it had a value in it, the value 0. Which i find rather shocking. How can this be?

If my requirement is to go to a different SWITCH branch as to if the cell is blank or 0...as both are valid (and different) observations in my data...any suggestions??

Of course the idea of finding some "dummy" value that is not otherwise valid in the data and substituting that for the blanks comes to mind. Very ugly and very difficult to maintain over time. I hope there is a better solution out there.

 

[FormR]

One workaround could be to convert the A1 value to text, you'd have to also make sure the "values" were represented as text as well. Something like..

=SWITCH(A1&"","","blank","0","zero/","1","one","2","two","else")

 

[Peter_SSs]

Hence it appears that excel is recognizing the blank cell as if it had a value in it, the value 0. Which i find rather shocking. How can this be?

There are many instances where Excel treats "" as a zero, sometimes annoying (like yours), sometimes useful (eg SUM(M1:M20) where there may be some blanks in that range)

If my requirement is to go to a different SWITCH branch as to if the cell is blank or 0...as both are valid (and different) observations in my data...any suggestions??

One suggestion: Just treat that case separately as is done in many other instances.
eg

=IF(A1="","option 5",SWITCH(A1,0,"zero/",1,"one",2,"two","else"))

 

[jasonb75]

it appears that excel is recognizing the blank cell as if it had a value in it, the value 0.

Try looking at it from a different perspective. Your cell is not blank it is empty. An empty cell has no value so it defaults to blank for text based functions or 0 for numeric functions / calculations.
When you use a function that will work with both text and numbers then the numeric default is used unless you include a logical test like the one in @Peter_SSs's suggestion to differentiate.

If your cell was actually blank (containing a formula that returns "" when there is no other valid result) then SWITCH would be able to see the difference.

 

[TomCon]

Thanks for all the comments. I wish Mr.Excel would let me mark more than one solution as "correct" as there are more than one proposal above that actually does work. But thanks for the help. I originally wanted to use SWITCH instead of nested IF as with many choices the nested IFs get a bit hard to read and keep track of. so the suggestion to use a single IF and then SWITCH in the FALSE branch is nice (Petr_SSs). Also the idea to convert everything to text within the SWITCH is quite clever as a workaround, too (FormR).

I personally do consider this behavior of Excel sort of a bug, and so people find clever workarounds and that is good to "accept the behavior and figure out how to work around it". I would say that in most "engineering logic" (programming languages, etc) the idea that comparing "nothing" to the number zero and getting a result of TRUE would be very unusual behavior that most engineers would consider as wrong. Excel does not always treat blanks as numeric 0, so Excel is not consistent in that regard, either. The example of SUM was given. But for example AVERAGE does not treat blanks as zero. In my opinion a little minefield, and it calls for "clever workarounds". Thanks for the discussion and solutions!

 

[jasonb75]

Excel does not always treat blanks as numeric 0, so Excel is not consistent in that regard, either.

Formula blanks are evaluated as text, empty cells are evaluated as either text or numeric depending on which criteria is met (formula / value type hierarchy not your personal criteria).

The numeric 0 or text null for empty cells applies when the formula refers to a single cell or when an operation is carried out on an array of cells (e.g. A1:A10*2). If the empty cells didn't have a numeric 0 value then the operation would return an error. With the 0 value the remaining rows will still yield a valid result.

When the function looks a range of cells (more than 1 cell) without additional operations then blank and / or empty cells are ignored by numeric functions.
Note that there may be some numeric functions which will not accept empty / blank cells and will return an error if any are used.

Every function follows a logical evaluation of the ranges passed to it which will vary depending on the task that the function is performing and the types of values that it will accept.

I personally do consider this behavior of Excel sort of a bug

A bug is something that doesn't function as intended by those who created the function, not something that doesn't work in the way that you think it should.

 

[TomCon]

Formula blanks are evaluated as text, empty cells are evaluated as either text or numeric depending on which criteria is met (formula / value type hierarchy not your personal criteria).

The numeric 0 or text null for empty cells applies when the formula refers to a single cell or when an operation is carried out on an array of cells (e.g. A1:A10*2). If the empty cells didn't have a numeric 0 value then the operation would return an error. With the 0 value the remaining rows will still yield a valid result.

When the function looks a range of cells (more than 1 cell) without additional operations then blank and / or empty cells are ignored by numeric functions.
Note that there may be some numeric functions which will not accept empty / blank cells and will return an error if any are used.

Every function follows a logical evaluation of the ranges passed to it which will vary depending on the task that the function is performing and the types of values that it will accept.

A bug is something that doesn't function as intended by those who created the function, not something that doesn't work in the way that you think it should.

To be a little picky, perhaps i should have said "design bug". I do not believe programming languages or statistical packages will return TRUE for someting=nothing. This seems illogical. If indeed this was intentional design, i would ask why that design was deemed advantageous. Why is it advantageous for
IF(A1=""...) to be TRUE if A1 is blank, while
SWITCH(A1,"",... is deemed FALSE when A1 is blank

What is the design motivation that lead to that being deemed "a good design". To me it is simply "confusing" and a random detail to keep in mind that has to be worked around in some situations.

 

[jasonb75]

Why is it advantageous for
IF(A1=""...) to be TRUE if A1 is blank, while
SWITCH(A1,"",... is deemed FALSE when A1 is blank

If you simply used =IF(A1,.. then it would also return FALSE. You could use A1="" or A1=0 on an empty cell, either would return TRUE.

When the cell is empty, any function that can see it as both "" and 0 will default to 0.

i would ask why that design was deemed advantageous.

Without it even the simplest of formulas would need to be error trapped. The empty cell would not be "" or 0, it would be nothing which would mean that it is not comparable to either.

I agree that there are times when it is not ideal but it is likely the lesser of evils. Unless MS choose to rewrite the core of excel so that it works the way that you think is correct, we will never know how much difference it will make, we can only speculate.

 

[TomCon]

Yes, thanks for the interesting conversation. Maybe i should have labeled my comments "IMHO". Its good to know that there are some "pros" too to what i classify in my mind at least to be "a design bug". I do agree that "the very big picture would be hard to assess". Or probably more correctly "I do not see ALL of the implications in the very big picture". But for now, it is to me at least "a somewhat inconsistent tidbit to keep in mind in the realm of these formulas as to which will return TRUE/FALSE as a comparison result". Thanks!