Filling in the blanks 1

[quig699]

Hi,

I have been researching this and I am either not wording my searches correctly or I have been staring this for far to long. I have a spread sheet that outlines the calls my company gets in hour increments. But it only lists the hours in which we receive calls. Here is an example:

Date------------Time------- Calls
9/19/2016-------07:00---------3
9/19/2016-------09:00---------4
9/19/2016-------12:00---------4
9/19/2016-------17:00---------8

I need to find a way to add all the other hours where we didn't receive calls to get something like:

Date-----------Time------Calls
9/19/2016------05:00-------0
9/19/2016------06:00-------0
9/19/2016------07:00-------3
9/19/2016------08:00-------0
9/19/2016------09:00-------4

Any suggestions? I need to do this for about six months worth of data.

Thank you!!!!


Thanks,

Amy

 

[SkipVought]

Hi,

Do you REALLY want data that means NOTHING? You could have days with no calls: 24 rows with ZEROS???

Standard reporting, as in the PivotTable feature in Excel will do the same thing.

There's a way to do this using queries, making a table of all time values and then performing an outer join using that table and your call count table.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[quig699]

The reason is because I am trying to get averages for each our on specific days. If I don't have the days/hours where 0 calls were taken the methods I have come across to average divide by the number of hours that show a value. So lets say I have four Saturdays I am looking at and only three received calls at 10am. when I ask for an average of calls at 10am the average calulation is based on the three Saturdays with data and not the four Saturday's that I am looking at.

I hope that makes sense.

Thank you

Thanks,

Amy

 

[SkipVought]

So are your statistics for the month? There are 4 Saturday's in a given month, but only data for 3 @ 10:00? Wouldn't that be [Call Count per criteria]/[Day Count per criteria] that is 4 days per month not 3 days per data count.

Let me see what I can develop on that basis.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Can you state the various biases for doing your averages? I'm assuming a Start date and and End date on which to perform a calculation. What other criteria need to be considered?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[quig699]

You are correct, we are looking for a start date, end date and then by the day of week. With the raw data example I provided I have a separate colum for the day of week extracted from the date with =Text(A2, "DDDD"). Easy enough to add to the data. That is all the criteria that we would need.

Thank you!!!

Thanks,

Amy

 

[SkipVought]

So for Sept 2016 the days of week per month...
[pre]
S M T W T F S
4 4 4 4 5 5 4
[/pre]
...yes?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[quig699]

yes!!

Thanks,

Amy

 

[SkipVought]

Okay, here's a solution, or the beginning of a solution if you also need to include TIMES in any criteria.

These are the essentials of this sheet:

1. Your table is a Structured Table named tCalls.

2. Enter a Start value in the yellow cell. End is a calculated value as the first of the next month. Start and End are also Named Ranges.

3. The Days of Week table has these features
DOW: Day of Week
First Week: The days for the first week
DTE: The dates for the first week of Start
DIM: Days in Month for each DOW
Calls: The number of calls for the month for DOW
Calls/DIM: Calls/DIM

4. Formulas:
First Week: =IF(WEEKDAY(Start)=I1,1,O3+1)
DTE: =DATE(YEAR(Start),MONTH(Start),I3)
DIM: =IF(MONTH(I4+28)=MONTH(Start),5,4)
Calls: =SUMPRODUCT((tCalls[Date]>=Start)*(tCalls[Date]<End)*(TEXT(tCalls[Date],"ddd")=I2)*(tCalls[Calls ]))
Calls/DIM: =I6/I5

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]