Replacing the div/0 with something else

ksagle

New Member
Joined
Jul 10, 2012
Messages
3
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!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

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

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

New Member
Joined
Jul 10, 2012
Messages
3
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

New Member
Joined
Jul 10, 2012
Messages
3

ADVERTISEMENT

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

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Assuming there are no negative values, you could try

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

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,144
Messages
5,599,978
Members
414,354
Latest member
Flaxarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top