P
Paul
Excel 2003. I have been maintaining a workbook with one tab per month. For
each process date I capture the completion times for two specific job
streams. The jobs have an SLA of 06:00.
In the example data below, I've included a potential range name to make the
current calcs easier to read.
Example (labels are columns and data is rows in actual worksheet):
Process Date 06/06 (Range = Dates)
Date Completed 06/07
Search Publish 05:57 (Range = Search)
Search Missed SLA N (Range = Missed_S
Asset Publish 08:23 (Range = Asset)
Asset Missed SLA Y (Range = Missed_A)
The missed SLA columns are currently manually populated with Y (missed SLA),
N (didn't miss SLA) and S (missed SLA but the delivery date for the data was
a non-business day, i.e. Sat/holiday).
I am trying to calculate for each month (including the current month for
whatever data is available):
1 - Avg completion time = AVERAGE(Search)
2 - Earliest = MIN(Search)
3 - Latest = MAX(Search)
4 - Avg excluding earliest and latest times
{=AVERAGE(IF(Search<>"",IF(Search<>MAX(Search),IF(Search<>MIN(Search),Search,""))))} OR
=(SUM(Search)-MAX(Search)-MIN(Search))/(COUNT(Search)-2) (thanks to those on
the site that helped me with those)
5 - Avg time for those dates where the publish time was > than the SLA time
(i.e. 06:00)
=DAVERAGE(A2:F23,C2,A31:F33) where A2:F23 is the full data table, C2 is the
column header for the Search publish times, and A31:F33 contains the table
headers again and the values "Y","S" in separate rows under the appropriate
Missed SLA column (Search or Asset).
6 - Missed SLA count = COUNTIF(Missed_S,"Y")+COUNTIF(Missed_S,"S")
7 - Total Days in month = COUNT(Missed_S)
8 - % the SLA was missed = Missed SLA count/Total days in month
9 - % the SLA was missed if I exclude Sat/Holiday completion days (i.e.
Friday's job finished at 08:00 on Sat but I'll exclude that miss from the
second % calc since that miss is 'less important'.
= COUNTIF(Missed_S,"Y")/Total days in month
Still with me? Having done this for a few months - creating one tab per
month, copying the tabs, adjusting the dates and modifying the ranges in the
calcs - I can say this is not the way I'd like to do it long term. I have
created a single data table with just the process date, search publish time
and asset publish time. I can manually create a summary table - easier to
read than going to the individual tabs, but it still requires me to manually
select or name ranges to get the data for each month. Here's an example for
the avg excluding earliest and latest times where I am using the previously
calculated max and min values:
{=AVERAGE(IF(MONTH(Dates)=6,IF(Dates<>G2),IF(Dates<>H2),Search,""))))}
Technically I can copy this down and just change the month to 7, 8, 9...but
that only works if I have one year. After that I need to add the YEAR
(doable, but still fairly manual).
I also tried to auto calc the % Missed SLA in the new table but cannot get
it to work:
{=COUNT(IF(MONTH(Dates)=6,IF(Search>$Q$2,Search,"")))} - $Q$2 is a constant
- 06:00 - representing the SLA cutoff (I couldn't enter a value in the
formula directly either - no idea what value to use to get the right result -
but using a cell reference is better anyway).
In the ideal situation I would use a pivot table and the group by
Years/Months function to derive the results automatically. Works well for the
AVG/MAX/MIN/COUNT columns but is nasty for anything more complex. All I would
need to do is update the source data range every once in a while. However, I
have been unable to figure out how to get the custom calculations into the
pivot table.
Any suggestions? Thanks for hanging in thru this long post.
each process date I capture the completion times for two specific job
streams. The jobs have an SLA of 06:00.
In the example data below, I've included a potential range name to make the
current calcs easier to read.
Example (labels are columns and data is rows in actual worksheet):
Process Date 06/06 (Range = Dates)
Date Completed 06/07
Search Publish 05:57 (Range = Search)
Search Missed SLA N (Range = Missed_S
Asset Publish 08:23 (Range = Asset)
Asset Missed SLA Y (Range = Missed_A)
The missed SLA columns are currently manually populated with Y (missed SLA),
N (didn't miss SLA) and S (missed SLA but the delivery date for the data was
a non-business day, i.e. Sat/holiday).
I am trying to calculate for each month (including the current month for
whatever data is available):
1 - Avg completion time = AVERAGE(Search)
2 - Earliest = MIN(Search)
3 - Latest = MAX(Search)
4 - Avg excluding earliest and latest times
{=AVERAGE(IF(Search<>"",IF(Search<>MAX(Search),IF(Search<>MIN(Search),Search,""))))} OR
=(SUM(Search)-MAX(Search)-MIN(Search))/(COUNT(Search)-2) (thanks to those on
the site that helped me with those)
5 - Avg time for those dates where the publish time was > than the SLA time
(i.e. 06:00)
=DAVERAGE(A2:F23,C2,A31:F33) where A2:F23 is the full data table, C2 is the
column header for the Search publish times, and A31:F33 contains the table
headers again and the values "Y","S" in separate rows under the appropriate
Missed SLA column (Search or Asset).
6 - Missed SLA count = COUNTIF(Missed_S,"Y")+COUNTIF(Missed_S,"S")
7 - Total Days in month = COUNT(Missed_S)
8 - % the SLA was missed = Missed SLA count/Total days in month
9 - % the SLA was missed if I exclude Sat/Holiday completion days (i.e.
Friday's job finished at 08:00 on Sat but I'll exclude that miss from the
second % calc since that miss is 'less important'.
= COUNTIF(Missed_S,"Y")/Total days in month
Still with me? Having done this for a few months - creating one tab per
month, copying the tabs, adjusting the dates and modifying the ranges in the
calcs - I can say this is not the way I'd like to do it long term. I have
created a single data table with just the process date, search publish time
and asset publish time. I can manually create a summary table - easier to
read than going to the individual tabs, but it still requires me to manually
select or name ranges to get the data for each month. Here's an example for
the avg excluding earliest and latest times where I am using the previously
calculated max and min values:
{=AVERAGE(IF(MONTH(Dates)=6,IF(Dates<>G2),IF(Dates<>H2),Search,""))))}
Technically I can copy this down and just change the month to 7, 8, 9...but
that only works if I have one year. After that I need to add the YEAR
(doable, but still fairly manual).
I also tried to auto calc the % Missed SLA in the new table but cannot get
it to work:
{=COUNT(IF(MONTH(Dates)=6,IF(Search>$Q$2,Search,"")))} - $Q$2 is a constant
- 06:00 - representing the SLA cutoff (I couldn't enter a value in the
formula directly either - no idea what value to use to get the right result -
but using a cell reference is better anyway).
In the ideal situation I would use a pivot table and the group by
Years/Months function to derive the results automatically. Works well for the
AVG/MAX/MIN/COUNT columns but is nasty for anything more complex. All I would
need to do is update the source data range every once in a while. However, I
have been unable to figure out how to get the custom calculations into the
pivot table.
Any suggestions? Thanks for hanging in thru this long post.