What is wrong with this =AVERAGE(IF formula?

[fbarbie]

Hi everybody,

I have an =AVERAGE(IF… statement in one worksheet that refers to ra
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 valu
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
result. What is wrong with the formula? And I am pressin
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 ca
reference an entire column rather than having to reference 2:1000. Th
data will eventually extend past 1000. Thanks.

Thank you very much for your help

 

[Bernard Liengme]

You cannot use Boolean function in an array formula
Try:
SUMPRODUCT(--(raw!$I$2:raw!$I$1000="02-01-001"),--(raw!$U$2:raw!$U$1000=1.1),raw!$Y$2:raw!$Y$1000)/SUMPRODUCT(--(raw!$I$2:raw!$I$1000="02-01-001"),--(raw!$U$2:raw!$U$1000=1.1))best wishes--Bernard V Liengmewww.stfx.ca/people/bliengmeremove caps from email"fbarbie" <[email protected]> wrote in messageHi 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>

 

[Jason Morin]

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

 

[Bob Phillips]

Your syntax has a lot of problems, bu here's an alternative

=SUMPRODUCT(--(raw!$I$2:raw!$I$2:$I$10=--"2005-01-02"),--(raw!$I$2:raw!$U$2:
$U$10=1),raw!$I$2:raw!$Y$2:$Y$10)/SUMPRODUCT(--(raw!$I$2:raw!$I$2:$I$10=--"2
005-01-02"),--(raw!$I$2:raw!$U$2:$U$10=1))

--

HTH

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

 

[Bob Phillips]

=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]

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)





.