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

[RockEd]

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!

 

[RockEd]

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

=IFERROR(AVERAGEIFS($H$2:$H$44482,$F$2:$F$44482,$AD23,$J$2:$J$44482,$Y$3:$Y$18),0)

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]

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]

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]

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]

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.