Average IF

  • Thread starter Thread starter PAL
  • Start date Start date
P

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.
 
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
 
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.
 
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))
 
Back
Top