Looking for solution to shorten an IF formula

Solution 1:

Another approach is to use double inversion:

=IFERROR(1/(1/really_long_formula)+A1,"")

If the really_long_formula evaluates to 0, you will get a divide by zero and the IFERROR() will catch it!

Please note that the usual way (and best way) to handle this requirement is with a helper cell.

Solution 2:

The obvious answer is to put the formula in a work cell, away from the main sheet. For example, if you use H1, set it to:

=!X!

Your formula then becomes:

=IF(H1<>0,H1+A1,"")

This is typical of what one would do in any other programming language.

Solution 3:

Do you really need the result to be "" in the false case? If you only need the cell to look blank (e.g. you won't use something like =ISNUMBER() on it later), you can use conditional formatting to hide the contents in the false case.

The conditional format you'll apply to the cell so that it doesn't display anything is the custom format "", like this (it's in Portuguese but you get the idea):

The formula in the cell will be, as expected, simply =!X!+A1.

The conditional formatting formula could be =!X!=0, but that would force recalculation of !X!, which you don't want (your "Point 2"). It's better to harness the cell itself by using =B1=A1 (supposing our cell is B1) -- that would imply !X! = 0.

_{Even if you need the cell content to actually be "", usually minor alterations can be made in the worksheet so that this approach can be used. If that's the case, leave a comment describing the situation.}

Solution 4:

I do not have possibility to add a help column. Thanks anyway.

If you can't add a helper column, why not add a whole worksheet? There are several advantages to this:

• Your helper cell doesn't take up space that might otherwise be used, because it's in a separate worksheet.
• You can name the cell then address it by name, e.g. =IF(X<>0,X+A1,"")
• If you need to do this in more than one cell, you can:
  1. Rename the helper worksheet "helper"
  2. Put the helper equation in the same cell as the main equation (let's call it D5.
  3. Address the cell as helper!D5 in the main sheet.
• You can hide the helper sheet if necessary.
• It is faster than evaluating twice.
• It doesn't lose precision.

The disadvantages I can see are:

• You have to reference unnamed cells in the main sheet as sheetname!D5 instead of just as D5.
• Now the formula is in two parts instead of one.
• Worksheet proliferation.

Weighing up the advantages and disadvantages, I think that for many use cases this is a good solution. There exist situations where it is not optimal, though I can't think of any at the moment.