averageifs in filtered table

XFG

New Member
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

Attachments

• filteredtable averageifs.PNG
30.4 KB · Views: 9

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

jasonb75

Well-known Member
You need to compare the subtotal to the actual value first. Without that the hidden rows are not excluded.
Excel Formula:
``=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

New Member
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

Well-known Member
Try
Excel Formula:
``=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

XFG

New Member

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

AhoyNC

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

Book1
ABCDE
1Week1
2LB1
3UB4
4
5WeekValue
6133.00
713
815
922
1023
Sheet1
Cell Formulas
RangeFormula
E6E6=AVERAGE(FILTER(Table1[Value],(Table1[Week]=\$C\$1)*(Table1[Value]>=\$C\$2)*(Table1[Value]<=\$C\$3),""))

XFG

jasonb75

Well-known Member
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

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

Replies
4
Views
119
Replies
3
Views
41
Replies
4
Views
122
Replies
13
Views
347
Replies
5
Views
341

1,127,855
Messages
5,627,269
Members
416,236
Latest member
Lynchbox

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.

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