Excel help

[ptrifile]

I have a large amount of data that I need to try to manipulate. I have a spreadsheet that shows quarter hour times so ideally it would look like the following:

A1 B1
0 to 14 00:04:30
15 to 29 00:02:45
30 to 44 00:05:00
45 to 59 00:01:25
0 to 14 00:02:30
15 to 29 00:06:45
30 to 44 00:08:00
45 to 59 00:02:25
and so on for the rest of the 22 hours that are left in the day.

The above is listed for each 24 hour time period for any given day. The problem is that the report that this is exported out of (crystal reports) does not display any quarter hour that may have a zero for any quarter hour, so if say the quarter hour for the first hour of the day 15 to 29 is zero it will look like this(missing the "15 to 29" time period in the first hour):

A1 B1
0 to 14 00:04:30
30 to 44 00:05:00
45 to 59 00:01:25
0 to 14 00:02:30
15 to 29 00:06:45
30 to 44 00:08:00
45 to 59 00:02:25

Is there way to go through the spreadsheet and insert the one that is missing in the string and add a 00:00:00 after it?

I hope i explained this well enough. Thank you for any help or suggestions.

Paul

 

[SkipVought]

Hi,

Showing something where there is nothing, is generally not a good practice. No reporting tool can do that, as you have experienced in CR. Nothing worse than paging thru gobs of nothingness!

So, why the DUPLICATION of times? There's obviously things that you do not understand.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptrifile]

Ok these are for call stats for a call center, and unfortunately the way the database structure is on the phone switch there are inconsistencies with some data.

To give some background so i dont seem out of my mind here, i have a report that shows number of calls offered, # of calls answered, Average talk time etc..for each 15 minute interval even if there are no(zero) calls for that particular interval.

Now another report is needed to obtain what is called "After Call Work" but these tables capture at the minute data and not just a sum at the 15 minute interval (which is why the report wont show a 15 minute interval with zero data).

I need to combine the two reports together in excel to get "Average Handle Time" for each 15 minute interval. Average Handle time is the Average Talk time + the Average After Call work.

Im not sure if this is all making sense to you but essentially i would like to copy the row with the ACW right into the other spreadsheet so the 15 minute intervals match up properly.

As for the duplication of times, each 15 minute interval needs to be broken out by hour for call forecasting.

Paul

 

[SkipVought]

As for the duplication of times, each 15 minute interval needs to be broken out by hour for call forecasting.

Does not answer the question, why is 0 to 14 in your table TWICE???

BTW, it is MUCH MUCH more difficult to analyize data from a REPORT like this, than from the source data which would have the actual Date/Time stamp for each event. You seem to be shooting yourself in the foot with one hand tied behide you back, blindfolded, with ear plugs.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptrifile]

each quarter interval will be listed in the table 24 times as there are 24 hours in a day.

There is an actual date/time stamp but the information needs to be broken out by sums of quarter hours not exactly when the transaction took place(which I have already accomplished through crystal reports).

 

[SkipVought]

I need to combine the two reports together in excel to get "Average Handle Time" for each 15 minute interval. Average Handle time is the Average Talk time + the Average After Call work.

If you intend to use this kind of a report to answer the requirement as you have stated it, it will be EXTREMELY DIFFIULT. You seem to be focused more on what is pretty, than what is practical.

FIRST get your data analysis needs addressed.

THEN cast the results in a form that your users will find useful.

The answer to your original question must involve the HOUR data along with the minute ranges. You START with your 24 * 4 rows for these two columns (Hour and Min Range) and then use a LOOKUP to your report to return the duration as report fot that HOUR Minute Range.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptrifile]

Thanks for the help SkipVought, this data isn't going to be used by end users its going to be fed to a program that analyzes and forecasts call data at the 15 minute level.

Again i appreciate the help, i think i may have come up with a time consuming but usable solution using a simple vlookup.

Thanks again

Paul

 

[SkipVought]

So here's the starting point for your report in Excel...
[tt]
Hr Min Range Duration
0 0 to 14
0 15 to 29
0 30 to 44
0 45 to 59
1 0 to 14
1 15 to 29
1 30 to 44
1 45 to 59
...
[/tt]
and the formulas to copy down...
[tt]
A2: =INT((ROW()-2)/4)
b2: =INDEX(MinRngTbl,MOD(ROW()-2,4)+1,1)
[/tt]
Where the MinRngTbl looks like this...
[tt]
MinRngTbl
0 to 14
15 to 29
30 to 44
45 to 59
[/tt]
Then use a lookup formula, maybe SUMPRODUCT, to return the druation from your CR report sheet.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}