Using Excel 2003, I need to find the average age of males who have had a
specified procedure before March 31, 2010. D311 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.

"Basenji" wrote:
> Using Excel 2003, I need to find the average
> age of males who have had a specified procedure
> before March 31, 2010. D311 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. D311 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.

Hi,

Try this

=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)))/SUMPRODUCT(('East
2010'!$G$3:$G$11="THR")*('East 2010'!$E$3:$E$11="Male")*('East
2010'!$F$3:$F$11<=DATE(2010,3,31)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Basenji" <(E-Mail Removed)> wrote in message
news:3B279D59-21BF-4D57-A26D-(E-Mail Removed)...
> Using Excel 2003, I need to find the average age of males who have had a
> specified procedure before March 31, 2010. D311 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.

Thank you for the explanation between using sumproduct and average with
array. Is the purpose of the asterisks to indicate multiple if criteria
within the array? Also, is it possible to include ISERROR in the formula so
that if there are no males that a blank cell or zero is returned instead of a
divide by zero error message. I have tried several places but have been
unsuccessful.

Thank you.

"Joe User" wrote:

> "Basenji" wrote:
> > Using Excel 2003, I need to find the average
> > age of males who have had a specified procedure
> > before March 31, 2010. D311 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. D311 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.

"Basenji" wrote:
> Is the purpose of the asterisks to indicate multiple
> if criteria within the array?

More specifically, the "*" (multiplication) functions as AND in this
context, just as it does in SUMPRODUCT. We cannot use AND for this purpose
in an array formula.


> is it possible to include ISERROR in the formula so
> that if there are no males that a blank cell or zero is
> returned instead of a divide by zero error message.

It is "possible", but it is messy. You would have to repeat the entire
formula.

If you were using Excel 2007, you could use IFERROR for a compact solution.
But since you are using Excel 2003, you might use the following array formua[*]:

=IF(COUNTIF('East 2010'!$E$3:$E$11,"Male")=0, 0,
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)))

However, I think you really should test whether there are zero cells that
meet __all__ of the required conditions, not just zero males. So use the
following array formula[*]:

=IF(SUMPRODUCT(('East 2010'!$G$3:$G$11="THR")
*('East 2010'!$E$3:$E$11="Male")
*('East 2010'!$F$3:$F$11<=DATE(2010,3,31)))=0, 0,
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)))
[*] Recall that you 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:
> Thank you for the explanation between using sumproduct and average with
> array. Is the purpose of the asterisks to indicate multiple if criteria
> within the array? Also, is it possible to include ISERROR in the formula so
> that if there are no males that a blank cell or zero is returned instead of a
> divide by zero error message. I have tried several places but have been
> unsuccessful.
>
> Thank you.
>
> "Joe User" wrote:
>
> > "Basenji" wrote:
> > > Using Excel 2003, I need to find the average
> > > age of males who have had a specified procedure
> > > before March 31, 2010. D311 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. D311 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.