Trying to get an IF/AND average to work

S

Sage

I'm trying to get this to only average specific information in column F that
contain "January" and "2005" in that particular row. If it doesn't match
January and 2005, it should return nothing.

=IF(AND('Period Data'!A10:A200="January",'Period
Data'!E10:E200=2005),AVERAGE('Period Data'!F10:F200),"")

It's averaging everything right now. I'm sure there's a better way to do
this, but it's beyond me at this point.

Thanks in advance,
 
S

Sage

So I tried a different method here and still getting the same results. Here's
what I tried:

=AVERAGE(IF(AND('Period Data'!A10="January",'Period Data'!E10=2005),'Period
Data'!F10:F20))

It's still averaging everything in the F column rather then just those
particular rows that match January and 2005.
 
S

SteveG

Make sure you are entering your formula in as an array.

=AVERAGE(IF(Period Data!A1:A200="January",IF(Perio
Data!E1:E200=2005,Period Data!F1:F200,FALSE)))

When you complete the formula, don't hit enter to commit, hi
Ctrl-Shift-Enter Simultaneously. This will create curly bracket
around the formula.

{=AVERAGE(IF(Period Data!A1:A200="January",IF(Perio
Data!E1:E200=2005,Period Data!F1:F200,FALSE)))}

HTH

Stev
 
S

Sage

That worked. Just had to add the ' around period data and the good 'ole
CTRL+Shift+Enter.

Thanks mate!
 
S

Sage

One more question if you're still around. How would I only average cells in
column F that contain information and ignore blank cells? As of now it's
bring my average down if there's a cell with nothing in it.

=AVERAGE(IF(Period Data!A1:A200="January",IF(Period Data!E1:E200=2005,Period
Data!F1:F200,FALSE)))

Thanks again!

Sage
 
S

SteveG

This should do it for you.


=AVERAGE(IF(Period Data!A1:A200="January",IF(Period
Data!E1:E200=2005,IF(Period Data!F1:F200<>"",Period
Data!F1:F200,FALSE))))

Again, it is an array formula.

HTH

Steve
 
Top