A recurring Excel problem I have is formulas such as INDEX(array,row,column)
that return 0 when there's no result, rather than returning blank.
What is the best way to change the zero result to blank?
Here are approaches that I have tried so far:
1) Using division by zero. If INDEX returns 0, I cause an error that I then filter out.
=IFERROR(1/1/INDEX(A,B,C),"")
CONS: Makes the formula more messy and hides errors you may want to see.
2) Using custom formatting
0;-0;;@
CONS:
1) can't simultaneously apply date format
2) It doesn't work with conditional formatting when it comes to checking for blank cells (there is still the value of zero, it's just not shown)
3) UsingIF
statements
=IF((1/1/INDEX(A,B,C))<>"",(1/1/INDEX(A,B,C)),"")
CONS: Messy repetition
Does anyone have any other/better ideas?
You can create your own user defined functions in a module within Excel such as (from memory, so may need some debugging, and the syntax may vary among Excel versions as well):
Public Function ZeroToBlank (x As Integer) As String
If x = 0 then
ZeroToBlank = ""
Else
ZeroToBlank = CStr(x)
End If
End Function
You can then simply insert =ZeroToBlank (Index (a,b,c))
into your cell.
There's a nice tutorial on just this subject here.
The basic steps are:
Tools -> Macro -> Visual Basic Editor
.Insert -> Module
.=ZeroToBlank (<<whatever>>)
<<whatever>>
is the value you wish to use blank for if it's zero.Note that there may be minor variations depending on which version of Excel you have. My version of Excel is 2002 which admittedly is pretty old, but it still does everything I need of it.