Hi everybody,

I have an =AVERAGE(IF… statement in one worksheet that refers to raw
data in another worksheet. Here is what the raw data looks like:

Column I Column U Column Y
02-01-001 1.0 5
02-01-001 1.1 4
02-01-001 1.1 2
02-01-001 1.2 5
02-01-001 1.2 1
02-01-001 2.0 3
02-01-001 2.1 5
02-01-001 2.1 4
02-01-001 2.2 2
02-01-001 2.2 2
02-02-002 1.0 5
02-02-002 1.1 3
02-02-002 1.1 1
02-02-002 1.2 5
02-02-002 1.2 2

In another worksheet, I have this formula:

{=AVERAGE(IF(AND(raw!$I$2:raw!$I$1000="02-01-001",raw!$U$2:raw!$U$1000=1.1),raw!$Y$2:raw!$Y$100 0))}

I want this formula to find values in Y for records that have a value
of 02-01-001 in I and a value of 1.1 in U. Looking at the table above,
the resulting value should be (4+2)/2=3. However, all I get is 0.0 as a
result. What is wrong with the formula? And I am pressing
CTRL+SFT+Enter when I get out of the formula.

I would also like to know how I can change the formula so that I can
reference an entire column rather than having to reference 2:1000. The
data will eventually extend past 1000. Thanks.

Thank you very much for your help.


--
fbarbie
------------------------------------------------------------------------
fbarbie's Profile: http://www.excelforum.com/member.php...fo&userid=8110
View this thread: http://www.excelforum.com/showthread...hreadid=354764