#DIV/O! Errror with Absolute Number Calculation
 

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!!

#DIV/O! Errror with Absolute Number Calculation
 

Try this...

=IF(K41="","N/A",ABS(K41-J41)/ABS(K41))

--
Biff
Microsoft Excel MVP


"Brent" wrote in message
...
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!!

#DIV/O! Errror with Absolute Number Calculation
 

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.


"Brent" wrote:

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!!

#DIV/O! Errror with Absolute Number Calculation
 

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

"Mike H" wrote:

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.


"Brent" wrote:

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!!

#DIV/O! Errror with Absolute Number Calculation
 

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.


"Brent" wrote:

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

"Mike H" wrote:

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.


"Brent" wrote:

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!!

#DIV/O! Errror with Absolute Number Calculation
 

=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


On 5/11/2010 3:47 PM, Brent wrote:
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)))

"Mike H" wrote:

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.


"Brent" wrote:

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!!

#DIV/O! Errror with Absolute Number Calculation
 

Thanks Mike your advice was really helpful today.. I hope this note finds you
well.

Thanks again.
Brent

"Mike H" wrote:

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.


"Brent" wrote:

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

"Mike H" wrote:

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.


"Brent" wrote:

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!!

#DIV/O! Errror with Absolute Number Calculation
 

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.


"Brent" wrote:

Thanks Mike your advice was really helpful today.. I hope this note finds you
well.

Thanks again.
Brent

"Mike H" wrote:

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.


"Brent" wrote:

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

"Mike H" wrote:

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.


"Brent" wrote:

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!!