Excel formula help with #DIV/0!

[akunz]

I have a financial budget spreadsheet with a monthly budget column, a
monthly actual column, and a percentage difference column. On certain
rows there will be expenses listed with no budget amount for the month
and no actual amount for the month. In these instances, the result is
displaying " #DIV/0!. In this case, I would like the result to display
a blank cell. As I am new to writing formula's, could someone please
help.

Example:

Account: Budget Actual % Difference


Courier Expense $ 0 $ 0 #DIV/0!

 

[Vasant Nanavati]

Something like:

=IF(B2=0,"",(C2-B2)/B2)

 

[Andy B]

Hi

Use an IF function to check whether the divisor is zero:

=IF(Actual<>0,Actual/Budget,0)

 

[Peo Sjoblom]

=IF(Divisor_cell=0,"",your_formula)

 

[Toby Erkson]

I prefer to check for an error in the formula, like so:

= IF(ISERROR(yourformula), 0, yourformula)

Granted, if "yourformula" is long then this can make the cell formula a tad long, however, this way you don't have to check a specific cell/range for zero or
whatever else would cause a mathematical error (like previous examples are limited to).

Of course, if you know you will only get one type of error then you could use an error checker specific to the type of error you may get. Look in the Help
under "IS functions".
Toby Erkson
Oregon, USA

 

[Harlan Grove]

I prefer to check for an error in the formula, like so:

= IF(ISERROR(yourformula), 0, yourformula) ...
Of course, if you know you will only get one type of error then you could use
an error checker specific to the type of error you may get. Look in the Help
under "IS functions".

...

It's precisely because other types of error could happen that using an ISERROR
trap is usually a **BAD** idea. If the original formula contained range
references, but the rows containing one of those ranges were deleted, that range
reference would become #REF!. It's usually a VERY GOOD THING to catch #REF!
errors as soon as possible. ISERROR traps would hide them. Likewise for #NAME?
and #NULL! errors. #NUM! errors are problematic, sometimes indicating formula
logic problems, other times indicating arithmetic operations that should be
trapped, e.g., SQRT(-1). Often there are good reasons to trap #VALUE! and #N/A
errors, but #DIV/0! errors are in a special class - they're very common in
certain classes of arithmetic formulas, and they generally don't indicate
anything wrong in either formula logic or worksheet/workbook integrity.

If #DIV/0! errors would be caused by dividing by zero, then there's no good
reason to use anything other than

=IF(Denominator<>0,otherterms..Numerator/Denominator..otherterms,"")

If the #DIV/0! errors would be caused by calculating certain descriptive
statistics for operands containing no numeric values, then there's no good
reason to use anything other than

=IF(COUNT(Operands),AVERAGE(Operands),"")
=IF(COUNT(Operands)>1,VAR(Operands),"")
=IF(COUNT(Operands)>1,STDEV(Operands),"")
=IF(COUNT(Operands)>2,SKEW(Operands),"")
=IF(COUNT(Operands)>3,KURT(Operands),"")

Finer grained control is possible. To trap only #DIV/0!, #VALUE! and #N/A error
values and pass all others, use

=IF(ISERROR(1/OR(Error.Type(Expression)={2,3,7})),Expression,"")