Complex Average
 

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,

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


.

Maybe a mod of this.
this is an ARRAY formula so must be entered/edited with ctrl+shift+enter

=AVERAGE(IF(H2:H1010,H2:H10))


--
Don Guillett
SalesAid Software

"John" <john(dot)cole(at)co(dot)riverside(dot)ca(dot)us wrote in 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