Average for condition in different column

[Tony R]

In Excel 97 how can I average in one column for a given
value in another column.

Column R has a list of numerical values I want to find the
average but only for values in R which correspond with a
given month in column W.

For example what is the average in R for all values
corresponding to October in W.

Many Thanks in advance

Tony R

 

[Max]

Assuming col W contains text such as Oct, Nov, Dec etc

try something like:
=AVERAGE(IF($W$2:$W$400="Oct",$R$2:$R$400))

which needs to be array-entered i.e.:
Hold down Ctrl & Shift keys and press Enter
(instead of just pressing Enter)

Done correctly, Excel will wrap curly braces around the formula:
{=AVERAGE(IF($W$2:$W$400="Oct",$R$2:$R$400))}
(don't type-in the curly braces)
--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email

 

[Paul]

In Excel 97 how can I average in one column for a given
value in another column.

Column R has a list of numerical values I want to find the
average but only for values in R which correspond with a
given month in column W.

For example what is the average in R for all values
corresponding to October in W.

Many Thanks in advance

Tony R

If you have dates in column W, use:
=AVERAGE(IF(MONTH(W1:W10)=10,R1:R10))
If you have the month names as text, use:
=AVERAGE(IF(W1:W10="October",R1:R10))
Both of these are array formulas, so they have to be entered using
CTRL+SHIFT+ENTER rather than just ENTER.