#DIV/O! Errror with Absolute Number Calculation

  • Thread starter Thread starter Brent
  • Start date Start date
B

Brent

Hi,

I am calculating the following formula.

=ABS(K41-J41)/ABS(K41)

The result is #DIV/O! many times because K41 is 0 and the result is #DIV/O!

I'd like the result to be "N/A" or something that I could put in between
parenthesis.

I'd be happy with any recommendations you have.

Thanks!!
 
Hi,

Firstly your formula can be simplified to

=ABS((K41-J41)/K41)

and to get NA# instead of div/0 use

=IF(K41=0,NA(),ABS((K41-J41)/K41))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Hi Mike,

Thanks so much for your quick response. The result of your equation was #N/A
which was what I wanted. However, what if I now wanted the #N/A to be worded
"No YTD Sales" of something to that affect. I attempted the following
formula, but it didn't work using your prior logic. I think the prefix of my
equation is causing me problems.

=IF(iserror(K41=0,NA(),ABS((K41-J41)/K41)), "No YTD Sales"
K41=0,NA(),ABS((K41-J41)/K41)))
 
Hi,

Like this

=IF(K41=0,"No YTD Sales",ABS((K41-J41)/K41))

Note it's now in quotes
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
=IF(iserror(K41=0,NA(),ABS((K41-J41)/K41)), "No YTD Sales"

One idea w/ Excel 2007:

=IFERROR(ABS(1-J41/K41), "No YTD Sales")

= = = = = = =
HTH :>)
Dana DeLouis
 
Thanks Mike your advice was really helpful today.. I hope this note finds you
well.

Thanks again.
Brent
 
I'm glad I could help and yes I am well thank you and thanks for the feedback
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Back
Top