Averaging results from a filtered column?!

[russell henderson]

So this should be the easiest thing in the world but it wasn't covered in the Excel training I did yet did come up in an interview as an exercise..

I have a spreadsheet containing a number of columns as a table. I need to filter one column (to select nationalities) and average the sum (of duration of stay).

So I did an autosum at the bottom of the duration of stay column then expected that when I filtered this by nationality - It would recalculate the average on the basis of what was displayed.

I ended up doing the averages on a calculator and feeling like an idiot.. so what am I missing?

 

[Sektor]

You need SUBTOTAL function. The first argument is the kind of operation you need (AVERAGE, in your example).

 

[russell henderson]

You need SUBTOTAL function. The first argument is the kind of operation you need (AVERAGE, in your example).

I don't understand what this means?

 

[MrJoosten]

Hi Russel,

I think you can add this formula at the bottom row of your table: =SUBTOTAL(1;AH108:AH120) 'change AH108:AH120 to your range

Hope it helps!

 

[jacobsens]

Following on this question, if I have zero's or blanks and I don't want those calculated in the average (Subtotal(101, range1, range2) what additional component can I add to not look at blank or zero cells? Appreciate the help!

 

[russell henderson]

Hi Russel,

I think you can add this formula at the bottom row of your table: =SUBTOTAL(1;AH108:AH120) 'change AH108:AH120 to your range

Hope it helps!

Thankyou Mr Joosten!!