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

RockEd

Board Regular
Joined
Aug 13, 2021
Messages
66
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 13, 2021
Messages
66
Office Version
  1. 365
Platform
  1. Windows
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 :biggrin:!
 
Solution

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,798
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
Joined
Aug 13, 2021
Messages
66
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 2, 2008
Messages
39,798
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
Joined
Aug 13, 2021
Messages
66
Office Version
  1. 365
Platform
  1. Windows
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:

Forum statistics

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