Complex Averaging

[John]

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.

 

[Biff]

Hi!

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

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

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

Biff

 

[Bob Phillips]

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

--

HTH

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

 

[Bob Phillips]

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)