# Average of averageifs, not working where answers have #DIV/0 error

#### RockEd

##### Board Regular
Hello I have a formula, that has piggy-backed off my last question about countifs; except it's a little more complicated.

The formula looks like this:

=AVERAGEIFS(\$H\$2:\$H\$44482,\$F\$2:\$F\$44482,\$AC23,\$J\$2:\$J\$44482,\$Y\$3:\$Y\$18)

Where:
H column is the average range.
F column is the criteria range 1
AC23 is the criteria1 for it to match
J column is the criteria range 2
With an array of column Y to be matched against.

The spilled result includes 2 DIV/0 errors (I presume there is no data for it to give an average). I don't want a spilled data result though - i'll like the average of all the results.

2 questions:

1) How do I get it to ignore DIV/0 errors?
2) Assuming you/I/we get this to work, will the result give me an arithmetic mean i.e. there will be no weighting applied to the contents of the array? Just a simple average between the results? How would you recommend a geometric mean instead?

thanks!

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### RockEd

##### Board Regular
Answering my own question here, but for number 1, I believe I just need an iferror function put in like this:

As to the type of mean, I might be getting confused between geometric and arithmetic, but I still think if i take an average of those numbers, it doesn't take into account their weighting. For the time being I will build out a table with each of the counts so that I can 'manually' work out the average.... of course if anyone stumbles across this and can think of a better solution then I'm all ears !

#### RoryA

##### MrExcel MVP, Moderator
If you want the average (unweighted) of the results, wrap the whole thing in another AVERAGE function. If you don't want your errors treated as 0 (which will affect the overall result) change the IFERROR to return "" instead.

#### RockEd

##### Board Regular
If you want the average (unweighted) of the results, wrap the whole thing in another AVERAGE function. If you don't want your errors treated as 0 (which will affect the overall result) change the IFERROR to return "" instead.
Ok good point about the "" bit instead of 0. As you say though, the results are still unweighted which won't be useful for me in this situation. But thank you for your response anyway.

#### RoryA

##### MrExcel MVP, Moderator
If you want them weighted, you'll either want to do a sum/count type calculation or add in a SUMPRODUCT. Depends on what you want to use for the weights.

#### RockEd

##### Board Regular
If you want them weighted, you'll either want to do a sum/count type calculation or add in a SUMPRODUCT. Depends on what you want to use for the weights.
Yeah fair point, it's a little complicated for my tired head, so I just put another column in where it states if the data = any of the \$Y\$3:\$Y\$18 then it's "Income", and if not it's "growth" - and then swapped in this new reference into the formula.

That way I don't need to use the extra =average at the front and the average should be weighted appropriately throughout i.e. it's not going to give an "equal split" weighting to something that only appears a few times - whereas it should be a proportional weighting, which my new method has sorted.

Last edited:

Replies
3
Views
426
Replies
4
Views
898
Replies
4
Views
188
Replies
3
Views
620
Replies
7
Views
742

1,181,673
Messages
5,931,350
Members
436,787
Latest member
ogharipour

### 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.

### Which adblocker are you using?

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

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