software

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

=IF(AND('Period Data'!A10:A200=quot;Januaryquot;,'Period
Data'!E10:E200=2005),AVERAGE('Period Data'!F10:F200),quot;quot;)

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=quot;Januaryquot;,'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.

quot;Sagequot; wrote:

gt; I'm trying to get this to only average specific information in column F that
gt; contain quot;Januaryquot; and quot;2005quot; in that particular row. If it doesn't match
gt; January and 2005, it should return nothing.
gt;
gt; =IF(AND('Period Data'!A10:A200=quot;Januaryquot;,'Period
gt; Data'!E10:E200=2005),AVERAGE('Period Data'!F10:F200),quot;quot;)
gt;
gt; It's averaging everything right now. I'm sure there's a better way to do
gt; this, but it's beyond me at this point.
gt;
gt; Thanks in advance,


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

=AVERAGE(IF(Period Data!A1:A200=quot;Januaryquot;,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=quot;Januaryquot;,IF(Period
Data!E1:E200=2005,Period Data!F1:F200,FALSE)))}

HTH

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=502639That worked. Just had to add the ' around period data and the good 'ole
CTRL Shift Enter.

Thanks mate!

quot;SteveGquot; wrote:

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


Anytime.

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=502639One 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=quot;Januaryquot;,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=quot;Januaryquot;,IF(Period
Data!E1:E200=2005,IF(Period Data!F1:F200lt;gt;quot;quot;,Period
Data!F1:F200,FALSE))))

Again, it is an array formula.

HTH

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=502639Care for a virtual beer? Cheers!

quot;SteveGquot; wrote:

gt;
gt; This should do it for you.
gt;
gt;
gt; =AVERAGE(IF(Period Data!A1:A200=quot;Januaryquot;,IF(Period
gt; Data!E1:E200=2005,IF(Period Data!F1:F200lt;gt;quot;quot;,Period
gt; Data!F1:F200,FALSE))))
gt;
gt; Again, it is an array formula.
gt;
gt; HTH
gt;
gt; Steve
gt;
gt;
gt; --
gt; SteveG
gt; ------------------------------------------------------------------------
gt; SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
gt; View this thread: www.excelforum.com/showthread...hreadid=502639
gt;
gt;