27

In Column C I have Production. In column D I have Goal. In Column E I have variance %. My formula is =(D11-C11)/D11

However, how do you hide the cells down the sheet until you put something in D11 & C11 to hide #DIV/0!. I have tried using the IF formula but seem to get it wrong?

45

IFERROR function

There is a "special" IF test designed just to handle errors:

=IFERROR( (D11-C11)/D11, "")

This gives you the calculated value of (D11-C11)/D11 unless the result is an error, in which case it returns a blank.

Explanation

The "if error" value, the last parameter, can be anything; it isn't limited to the empty double-quotes. IFERROR works for any condition that returns an error value (things that start with a #), like:

#NULL!  -   reference to an intersection of two ranges that don't intersect
#DIV/0! -   attempt to divide by zero
#VALUE! -   variable is the wrong type 
#REF!   -   invalid cell reference
#NAME?  -   formula name, or text within a formula, isn't recognized
#NUM!   -   invalid number
#N/A    -   value is not available

This is handy for debugging; the function can be temporarily wrapped around a formula to return some message text when the formula produces an error. It is also a streamlined form of IF test; it doesn't require including an expression to test it, and then including it again to use its result.

Other Spreadsheets

This function is also available to users of other spreadsheet programs. It was added to LibreOffice Calc in version 4.0 (not yet the distributed version in some Linux distros). As @Kroltan points out, though, it is even more streamlined in Google Sheets, where the "if error" value is optional; it defaults to a blank if missing. So in a case like this, where you just want to hide potential error values, Google Sheets can do it with IFERROR(expression).

  • I know this is for Excel, but in Google Docs and Open/LibreOffice, there is a variation without the necessity for the "value if true", simplifying it to =IFERROR(yourformula). – Kroltan Oct 1 '15 at 13:51
  • 1
    @Kroltan: Just when I thought they couldn't streamline this function any more. It looks like you're right about Google Sheets. Its IFERROR defaults to blank on error if no alternate value is specified, so it's optional. LibreOffice Calc implemented the function with version 4.0. The latest version I have access to is 4.2 and in that, the if-error value is still required. – fixer1234 Oct 1 '15 at 15:11
  • Indeed i had only tested on Google Spreadsheets, but according to the docs it is identical to the one from LibreOffice. Sorry for misinforming. – Kroltan Oct 1 '15 at 23:54
11

Within an IF statement is a logical check (the first part).

IF(logical_test, value_if_true, [value_if_false])

To avoid errors caused while your production and/or the goal data is blank, use the OR with the ISBLANK function within the logical check.

=IF(OR(ISBLANK(C11),ISBLANK(D11)), "", (D11-C11)/D11)

This checks if the either cell referenced is blank. If one or both are blank (making the logical test TRUE), it will process the IF TRUE portion of the IF statement. In this case, the "" tells the formula to do nothing. Otherwise, it will process the IF FALSE part of the formula which is the formula you have.

  • I updated this to include checking both C11 & D11 as you originally posted. Sorry I missed that in my original answer. – CharlieRB Oct 1 '15 at 11:32
  • Good catch. If the goals get pre-populated and then wait for the production entries, the results would show 100% variance until they're entered. Your solution takes care of that, which is much less pressure if performance is being measured off this sheet. :-) – fixer1234 Oct 1 '15 at 13:05
1

Try this:

=IF(D11<>0,(D11-C11)/D11,"")
or
=IF(D11=0,"",(D11-C11)/D11)

1

Select the whole spreadsheet, then under menu Home - Conditional Formatting - New Rule... - Select Format only cells that contain - Under Format only cells with select Errors - Click Format... button - Go to the Font tab - Under Color select the same font color as the background (such as white).

New Rule Dialog

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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