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?

4 Answers

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

3

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.

2

Try this:

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

or

=IF(D11=0,"",(D11-C11)/D11)

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy