What is wrong with this =AVERAGE(IF formula?

[fbarbie]

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.

[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 messagenews:[email protected]...>> 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>

[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

>-----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...hreadid=354764
>
>.
>

[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)


"fbarbie" <[email protected]> wrote in message
news:[email protected]...
>
> 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...fo&userid=8110
> View this thread: http://www.excelforum.com/showthread...hreadid=354764
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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...hreadid=354764
> >
> >.
> >

[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)
>
>
>"Jason Morin" <[email protected]> wrote
in message
>news:[email protected]...
>> 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
>> >
>> >.
>> >
>
>
>.
>