Methods In Excel

Have you ever in your spreadsheets wanted to show a value only under certain conditions?
We’ve created two functions show-functions (DOWNLAOD THE WORKBOOK HERE)  that will do exactly this. The first accepts a value (which can be a simple value, a cell reference, or a calculation) and a condition as a string (like the MATCH function). It then evaluates the condition on the value. If the condition is true, the value is returned, otherwise an empty string is returned. You can also specify a different return value if the condition is not met by setting a third parameter.

The second function does exactly the same, except that it will return the value if the condition is NOT met, and an empty string (or the third parameter) if the condition IS met.

Here’s an example of how you might use these functions. This is a formula used in one of our spreadsheets:

=IF((SUMIFS(Payments!$D$2:$D$1000,Payments!$C$2:$C$1000,"="&B$1,

OFFSET(Payments!$D$2:$D$1000,0,MATCH($A3,Payments!$E$1:$Z$1,0)),"x") -

 SUMIFS(Payments!$D$2:$D$1000,Payments!$C$2:$C$1000,"="&$A3,

OFFSET(Payments!$D$2:$D$1000,0,MATCH(B$1,Payments!$E$1:$Z$1,0)),"x"))<=0, "",

SUMIFS(Payments!$D$2:$D$1000,Payments!$C$2:$C$1000,"="&B$1,

OFFSET(Payments!$D$2:$D$1000,0,MATCH($A3,Payments!$E$1:$Z$1,0)),"x") -

SUMIFS(Payments!$D$2:$D$1000,Payments!$C$2:$C$1000,"="&$A3,

OFFSET(Payments!$D$2:$D$1000,0,MATCH(B$1,Payments!$E$1:$Z$1,0)),"x"))

Looking closely, it’s possible to see that we have the same formula appear twice; once it is checked to see if it’s less then or equal to zero, and again as the result.

Now, the same formula using SHOWIF:

=SHOWIF(SUMIFS(Payments!$D$2:$D$1000,Payments!$C$2:$C$1000,"="&B$1,

OFFSET(Payments!$D$2:$D$1000,0,MATCH($A3,Payments!$E$1:$Z$1,0)),"x") -

 SUMIFS(Payments!$D$2:$D$1000,Payments!$C$2:$C$1000,"="&$A3,

OFFSET(Payments!$D$2:$D$1000,0,MATCH(B$1,Payments!$E$1:$Z$1,0)),"x"), "<=0")

That’s MUCH shorter, and probably much easier to understand.

Here are the two functions:

' This function will display the value if it matches the condition;
' otherwise, it will display an empty cell, or
' the third (optional) parameter
Function SHOWIF(value As Variant, condition As String, _
 Optional nonvalue As String = "") As Variant
 If (Not IsError(value) And Application.Evaluate(CStr(value) & condition)) Then
 SHOWIF = value
 Else
 SHOWIF = nonvalue
 End If
End Function
' This function will display the value if it does not matches the condition;
' otherwise, it will display an empty cell, or' the third (optional) parameter
Function SHOWUNLESS(value As Variant, condition As String, _
Optional nonvalue As String = "") As Variant
 If (IsError(value) Or Application.Evaluate(CStr(value) & condition)) Then
 SHOWUNLESS = value
 Else
 SHOWUNLESS = nonvalue
 End If
End Function

To use them, just put in the formula =SHOWIF(value, condition) or =SHOWUNLESS(value, condition)

Grab the file SHOWFunctions.xla which is an Excel Add-in file containing both user-defined functions. It’s packaged as an Excel Add-in so you can easily add it to Excel without having to add the functions to each workbook.

To use it, save it as a file, then open Excel, and add it to the Add-in list. In Excel prior to 2007, you can do this using the Add-in command in the Tools menu (select ‘Browse’ from the dialog that opens and select the saved file to use it). In Excel 2007 and 2010, go to the Add-ins page in the Excel Options dialog, select ‘Excel Add-ins’ in the Manage box and press the Go button. Then use the browse button the same as in the previous versions.
Summary

These functions should make your spreadsheet code much easier to follow and therefore easier to debug. We hope you get a lot of use out of them! Perhaps you could improve or extend our ideas? Please let us know…

About the author

Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to XLS conversion software.

For more Excel tips from Yoav, join him on Facebook or Twitter