Average a range of numbers AVERAGEIF

I am trying to average a range of cells using an AVERAGEIF formula.
I only want to average if cell in A=4, then I want to average all numbers between columns S & AD.
AVERAGEIF(A:A,4,S:AD)
What I am now realizing is that it is ONLY averaging column S, not columns S thru AD.
Is there a formula that will average all the numbers in all the columns?
Thanks!
Euro5Asked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
You have to use an array formula, e.g.

=AVERAGE(IF(A:A=4,IF(ISNUMBER(S:AD),S:AD)))

confirmed with CTRL+SHIFT+ENTER

To do this place formula in cell then press F2 to select formula - hold down CTRL and SHIFT keys and press ENTER. If done correctly then curly braces like { and } will appear around the formula in the formula bar.

Note that the above will be quite slow because it looks at whole columns - best to limit to a fixed range, e.g. rows 1 to 100 like this

=AVERAGE(IF(A1:A100=4,IF(ISNUMBER(S1:AD100),S1:AD100)))

regards, barry
0
 
barry houdiniCommented:
....or you can use this longer "non-array" version

=SUMPRODUCT((A1:A100=4)*(S1:AD100))/SUMPRODUCT((A1:A100=4)*ISNUMBER(S1:AD100))

won't work if there is any text in S1:AD100.....

regards, barry
0
 
Euro5Author Commented:
Perfect - options & explainations. Can't do better than that!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.