Excel needs an AVERAGEIF function similar to SUMIF. Please!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Excel 2003 SP2

I'm trying to take a numerical average of a selection of cells, defined by
the value of another column of the row the data is in.

Here's an example:

group score
a 6
b 4
b
a 6
c
b 8
c 6

total 6
count 2
avg 3

I want to know what the average for all scores in group "c". I can add them
all up using SUMIF, and I can count the number of "c"s using COUNTIF, but if
I have a blank cell (as in the example above) the average is incorrect. How
can I find the average for all "c"s where there is data in the adjacent cell?
(thus making the average in the above example 6!)

Many thanks.


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...f-14c0413a5c66&dg=microsoft.public.excel.misc
 
try this ARRAY formula which must be entered/edited with ctrl+shift+enter vs
just enter

=AVERAGE(IF((C1:C21="c")*(D1:D21>0),D1:D21))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Is there an AVERAGEIF function in excel? 5
AVERAGEIFS Multiple Criteria: use an Array? 7
Average problem 1
AverageIFs 4
Max/min functions similar to SUMIF 3
getting specific data from one page to another 3
EXCEL FUNCTIONS 1
the weeknum function in excel seems to suggest flexible day 1 of w 1

Back
Top