Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Filling in the blanks 1

Status
Not open for further replies.

quig699

Technical User
Nov 7, 2006
42
US
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
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
 http://files.engineering.com/getfile.aspx?folder=4f18070e-9fcc-47ce-b93a-8bc516fabd67&file=tt-WeeksSummary.xlsx
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top