averageifs in filtered table

XFG

New Member
Joined
Jan 19, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
    filteredtable averageifs.PNG
    30.4 KB · Views: 9

Some videos you may like

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
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 19, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
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.
 
  • Like
Reactions: XFG
Solution

XFG

New Member
Joined
Jan 19, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,750
Office Version
  1. 365
Platform
  1. Windows
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),""))
 
  • Like
Reactions: XFG

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 19, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Thank you both for your help. Both suggestions are very welcome and I will continue to use them both.
 

Watch MrExcel Video

Forum statistics

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