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,

Trying to get an IF/AND average to work
 

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,

Trying to get an IF/AND average to work
 

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

Trying to get an IF/AND average to work
 

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

Trying to get an IF/AND average to work
 

Anytime.

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=502639

Trying to get an IF/AND average to work
 

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

Trying to get an IF/AND average to work
 

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

Trying to get an IF/AND average to work
 

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