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))
 
Back
Top