6

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.

Iszi
  • 13,775
  • 1
    You're looking for named ranges. They can be used to store formulas that you can reference in any way you like. – wbeard52 Aug 25 '15 at 14:10

3 Answers3

3

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.

Jim St. Clair
  • 31
  • 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
3

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")
Excellll
  • 12,717
1

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.

chris neilsen
  • 4,333
  • 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