software

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:

groupscore
a6
b4
b
a6
c
b8
c6

total6
count2
avg3

I want to know what the average for all scores in group quot;cquot;. I can add them
all up using SUMIF, and I can count the number of quot;cquot;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 quot;cquot;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 quot;I
Agreequot; 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 quot;I Agreequot; in the message pane.

www.microsoft.com/office/comm...lic.excel.misc

try this ARRAY formula which must be entered/edited with ctrl shift enter vs
just enter

=AVERAGE(IF((C1:C21=quot;cquot;)*(D121gt;0),D121))

--
Don Guillett
SalesAid Software

quot;Roselandquot; gt; wrote in message
...
gt; Excel 2003 SP2
gt;
gt; I'm trying to take a numerical average of a selection of cells, defined by
gt; the value of another column of the row the data is in.
gt;
gt; Here's an example:
gt;
gt; group score
gt; a 6
gt; b 4
gt; b
gt; a 6
gt; c
gt; b 8
gt; c 6
gt;
gt; total 6
gt; count 2
gt; avg 3
gt;
gt; I want to know what the average for all scores in group quot;cquot;. I can add
gt; them
gt; all up using SUMIF, and I can count the number of quot;cquot;s using COUNTIF, but
gt; if
gt; I have a blank cell (as in the example above) the average is incorrect.
gt; How
gt; can I find the average for all quot;cquot;s where there is data in the adjacent
gt; cell?
gt; (thus making the average in the above example 6!)
gt;
gt; Many thanks.
gt;
gt;
gt; ----------------
gt; This post is a suggestion for Microsoft, and Microsoft responds to the
gt; suggestions with the most votes. To vote for this suggestion, click the quot;I
gt; Agreequot; button in the message pane. If you do not see the button, follow
gt; this
gt; link to open the suggestion in the Microsoft Web-based Newsreader and then
gt; click quot;I Agreequot; in the message pane.
gt;
gt; www.microsoft.com/office/comm...lic.excel.misc