Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average (geometric) | Excel Worksheet Functions | |||
average on 2 conditions | Excel Worksheet Functions | |||
calculate average hours and minutes | Excel Worksheet Functions | |||
calculate average hours worked | Excel Worksheet Functions | |||
average, array and offsets | Excel Worksheet Functions |