Trying to get an IF/AND average to work

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,

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.

"Sage" wrote:

> 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,

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

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

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

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

HTH

Steve

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

Thanks mate!

"SteveG" wrote:

>
> Make sure you are entering your formula in as an array.
>
> =AVERAGE(IF(Period Data!A1:A200="January",IF(Period
> Data!E1:E200=2005,Period Data!F1:F200,FALSE)))
>
> When you complete the formula, don't hit enter to commit, hit
> Ctrl-Shift-Enter Simultaneously. This will create curly brackets
> around the formula.
>
> {=AVERAGE(IF(Period Data!A1:A200="January",IF(Period
> Data!E1:E200=2005,Period Data!F1:F200,FALSE)))}
>
> HTH
>
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=502639
>
>

Anytime.

Steve

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

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

Care for a virtual beer? Cheers!

"SteveG" wrote:

>
> 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
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=502639
>
>