1

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.

  • Add more details about how your formulas are referencing other part of the data. Are all the referred ranges of the same size? Are all of them on the same relative position? – Rubén Jan 2 at 17:15
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.