Averaging the result of a filtered list based on criteria returns #Div/0 when the list is filtered, when it's unfiltered the average is wrong.

[RockandGrohl]

Hi fellas

I'm using this beast to calculate the Average of a filtered set of results based on three criteria:

=SUMPRODUCT((DataFeed!$AI$2:$AI$287189="FamilyFun")*(DataFeed!$AH$2:$AH$287189="")*(DataFeed!$AY$2:$AY$287189>0)*SUBTOTAL(101,OFFSET(DataFeed!$BC$2:$BC$287189,ROW(DataFeed!$BC$2:$BC$287189)-MIN(ROW(DataFeed!$BC$2:$BC$287189)),0,1)))

AI = FamilyFun
AH = ""
AJ >0

With BC being the average range.

Now this formula works really well when I do a count or sum from the Subtotal function, but when I change it to 101 which is average and the range is unfiltered, it gives me 1.5 million. If I highlight the unfiltered range (top to bottom of column BC) it gives me 304 as an average.

When I apply the filter to the range, the result of the formula gives me Div/0 even though 0's can be included when averaging.


I can't work this at as this three criteria function works so well for counting or summing a filtered range. Cheers!

 

[RockandGrohl]

I've checked all 300,000 lines in column BC and they are all numbers, verified with "ISNUMBER"

 

[RockandGrohl]

Also, if I apply the filter manually, extract the values of Column BC and average it, it correctly gives me £344.96 - so there's definitely no issue with the data.

If I change the SUBTOTAL to 103 (CountA) it successfully counts 4,400 results. If I change to 109 (SUM) it succesfully sums 1.5 million (I guess that's the first number)

Cheers.

 

[RockandGrohl]

This is kind of, maybe solved.

I feel like AVERAGE on the SUBTOTAL function is bugged, or it's clearly not working as intended.

With SUM or COUNT, you can pass an array into it as here

The functions are intended to only perform a simple count or sum or average, but by implementing rows and offset you can effectively give it an array to compare against for criteria.

I assumed this would work with Average. After all, if you can turn SUM and COUNT into SUMIFS and COUNTIFS, why not AVERAGE into AVERAGEIFS.

However it appears that when the range is unfiltered, the Average works like a COUNT in this capacity, and then when you apply a filter it errors out with a DIV/0 error.



I solved this by just dividing the sum of the bookings with the number of filtered results to get an average booking and then the same logic again for an average spend, which is what I wanted the original formula to do.


If anyone has a way of doing this within the original remit I would be interested to learn why my original way isn't working, but as for now luckily I am already pulling out the total bookings number for another part of the userform so I don't need the complex formula.