Replacing the div/0 with something else

[ksagle]

Hi everyone,

I'm struggling with a big spreadsheet I'm working on, and I can't seem to find a way to get rid of the #div/o error. I find it looks messy and confusing, and I just want to replace it with something like "N/A". While I've found some other threads that have asked this question, my formula is a little more confusing so I'm not quite sure how to alter it to make it work.

Here is the formula I'm using: =SUM(E5:E124)/COUNTIF(E5:E124,">0")

Can anyone tell me what I need to change this to to have it show "N/A" instead of the error message?

Thanks!

 

[VoG]

Try

=IF(COUNTIF(E5:E124,">0")>0,SUM(E5:E124)/COUNTIF(E5:E124,">0"),NA())

 

[T. Valko]

Hi everyone,

I'm struggling with a big spreadsheet I'm working on, and I can't seem to find a way to get rid of the #div/o error. I find it looks messy and confusing, and I just want to replace it with something like "N/A". While I've found some other threads that have asked this question, my formula is a little more confusing so I'm not quite sure how to alter it to make it work.

Here is the formula I'm using: =SUM(E5:E124)/COUNTIF(E5:E124,">0")

Can anyone tell me what I need to change this to to have it show "N/A" instead of the error message?

Thanks!

What version of Excel are you using?

If it's Excel 2007 or later use this:

=IFERROR(SUM(E5:E124)/COUNTIF(E5:E124,">0"),"N/A")

This one will work in any version:

=IF(COUNTIF(E5:E124,">0"),SUM(E5:E124)/COUNTIF(E5:E124,">0"),"N/A")

 

[ksagle]

Thank you for the quick response. That does help - it now shows up as #N/A. Is there anyway to get rid of the "#" or will that have to appear no matter what? Is there a way to just replace the error with a simple "0"?

 

[ksagle]

That's perfect. Thank you so much!!!

 

[Jonmo1]

Assuming there are no negative values, you could try

=SUM(E5:E124)/MAX(1,COUNTIF(E5:E124,">0"))

 

[T. Valko]

That's perfect. Thank you so much!!!

I think you're replying to me...

You're welcome!

If you want a 0 returned instead...

If you're using Excel 2007 or later:

=IFERROR(SUM(E5:E124)/COUNTIF(E5:E124,">0"),0)

Or, you can use jonmo1's suggestion with the caveat in any version.