Average IF
 

I am using MS Excel 2003.

I am trying to get the average of numbers in a column if the fit several
criteria. SUMPRODUCT is not appropriate, right?

I tried this AS AN ARRAY but it doesn't seem to work.

E2744 is a date

=IF(AND($F$2:$F$2741E2744,$H$2:$H$27410,$L$2:$L$ 27410),AVERAGE($H$2:$H$2741),"")

Ideas.

Average IF
 

Hi,

Try this array formula, I shortened the ranges for debugging

=AVERAGE(IF(F2:F27E27,IF(H2:H270,IF(L2:L270,H2: H27))))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

"PAL" wrote:

I am using MS Excel 2003.

I am trying to get the average of numbers in a column if the fit several
criteria. SUMPRODUCT is not appropriate, right?

I tried this AS AN ARRAY but it doesn't seem to work.

E2744 is a date

=IF(AND($F$2:$F$2741E2744,$H$2:$H$27410,$L$2:$L$ 27410),AVERAGE($H$2:$H$2741),"")

Ideas.

Average IF
 

Try this array formula** :

=AVERAGE(IF(($F$2:$F$2741E2744)*($H$2:$H$27410)* ($L$2:$L$27410),$H$2:$H$2741))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I am using MS Excel 2003.

I am trying to get the average of numbers in a column if the fit several
criteria. SUMPRODUCT is not appropriate, right?

I tried this AS AN ARRAY but it doesn't seem to work.

E2744 is a date

=IF(AND($F$2:$F$2741E2744,$H$2:$H$27410,$L$2:$L$ 27410),AVERAGE($H$2:$H$2741),"")

Ideas.

Average IF
 

You can use SUMPRODUCT if you don't want to use arrays; just have to remember
how an average is actually calculated and break it down.

=SUMPRODUCT(($F$2:$F$2741E2744)*($H$2:$H$27410)* ($L$2:$L$27410)*($H$2:$H$2741))/SUMPRODUCT(($F$2:$F$2741E2744)*($H$2:$H$27410)*( $L$2:$L$27410))

This calculates the sum of everything that meets your crtieria, then divides
by the count.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"PAL" wrote:

I am using MS Excel 2003.

I am trying to get the average of numbers in a column if the fit several
criteria. SUMPRODUCT is not appropriate, right?

I tried this AS AN ARRAY but it doesn't seem to work.

E2744 is a date

=IF(AND($F$2:$F$2741E2744,$H$2:$H$27410,$L$2:$L$ 27410),AVERAGE($H$2:$H$2741),"")

Ideas.

Average IF
 

Hi,

Based on the fact that E2744 is not absolute then you are probably not
copying this formula so you can remove the absolute references:

=AVERAGE(IF((F2:F2741B1)*(H2:H2741*L2:L27410),H2 :H2741))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"PAL" wrote:

I am using MS Excel 2003.

I am trying to get the average of numbers in a column if the fit several
criteria. SUMPRODUCT is not appropriate, right?

I tried this AS AN ARRAY but it doesn't seem to work.

E2744 is a date

=IF(AND($F$2:$F$2741E2744,$H$2:$H$27410,$L$2:$L$ 27410),AVERAGE($H$2:$H$2741),"")

Ideas.