# In Excel, is there a less repetitive way of writing `=IF(<Expression A>=0, “(none)”, <Expression A>)`?

In Excel, I have a long, complex expression (Expression A) that I am using in a formula. I want this formula to return the value of Expression A except in the case where that expression is equal to some specific value (0 for example), in which case it returns something else, like "(none)".

Is there a way of writing `=IF(<Expression A> = 0, "(none)", <Expression A>)` that wouldn't require me to repeat the entirety of Expression A twice? (Sort of like how `IFERROR` works, except with a custom condition)

-
Would testing the value of Expression A held in a different cell count? –  pnuts Jan 17 '13 at 21:53
No, because Expression A is dependent on the position of the cell that its in. I.E. `IFERROR(INDEX(Table1,ROWS(C\$2:C2),COLUMN(C2)), IFERROR(INDEX(Table2,ROWS(C\$2:C2)-ROWS(Table1),COLUMN(C2)), ""))` (This is the actual formula I'm using for Expression A.) –  Ajedi32 Jan 17 '13 at 21:58
Now I'm even less sure what you are after! –  pnuts Jan 17 '13 at 22:05
=/ Sorry, I guess that was pretty confusing. That's just the expression that I'm using as expression A. The point is that it contains relative references, (I.E. C2), so if I put it in a different cell it will get a different value. –  Ajedi32 Jan 17 '13 at 22:20
@Ajedi32: I still do not fully understand why you cannot calculate expression A in one cell and have the check for 0 in the next cell. Worst case you need to offset your column by one, i.e. `COLUMN(C2)-1`. The advantage apart from a cleaner formula is that it halves calculation time, as the operation has to be carried out only once. And to avoid cluttering the visual, simply hide column with expression A. –  Peter Albert Jan 17 '13 at 22:46

In the case where you want to display something else instead of zero specifically you could possibly force an error when the expression resolves to zero by dividing 1 by that result, e.g. with IFERROR function - available in Excel 2007 or later

`=IFERROR(1/(1/<Expression A>),"(none)")`

or use a custom format where zero displays as "(none)", e.g.

`0;-0;"(none)"`

-
+1 for Clever use of divide by zero. –  BillN Jan 17 '13 at 22:49
+1 Nice trick to force error –  chuff Jan 17 '13 at 23:52

'No', subject to the usual caveat, not without VBA.

But by a different approach you may save yourself some trouble, say with conditional formatting.

-

This can be done fairly easily in VBA.  Here are a couple of example functions:

```Function DISPLAY_0_DEFAULT(exp_val)
If exp_val = 0 Then
DISPLAY_0_DEFAULT = "(none)"
Else
DISPLAY_0_DEFAULT = exp_val
End If
End Function

Function DISPLAY_3_ARGS(exp_val, test_val, display_val)
If exp_val = test_val Then
DISPLAY_3_ARGS = display_val
Else
DISPLAY_3_ARGS = exp_val
End If
End Function
```

The `DISPLAY_0_DEFAULT` function does what you described in your question, taking “Expression A” as an argument.  The `DISPLAY_3_ARGS` function does the same thing, except it lets you specify the “specific value” (to compare against) and the value to display if “Expression A” matches the “specific value”.  Here is an illustration of their use:

-

There are a couple of options:

### Hidden Column

You can write `<Expression A>` to a separate cell in a hidden column, then refer to that.

``````    Column A           Column B
(hidden)

=<Expression A>  =IF(A1=0,"(none)",A1)
``````

### Number Formatting

Just use the formula `=<Expression A>`, don't worry about the `IF` function.

Set the cell's formatting to Custom: `0;-0;"(none)"`

-
Any reason for the down vote? –  Hand-E-Food Jan 20 '13 at 22:41