Average with Criteria in Rows and dynamically select several columns

[Holger]

Hi Everyone, would appreciate some help please. I'm able to create ie the average for March for KPI1 across all clients with below formula. I can't seem to work out, how to change my formula (or use a different one) to average across several columns based on my fields. In the screenshot below, how do I get a formula for the yellow cell, that averages for Q4 (Col AL,AM,AN).

Thank You so much for looking and any help.

Cheers

 

[bstory84]

If you have access to the Filter function. . Then this should work for you.
Place this formula in cell AL19 (if that is row 19 in your image).

=AVERAGE(FILTER(FILTER($AK$5:$AW$12,($AK$5:$AK$12=$AK$19),""),$AK$5:$AW$5=$AI$19,""))

 

[Holger]

If you have access to the Filter function. . Then this should work for you.
Place this formula in cell AL19 (if that is row 19 in your image).

=AVERAGE(FILTER(FILTER($AK$5:$AW$12,($AK$5:$AK$12=$AK$19),""),$AK$5:$AW$5=$AI$19,""))

Thank You so much but as I'm on a Mac it does not seem to work at the moment...Anyway, I found a solution with something like this =AVERAGE(IF((KPIRawDataCL!$B$5:$B$126=$L9)*(KPIRawDataCL!$A$3:$X$3=$B$32),KPIRawDataCL!$A$5:$X$126)). Much tanks again.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)