Excel Spread Sheet #Div/0! Issue

[MarkBurgham]

Hi All,

I am in the process of creating a work book and am using the formula below to work through a high number of cells that contain numbers that need to be added together then divided by the number that the formula found to get an average:

=SUMIF(Sheet1!$G$2:$G$1048576,Working!A2,Sheet1!$H$2:$H$1048576)/COUNTIF(Sheet1!$G$2:$G$1048576,Working!A2)

The formula does work however I'm getting the DIV/0! message which throws calculations out in cells that rely on the information from this formula. I've tried several different ways to remove this such as trying ERRORIF but can't get this to work.

Can someone help please?

Regards
Mark

 

[mole999]

=IFERROR(SUMIF(Sheet1!$G$2:$G$1048576,Working!A2,Sheet1!$H$2:$H$1048576)/COUNTIF(Sheet1!$G$2:$G$1048576,Working!A2),"")

 

[mick0005]

Hi All,

I am in the process of creating a work book and am using the formula below to work through a high number of cells that contain numbers that need to be added together then divided by the number that the formula found to get an average:

=SUMIF(Sheet1!$G$2:$G$1048576,Working!A2,Sheet1!$H$2:$H$1048576)/COUNTIF(Sheet1!$G$2:$G$1048576,Working!A2)

The formula does work however I'm getting the DIV/0! message which throws calculations out in cells that rely on the information from this formula. I've tried several different ways to remove this such as trying ERRORIF but can't get this to work.

Can someone help please?

Regards
Mark

Mark, what options have you tried (as you mentioned above you tried a few methods). I would think that Mole999's suggestion below should work to output a blank if an error is found. Maybe you can suggest what the issues were with the methods you tried so far?

 

[MarkBurgham]

Thank you so much, this has worked. Appreciate your help

 

[MarkBurgham]

Hi, Thank you for your reply - the above formula worked a treat!

 

[Aladin Akyurek]

Hi, Thank you for your reply - the above formula worked a treat!

If you are going to invoke IFERROR, try to use AVERAGEIF or AVERAGEIFS...

=IFERROR(AVERAGEIF(Sheet1!$G$2:$G$1048576,Working!A2,Sheet1!$H$2:$H$1048576),"")

=IFERROR(AVERAGEIFS(Sheet1!$H$2:$H$1048576,Sheet1!$G$2:$G$1048576,Working!A2),"")

 

[MarkBurgham]

Thank You Aladin,
I do have another issue. The formula above reports the 5 highest numbers on a separate sheet which works well however instead of printing the numbers which are Database sizes I would like the sheet to print data from another cell, these cells contain a number relating to a branch number so we have a quick reference to the sites that are having issues. I have read about Match and Index but have no idea as to how to set this up.

Please could you help?

Let me know I you require any further information to help.

Regards
Mark

 

[mole999]

I have read about Match and Index but have no idea as to how to set this up.

I used this to learn it, and refresh when I forget How to Use INDEX MATCH

 

[MarkBurgham]

Thanks Mole999,
Great help.
Regards
Mark