Show other result for 0 and empty cell with a formula in an excel report
Many extractions I did contain some empty cells or cells with 0. One of the options is to delete them but in some situation, I need to keep them. What I do most, it is to put another value, for example, all cells with zero, I will put “none” and for empty ones, “not related”.
When I use the formula ?
When I need to display another result if the cell is 0 or empty.
How to use the formula ?
The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".
How is/are the formula(s) ?
=IF()
=VLOOKUP()
This formula is telling to display an empty cell if the value is 0.
=IF(VLOOKUP(A2,A:B,2,0)=0,"",VLOOKUP(A2,A:B,2,0))
In fact, you can ask it to put anything you want, if you want to display “nothing” for all 0 values, the formula will be:
=IF(VLOOKUP(A2,A:B,2,0)=0,"nothing",VLOOKUP(A2,A:B,2,0))
Of course, if you prefer to tell it to display the empty cell or “nothing” for another value, just change the “=0” to for instance “=5”.
This formula works the same way if the cell value is empty:
=IF(VLOOKUP(A2,A:B,2,0)="","empty",VLOOKUP(A2,A:B,2,0))
Instead to put the cell reference A2, you can put the name:
=IF(VLOOKUP("chocolate",A:B,2,0)=0,"",VLOOKUP("chocolate",A:B,2,0))
The point is that if you have 2 or more “chocolate”, the formula will only take the first one that it will find. Putting the name is better only if the name is unique and not duplicate.
Interesting Topics
-
Be successfully certified ITIL 4 Managing Professional
Study, study and study, I couldn’t be successfully certified without studying it, if you are interested...
-
Be successfully certified ITIL 4 Strategic Leader
With my ITIL 4 Managing Professional certification (ITIL MP) in the pocket, it was time to go for the...
-
Hide visual and change background color based on selection
Some small tricks to customize the background colour of a text box...
-
Stacked and clustered column chart or double stacked column chart
In excel, I use a lot the combination of clustered and stacked chart...