IF function - is there a way to avoid repeating formula

Question

Can't believe I don't know this, but is there a way to avoid repeating a formula in an if statement if the logical test is dependent on it?

i.e.

=IF((SUMIFS formula)=0,"",SUMIFs formula)

I want to replace that SUMIFS function in the false scenario with something short that will tell it to just programmatically repeat the formula it originally tested for. Repeating the formula twice has to have detrimental effects on processing speed. Negligible, maybe, but want to go for best-practices here. Thanks.

Answer 1

You can force an error like #DIV/0! and then use IFERROR, e.g.

=IFERROR(1/(1/SUMIFS_formula),"")

Answer 2

You can assign a Name to a formula and use the Name..............See:

Assigning a name to a formula

Relevant excerpt -

For example, let's suppose we frequently use a formula like: =SUM(A1:A100)-SUM(B1:B100) and this resides in A101 and is copied across many columns on row 101. It would be better in this case to create a custom formula that does this in each cell on row 101. Here is how;

1) Select cell A101 (this is vital).

2) Go to Insert>Name>Define and in the "Names in workbook" box type: SalesLessCosts

3) Now click in the "Refers to" box and type: =SUM(A1:A100)-SUM(B1:B100) then click Add.

Now you can replace the formula in cell A101 with: =SalesLessCosts. You can also copy this across row 101 and it will change its relative references just as the formula =SUM(A1:A100)-SUM(B1:B100) would. The reason it does this is all down to the fact we selected A101 before going to Insert>Name>Define and used relative references in =SUM(A1:A100)-SUM(B1:B100) when we added it to the "Refers to" box.

Answer 3

If all you need to do is hide zeroes, there is an easy way:

• Select all cells where you wish to hide zeroes
• Go into Custom Number Formatting
• Set format to "General;General;"

The custom formatting has a structure of [positive numbers];[negative numbers];[zeroes] By making the last part blank you are effectively hiding zeroes, but showing everything else.

The advantage over conditional formatting is that you can use this on any background.

A neat trick which I sometimes use is to hide the cell value completely by using a custom format of ";;;". This way you can put images inside the cells, like the conditional formatting ones, and not see the value at all.