?
Solved

Average a range of numbers AVERAGEIF

Posted on 2011-09-08
3
Medium Priority
?
314 Views
Last Modified: 2012-05-12
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!
0
Comment
Question by:Euro5
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 36506986
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 36507009
....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
 

Author Closing Comment

by:Euro5
ID: 36507202
Perfect - options & explainations. Can't do better than that!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question