Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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)

share|improve this question
1  
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
2  
@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

4 Answers 4

up vote 7 down vote accepted

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)"

share|improve this answer
    
+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.

share|improve this answer

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:

example using DISPLAY_0_DEFAULT and DISPLAY_3_ARGS

share|improve this answer

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)"

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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