Average IF

[PAL]

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$2741>E2744,$H$2:$H$2741>0,$L$2:$L$2741>0),AVERAGE($H$2:$H$2741),"")

Ideas.

 

[Mike H]

Hi,

Try this array formula, I shortened the ranges for debugging

=AVERAGE(IF(F2:F27>E27,IF(H2:H27>0,IF(L2:L27>0,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

 

[Luke M]

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$2741>E2744)*($H$2:$H$2741>0)*($L$2:$L$2741>0)*($H$2:$H$2741))/SUMPRODUCT(($F$2:$F$2741>E2744)*($H$2:$H$2741>0)*($L$2:$L$2741>0))

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

 

[Shane Devenshire]

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:F2741>B1)*(H2:H2741*L2:L2741>0),H2:H2741))