"Basenji" wrote:
Using Excel 2003, I need to find the average
age of males who have had a specified procedure
before March 31, 2010. D3:D11 are ages.
E3:E11 are gender. F3:F11 are the dates.
G3:G11 are the procedures.

SUMPRODUCT is great when you want a single value, such as the total that you
are getting.

But for AVERAGE, you want the argument to be an array or list of values.
Try the follow array formula[*]:

=AVERAGE(IF(('East 2010'!$G$3:$G$11="THR")
*('East 2010'!$E$3:$E$11="Male")
*('East 2010'!$F$3:$F$11<=DATE(2010,3,31)),
'East 2010'!$D$3:$D$11))
[*] Enter an array formula by pressing ctrl+alt+Enter instead of just Enter.
In the Formula Bar, you should curly braces around the entire formula, viz.
{=formula}. Note that you cannot enter the curly braces yourself; that is
just Excel's way of denoting an array formula when it is displayed. If you
make a mistake, select the cell, press F2, edit as needed, then press
ctrl+alt+Enter.


----- original message -----

"Basenji" wrote:
Using Excel 2003, I need to find the average age of males who have had a
specified procedure before March 31, 2010. D3:D11 are ages. E3:E11 are
gender. F3:F11 are the dates. G3:G11 are the procedures. So far I have this
formula but am getting a total of the ages rather than the average.
=AVERAGE((SUMPRODUCT(('East 2010'!$D$3:$D$11)*('East
2010'!$G$3:$G$11="THR")*('East 2010'!$E$3:$E$11="Male")*('East
2010'!$F$3:$F$11<=DATE(2010,3,31)))))

Any suggestions would be appreciated. Thank you.