Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif and Date Comparison
Issues Log records date an issue is raised and a target date to complete
action (this being the date raised + 20 days). There is one issue per row. I need to count the number of instances where the target date has been missed, ie where the date completed is more than 20 days after the date raised. Then I would like to express this as a percentage of the total issues raised. Can anyone suggest a suitable formula please? Date Issue Raised Target Date Action Complete a a+20 a+10 b b+20 b+21 c c+20 c+30 Count if action completed in more than 20 days |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif and Date Comparison
=SUMPRODUCT(--(C2:C200-A2:A20020))
and =E1/COUNT(A2:A20) assuming the first formula is in E1 -- __________________________________ HTH Bob "Chas" wrote in message ... "Issues Log" records date an issue is raised and a target date to complete action (this being the date raised + 20 days). There is one issue per row. I need to count the number of instances where the target date has been missed, ie where the date completed is more than 20 days after the date raised. Then I would like to express this as a percentage of the total issues raised. Can anyone suggest a suitable formula please? Date Issue Raised Target Date Action Complete a a+20 a+10 b b+20 b+21 c c+20 c+30 Count if action completed in more than 20 days |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif and Date Comparison
Try this formula:
=SUMPRODUCT(--(C2:C4B2:B4))/(COUNTA(A:A)-1) Format the result cell as percent! Regards, Stefi Chas ezt *rta: Issues Log records date an issue is raised and a target date to complete action (this being the date raised + 20 days). There is one issue per row. I need to count the number of instances where the target date has been missed, ie where the date completed is more than 20 days after the date raised. Then I would like to express this as a percentage of the total issues raised. Can anyone suggest a suitable formula please? Date Issue Raised Target Date Action Complete a a+20 a+10 b b+20 b+21 c c+20 c+30 Count if action completed in more than 20 days |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date comparison | Excel Worksheet Functions | |||
Date comparison | Excel Discussion (Misc queries) | |||
Date comparison | Excel Worksheet Functions | |||
Date comparison | Excel Discussion (Misc queries) | |||
COUNTIF with compound comparison | Excel Worksheet Functions |