Averageif not calculating correctly for data in multiple columns

[Kimberly Matson]

Hello. I am using Excel 2010 and trying to average the numbers in columns D through J that meet the criteria of “No” in column A. For example in the image, because A7 and A10 are "No", it should calculate the average of the numbers in D7, E7, F7,G7, H7, I7, J7, D10, E10, F10, G10, H10, I10, J10. (There will eventually be more data, so I need for the calculation to consider all the rows…not stop at row 10.)

Have tried AVERAGEIF(A:A,”No”,D:J) but this is not calculating correctly. I’ve searched the web and the Chandoo forums and tutorials but I still cannot figure out how to make this calculate for average_range that includes more than one column. Any help is greatly appreciated. Thanks so much, Kimberly

 

[NARAYANK991]

Hi ,

Try this :

=AVERAGE(AVERAGEIF(A7:A25,"No",OFFSET(A7:A25,,{3,4,5,6,7,8,9})))

I have arbitrarily put 7 as the starting row and 25 as the last row of data ; change it to suit your data.

Narayan

 

[Kimberly Matson]

Hi ,

Try this :

=AVERAGE(AVERAGEIF(A7:A25,"No",OFFSET(A7:A25,,{3,4,5,6,7,8,9})))

I have arbitrarily put 7 as the starting row and 25 as the last row of data ; change it to suit your data.

Narayan

Thank you so much! Works perfectly. And even better, now that I know that OFFSET formula is needed...I was able to learn more about OFFSET (via Chandoo tutorial) so I can utilize it with other data! I am grateful for your response and for the great tutorials and resources on the site! Best, K-