Search code examples
if-statementgoogle-sheetsnumber-formattinggoogle-sheets-formula

Avoid double, identical arguments in IF

This image illustrates a budget, where the values for Yearly, Quartly, Monthly etc. are divided down to their per-month value and then summed up:

enter image description here

If nothing has been typed, then I don't want to see a 0. Instead, I just want the field kept empty. As shown in row 4.

Therefore I use an IF, where I first check if the calculation gives zero, A2/12+B2/3+C2>0. If it doesn't, then I show the result of A2/12+B2/3+C2; else, nothing is shown: " ".

=IF(A2/12+B2/3+C2>0 ; A2/12+B2/3+C2 ; " ")

My question is if I can avoid having the calculation twice in the formula? A2/12+B2/3+C2 is written twice. In my actual scenario, I have some quite large calculations, and it seems quite tedious and error-prone that when I have to fix something in the calculation, then I have to copy/paste it so it is identical in both places in the formula.

Is there are a smarter way to achieve this? A smarter way to perform a calculation but avoid the zero-case? A way that does not include duplicating the calculation?

Solution

  • with IF approach it's unavoidable, but there are different ways like for example:

    =REGEXREPLACE(""&(A2/12+B2/3+C2); "^0$"; )

    0