I have many formulas, all that look similar to this:
=Countif('2019'!$C2:$Z8,"2")/7
I would like to change each one, adding the if()
command, so that they look like this:
=IF(Countif('2019'!$C2:$Z8,"2") = 0, "", Countif('2019'!$C2:$Z8, "2")/7)
The problem is, even if I were to find & replace in a step by step process, each cell is referencing a different part of the data, so I would have to go in manually afterwards and fix them.
What is the easiest way to do this? I could also keep the old formula if there is a better way to replace all 0s with empty cells. All of the 0s are messing up my graphs.
Best Answer
If I understand your question, I think a regular expression search might do the trick. In the replace dialog, check the box for regular expression and search within formulas.
Try searching for:
(Count.\*)\\).*
And replace with:
if($1 = 0), "", $1)/7)
I think that should do it.