I asked this question before and want to continue this train of thought. I
now want to add another complication. I want to average the CPI of my
projects based on phase and division.

My data set will look like this:

ProjectName CPI Phase Division
Project1 .9 Active AppDev
Project2 .6 Active AppDev
Project3 1.1 Active AppDev
Project4 .78 Active Comm

Below is the previous post and reply on this topic on Feb 10. But I don't
know how to do the above.

Thanks



Hi,

Lets say the CPI is in range B2:B4 and the phase is in
range C2:C4 you can use either of these formulas:

=SUMIF(C2:C4,"Active",B2:B4)/COUNTIF(C2:C4,"Active")

entered normally

or

=AVERAGE(IF(C2:C4="Active",B2:B4)) entered with Ctrl
+Shift+enter as this is an array formula.

Thanks
Govind.




-----Original Message-----
I am looking to find the average of a column of numbers,
but the specific
cells in that column depend on the data in another
column. I want to
average the CPI of a column of project CPIs, but only if
that project is in
the active status.

An example of my data will look like this:

ProjectName CPI Phase
Project1 .9 Active
Project2 .6 Inactive
Project3 1.1 Active


I need the formula to average Project1 and Project3's
CPI because they are
active.

Thanks
John


.

Hi!

Try either one of these. The first formula is an array:

=AVERAGE(IF(C1:C4="active",IF(D1:D4="AppDev",B1:B4 )))

=SUMPRODUCT(--(C1:C4="active"),--
(D1:D4="AppDev"),B1:B4)/SUMPRODUCT(--(C1:C4="active"),--
(D1:D4="AppDev"))

Biff

-----Original Message-----
I asked this question before and want to continue this
train of thought. I
now want to add another complication. I want to average
the CPI of my
projects based on phase and division.

My data set will look like this:

ProjectName CPI Phase Division
Project1 .9 Active AppDev
Project2 .6 Active AppDev
Project3 1.1 Active AppDev
Project4 .78 Active Comm

Below is the previous post and reply on this topic on Feb
10. But I don't
know how to do the above.

Thanks



Hi,

Lets say the CPI is in range B2:B4 and the phase is in
range C2:C4 you can use either of these formulas:

=SUMIF(C2:C4,"Active",B2:B4)/COUNTIF(C2:C4,"Active")

entered normally

or

=AVERAGE(IF(C2:C4="Active",B2:B4)) entered with Ctrl
+Shift+enter as this is an array formula.

Thanks
Govind.




-----Original Message-----
I am looking to find the average of a column of numbers,
but the specific
cells in that column depend on the data in another
column. I want to
average the CPI of a column of project CPIs, but only if
that project is in
the active status.

An example of my data will look like this:

ProjectName CPI Phase
Project1 .9 Active
Project2 .6 Inactive
Project3 1.1 Active


I need the formula to average Project1 and Project3's
CPI because they are
active.

Thanks
John


.



.

=SUMPRODUCT(--(C2:C4="Active"),--(D2:D4="AppDev"),B2:B4)/SUMPRODUCT(--(C2:C4
="Active"),--(D2:D4="AppDev"))

--

HTH

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


"John" <john(dot)cole(at)co(dot)riverside(dot)ca(dot)us wrote in message
...
I asked this question before and want to continue this train of thought.
I
now want to add another complication. I want to average the CPI of my
projects based on phase and division.

My data set will look like this:

ProjectName CPI Phase Division
Project1 .9 Active AppDev
Project2 .6 Active AppDev
Project3 1.1 Active AppDev
Project4 .78 Active Comm

Below is the previous post and reply on this topic on Feb 10. But I don't
know how to do the above.

Thanks



Hi,

Lets say the CPI is in range B2:B4 and the phase is in
range C2:C4 you can use either of these formulas:

=SUMIF(C2:C4,"Active",B2:B4)/COUNTIF(C2:C4,"Active")

entered normally

or

=AVERAGE(IF(C2:C4="Active",B2:B4)) entered with Ctrl
+Shift+enter as this is an array formula.

Thanks
Govind.




-----Original Message-----
I am looking to find the average of a column of numbers,
but the specific
cells in that column depend on the data in another
column. I want to
average the CPI of a column of project CPIs, but only if
that project is in
the active status.

An example of my data will look like this:

ProjectName CPI Phase
Project1 .9 Active
Project2 .6 Inactive
Project3 1.1 Active


I need the formula to average Project1 and Project3's
CPI because they are
active.

Thanks
John


.

The first of these is an array formula, so commit with Ctrl-Shift-Enter if
you use that one.

--

HTH

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


"Biff" wrote in message
...
Hi!

Try either one of these. The first formula is an array:

=AVERAGE(IF(C1:C4="active",IF(D1:D4="AppDev",B1:B4 )))

=SUMPRODUCT(--(C1:C4="active"),--
(D1:D4="AppDev"),B1:B4)/SUMPRODUCT(--(C1:C4="active"),--
(D1:D4="AppDev"))

Biff

-----Original Message-----
I asked this question before and want to continue this
train of thought. I
now want to add another complication. I want to average
the CPI of my
projects based on phase and division.

My data set will look like this:

ProjectName CPI Phase Division
Project1 .9 Active AppDev
Project2 .6 Active AppDev
Project3 1.1 Active AppDev
Project4 .78 Active Comm

Below is the previous post and reply on this topic on Feb
10. But I don't
know how to do the above.

Thanks



Hi,

Lets say the CPI is in range B2:B4 and the phase is in
range C2:C4 you can use either of these formulas:

=SUMIF(C2:C4,"Active",B2:B4)/COUNTIF(C2:C4,"Active")

entered normally

or

=AVERAGE(IF(C2:C4="Active",B2:B4)) entered with Ctrl
+Shift+enter as this is an array formula.

Thanks
Govind.




-----Original Message-----
I am looking to find the average of a column of numbers,
but the specific
cells in that column depend on the data in another
column. I want to
average the CPI of a column of project CPIs, but only if
that project is in
the active status.

An example of my data will look like this:

ProjectName CPI Phase
Project1 .9 Active
Project2 .6 Inactive
Project3 1.1 Active


I need the formula to average Project1 and Project3's
CPI because they are
active.

Thanks
John


.



.