What is wrong with this =AVERAGE(IF formula?
 

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

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

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

.

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

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

.

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

.



.