3

I want to check if the formula produce 0 nor not. If the output value is NOT 0 then let it be , but if it's 0 then change it to "" (empty string).

When using if(), i have to type the formula twice: if(formula<>0, formula, "") .Is there any builtin function like ifzero() which similar to ifna() and iferror()

for example in ifna() i only need to type the formula once : ifna(formula, "")

1 Answer 1

2

Keep in mind any time you are sharing a formula that we can't see what you are seeing. So while things may make perfect sense to you (who can see everything), it's a bit spotty for those of us "out here." For this reason, it is usually best to share a link to a sample spreadsheet with a small set of realistic data.

That said, I'll give it a go, sight unseen. Try this:

=ArrayFormula(IFERROR(1/(1/MMULT(if(AD99:BY2907="",0,AD99:BY2907), SEQUENCE(COLUMNS(AD:BY),1,1,0)))))

Where MMULT returns a non-zero number 1/(1/#) will equal the original number. But where MMULT returns 0, the division of 1/0 will cause an error, which IFERROR will return as null here.

I also tightened up the elements of the MMULT parameters, particularly the IF clause, which was unnecessarily complicated.

ADDENDUM (based on poster comment of 7/14)

If the result of the formula for each cell in the range will only ever be some number, null, or an error, the same structure holds true:

=ArrayFormula(IFERROR(1/(1/your-base-formula)))

2
  • Sorry the formula is actually does't matter here. Only any random formula. The question is about when using if() , you have to type the formula twice. First in the condition parameter ,second in either the 2nd or 3drd parameter. Btw i've edited my question/post to simplify it.
    – andio
    Jul 14, 2021 at 1:42
  • See the addendum to my post above. The IFERROR is a form of IF, and the 1/(1/ serves to either trigger it or not trigger it, just as happens in a regular IF.
    – Erik Tyler
    Jul 14, 2021 at 4:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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