returning averages in cells based on autofilter

[sdtsdt]

hi guys

im trying to return answers in cells based on auto filter
please see workbook attached

in cells ap14, aq14. ar14 etc i have the averages of all games played
ie in cell ap14 the average of all arsenals goals at home this season is 2.36

what i want to do in cell ap15 (yellow)
is return the answer of the average of that column if i select a player
ie if i auto filter santi cazorla to 'y' then it should return the answer 2.33 in cell ap15.
is this possible please

[oeldere]

Ap15=subtotal(101,ap2:ap12)

[sdtsdt]

thanks oeldere worked a treat

[oeldere]

Thanks for the reply.

Glad I could help.

[sdtsdt]

oeldere would you be kind enough to explain how it works?
what is the 101 for?

[oeldere]

look at the explaintion of the formula subtotal.

101 is the argument for average.

109 is the argument for sum.