KnowledgeHub
Questions
Tags
Users
Search
Alex Rivera
|
Logout
Edit Question
Title
Body
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) Using IF statements =IF((1/1/INDEX(A,B,C))<>"",(1/1/INDEX(A,B,C)),"") CONS: Messy repetition Does anyone have any other/better ideas?
Tags (comma-separated)
Save Edits
Cancel