formula to show "na" not working in some cells

[Meenie]

I have excel 2003
I have a formula in a spreadsheet that works in some cells and not in others.
I've tried clearing the area where it doesn't work and re-entering the formula - still doesn't work.
I've tried clearing the area where it doesn't work and copying the formula and pasting it in (paste special/formula) but it still doesn't work.
I've tried using the paint formula on the naughty cells, but it doesn't work.

The formula I have is (assume the equal sign) "IF(ISError(D47/D42),"N/A",D47/D42)
The formula goes across a row to 3 different cells and the range changes according to the column (ie: B47/B42 and F47/F42) and is in different rows also so the number of the row changes as you go down the spreadsheet, of course)
It always works in the D column but in some of the other columns it sometimes returns a zero instead of N/A.

Any ideas?
Thanks!

 

[Andrew Poulsom]

That means that your division doesn't result in an error. What's in the cell in row 42 when you don't get N/A as you would expect.

 

[Jonmo1]

Take this as example...

=x/y

The Div/0 Error occurs when the y is a 0
It will not error if the x is a 0, and the y is any other number.
0 devided by ANY Number (other than 0), is 0.

 

[T. Valko]

I have excel 2003
I have a formula in a spreadsheet that works in some cells and not in others.
I've tried clearing the area where it doesn't work and re-entering the formula - still doesn't work.
I've tried clearing the area where it doesn't work and copying the formula and pasting it in (paste special/formula) but it still doesn't work.
I've tried using the paint formula on the naughty cells, but it doesn't work.

The formula I have is (assume the equal sign) "IF(ISError(D47/D42),"N/A",D47/D42)
The formula goes across a row to 3 different cells and the range changes according to the column (ie: B47/B42 and F47/F42) and is in different rows also so the number of the row changes as you go down the spreadsheet, of course)
It always works in the D column but in some of the other columns it sometimes returns a zero instead of N/A.

Any ideas?
Thanks!

D47/D42 will only return an error if:

• One or both cells already contain an error
• One or both cells contain a TEXT entry
• Cell D42 is empty or contains 0

Assuming the cells will only contain numbers...

=IF(D42=0,"N/A",D47/D42)

If D47 is either an empty cell or contains 0 and D42 contains some number other than 0 then the correct result will be 0.

Do you not want a result of 0?

 

[Meenie]

Hmmm ok, I see the error and the error is mine!
I was thinking it would show the NA if either number were a zero, but you're right it should only show the n/a if D42 is the 0. (I knew that when I created the formulas a couple of years ago, rofl)
duh, thanks for clearing the cobwebs out of my head this morning; I guess the long weekend short-circuited my brain, lol.
Thanks for your quick answers, I really appreciate it
Meenie

 

[T. Valko]

Hmmm ok, I see the error and the error is mine!
I was thinking it would show the NA if either number were a zero, but you're right it should only show the n/a if D42 is the 0. (I knew that when I created the formulas a couple of years ago, rofl)
duh, thanks for clearing the cobwebs out of my head this morning; I guess the long weekend short-circuited my brain, lol.
Thanks for your quick answers, I really appreciate it
Meenie

You're welcome. Thanks for the feedback!