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.

I like to keep my Excel spreadsheets fairly clean. So, often times I'll end up writing statements like this:

IF([formula x]=[value],"",[formula x])

What that essentially says is that if the formula matches a given criteria, do not display the result - otherwise, display the result.

In some cases, [formula x] is repeated down an entire column of cells and each cell has a reference in [formula x] that points to the cell above it. To avoid formula errors, I have to add another layer like so:

IF(C2="","",IF([formula x]=[value],"",[formula x])

However, especially when [formula x] is very long, this can result in an end formula that looks much more complicated than it is and becomes much harder to troubleshoot and maintain than it should be.

Here's one horrific example...

Base formula:

=IF(A3>=$E$11,C2+(C2*($F$2/12))-$E$9,C2+(C2*($F$2/12))-$E$7)

Add conditional blanking:

=IF(C2="","",IF(IF(A3>=$E$11,C2+(C2*($F$2/12)-$E$9),C2+(C2*($F$2/12))-$E$7)<=0,"",IF(A3>=$E$11,C2+(C2*($F$2/12))-$E$9,C2+(C2*($F$2/12))-$E$7)))

An already very long formula is more than doubled in size, just so that I can display a blank (or any other chosen value, for that matter) when that same formula meets a given condition. If I try to do this across an entire row, depending on the starting formula, I could easily run into circular reference errors.

Is there any way to self-reference an existing formula or argument within the same cell, or maybe another function or feature that can be used to achieve this result more cleanly?

A function that does what I'm looking for might be like this:

=FnName([base formula],[match condition],[condition result])

Argument 1 is the base formula, argument 2 is the condition I'm interested in matching. Argument 3 is the result to display if the condition matches. In the case the condition is not matched, the function returns the result of the base formula.

share|improve this question
add comment

3 Answers

Have you thought about evaluating the formula in a hidden cell (or in a cell on another worksheet) and then do your conditional blanking based on the value in the hidden cell instead of having to enter the formula twice. I don't know the entire context in which you are working but I have done something similar with some success in the past.

share|improve this answer
    
Nice idea, but this only really adds to the confusion when I go to review the spreadsheet later on, since the primary function of the cell is no longer represented within itself. –  Iszi May 10 '12 at 15:02
add comment

You could resort to a VBA function to clean up the formula syntax. For example, you can put something like this in a module (Press Alt+F11, then Insert >> Module):

Option Explicit
Public Function BLANKIF(checkcell As String, notb As Variant, Optional checkcond As String) As Variant
If checkcell = checkcond Then
    BLANKIF = ""
Else
    BLANKIF = notb
End If
End Function

So, to use this to apply conditional blanking to

=IF(A3>=$E$11,C2+(C2*($F$2/12))-$E$9,C2+(C2*($F$2/12))-$E$7)

You would use

=BLANKIF(C2,IF(A3>=$E$11,C2+(C2*($F$2/12))-$E$9,C2+(C2*($F$2/12))-$E$7))

If you wanted to blank the cell if C2= "omg", you would add a third optional argument:

=BLANKIF(C2,IF(A3>=$E$11,C2+(C2*($F$2/12))-$E$9,C2+(C2*($F$2/12))-$E$7),"omg")
share|improve this answer
add comment

You can use Conditional Formatting to achieve you aim.

Remove all the extra stuff from the formula, leaving only the base formula

Apply a Format only cells that contain condition with your blnaking criteria

When the Blanking condition is true apply a Custom Number format of ;;;

Alternative for Excel 2007 or later

IFERROR(value, value_if_error)

From Excel help:

Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Use the IFERROR function to trap and handle errors in a formula.

share|improve this answer
    
I'm not quite sure this will work in some of the more advanced situations I've described above. The particular problem here is that this won't (unless I'm missing something) actually blank the cell's resulting value - it will only make the cell appear blank. So, formulas requiring values that don't exist will still error out. –  Iszi Jul 12 '12 at 20:47
    
You are right, this does hide the error, not prevent it - I took your statement do not display the result - otherwise, display the result literally. I've added an alternative (but only for Excel 2007 or later) –  chris neilsen Jul 12 '12 at 21:12
    
Wait a sec... IFERROR defaults to the value of the successfully-run formula? I've always used a nested IF/IFERROR because I didn't think IFERROR did anything for non-erroring formulas. Example: =IF(IFERROR(A1+1,1)=1, "Error", A1+1) So, it sounds like the ideal solution is actually a combination of IFERROR and conditional blanking. –  Iszi Jul 12 '12 at 21:18
2  
Just to be clear: IFERROR(A1+1,"Error") will return Error if A1+1 is an error, otherwise it will return the result of A1+1. No repitition of the tested formula or nesting is required. –  chris neilsen Jul 12 '12 at 21:42
add comment

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.