I assumed column I aren't dates.

Jason

-----Original Message-----
=AVERAGE(IF((raw!$I$2:$I$1000=--"2005-01-02")*(raw!
$U$2:$U$1000=1.1),raw!$Y$
2:$Y$1000))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jason Morin" wrote
in message
...
I didn't test this, but it should work:

=AVERAGE(IF((raw!$I$2:$I$1000="02-01-001")*(raw!
$U$2:raw!
$U$1000=1.1),raw!$Y$2:$Y$1000))

Array-entered.

HTH
Jason
Atlanta, GA

-----Original Message-----

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$1000))}

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?
action=getinfo&userid=8110
View this thread:
http://www.excelforum.com/showthread.php?
threadid=354764

.



.