averageifs in filtered table

[XFG]

Hi,

I am trying to calculate the average from a table using the averageifs function, because I only want to take into account the numbers between the lower and upper boundary (LB and UB). I know I need to use the subtotal function to not take into account the hidden cells. I could not find another post satisfying my case so I hope someone can help me on this one.

In the photo, which you can find attached, I have tried to visualise the problem.

- The first table is de unfiltered table, the second table is the same table but filtered on week "1".
- When using the normal average function it averages all the values, which is not wat I want.
- When using the subtotal function it averages only the visible values. This is a step in the right direction. However, I want it to exclude the values above 4 and below 1.
- I tried it with the formula: AVERAGE(IF(SUBTOTAL(9,OFFSET(Test6[[#Headers],[Value]],ROW(Test6[Value])-ROW(Test6[[#Headers],[Value]]),0,1))<C3,Test6[Value])), which I found online. This unfortunately does not work.

How should I write the function for excel to calculate the average with the values between the boundaries?

I hope I made myself clear.


Kind regards,
XFG

 

[jasonb75]

You need to compare the subtotal to the actual value first. Without that the hidden rows are not excluded.

=AVERAGE(IF(SUBTOTAL(9,OFFSET(Test6[[#Headers],[Value]],ROW(Test6[Value])-ROW(Test6[[#Headers],[Value]]),0,1))=Test6[Value],IF(Test6[Value]<C3,Test6[Value])))

Note that a zero value in a hidden row can still cause incorrect results, I do not believe that there is any way around that.

 

[XFG]

Hi Jason,

Thank you for your quick reply. This indeed works. However, the lower boundary had not been taken into account with this function. How can the lower boundary be added to the calculation?

 

[jasonb75]

Try

=AVERAGE(IF(SUBTOTAL(9,OFFSET(Test6[[#Headers],[Value]],ROW(Test6[Value])-ROW(Test6[[#Headers],[Value]]),0,1))=Test6[Value],IF(Test6[Value]<C3,IF(Test6[Value]>C2,Test6[Value]))))

As long as the lower bound is always >0, this should also fix the problem that I mentioned about hidden zero values. With 0 or negative lower bounds they would still skew the results.

 

[XFG]

This solved my problem! Thank you very much, also for the quick replies.

 

[AhoyNC]

Since you have Excel 365 another option would be to use the FILTER function with AVERAGE.

Book1
	A	B	C	D	E
1		Week	1
2		LB	1
3		UB	4
4
5		Week	Value
6		1	3		3.00
7		1	3
8		1	5
9		2	2
10		2	3
Sheet1

Cell Formulas
Range	Formula
E6	E6	=AVERAGE(FILTER(Table1[Value],(Table1[Week]=$C$1)*(Table1[Value]>=$C$2)*(Table1[Value]<=$C$3),""))

 

[jasonb75]

I thought about using something similar in an earlier thread but it has a lot of potential for error, especially with more complex filter criteria such as multiple selections from a single column.

 

[XFG]

Thank you both for your help. Both suggestions are very welcome and I will continue to use them both.