Efficiently replace 0 with NA

Question

I am trying to find a way to efficiently replace zero with NA() in an Excel formula. I know the following works:

=IF(FORMULA = 0, NA(), FORMULA)

But my problem is that this will cause FORMULA to execute twice. I have cases where this may be a longer =SUMIFS() in a giant table.

So I would like for:

• No VBA
• Only have the base FORMULA calculate once

I thought at first to try to use SUBSTITUTE() to replace "0" with something that would trigger a value error, and then just wrap all of that within IFERROR(). That obviously fails since SUBSTITUTE() cannot be forced (to my knowledge) to check for full word match (so 100 would trigger the error).

Is this possible? I have thought for years it was not, but decided to put some thought back into it.

Answer 1

Calculation and display can occur in two different locations - why merge operations when you don't need to?

A1 - =Formula
B1 - =If(A1 = 0, NA(),A1)

Answer 2

The general answer is

=IFERROR(f'(f(FORMULA)), AlternateValue)

where f(FORMUALA) returns an error (any error will do) for values of FORMULA that you want an alternate value for.

And f'(...) is the inverse of f(...), so f'(f(FORMULA)) returns FORMULA for other values.

Ensure the first function is applied to the whole of FORMULA. Enclosing it in () guarantees that.

Secondly, ensure the two functions are applied in the correct order, also achieved using ().

In this case you want an alternate value for 0 so you can use

=IFERROR(1/(1/(FORMULA)), NA())