average calls per hour
-
- 3StarLounger
- Posts: 310
- Joined: 19 Apr 2010, 16:18
- Location: middle of the state of Washington
average calls per hour
I need to show a graph of the average number of calls received per hour (worksheet sample attached).
So, from midnight-1am, 1am-2am, 2am-3am, etc.
From the data I have in the attached worksheet, how can I do that?
We don't care what day the call came in. Just the hour.
I'm visualizing a line graph?
So, from midnight-1am, 1am-2am, 2am-3am, etc.
From the data I have in the attached worksheet, how can I do that?
We don't care what day the call came in. Just the hour.
I'm visualizing a line graph?
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78481
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: average calls per hour
I'd create a pivot table and pivot chart based on the data. You can group the times by hour in the pivot table.
See the attached version.
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 310
- Joined: 19 Apr 2010, 16:18
- Location: middle of the state of Washington
Re: average calls per hour
perfect.. I was playing with a pivot table myself, but I couldn't figure out how to display the time as just the hour. How did you do that? I set my number format for the time field to the same as yours and it displays 5:49:00 PM but yours is just 5
-
- Administrator
- Posts: 78481
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: average calls per hour
Right-click any of the items in the Rows area of the pivot table.
Select Group... from the context menu.
Clear all selected (highlighted) options, then select Hours.
Click OK.
Select Group... from the context menu.
Clear all selected (highlighted) options, then select Hours.
Click OK.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 310
- Joined: 19 Apr 2010, 16:18
- Location: middle of the state of Washington
Re: average calls per hour
never mind.. you said group by time and I didn't do that.. when I did it that worked, but I can't get rid of the am/pm.... working on that figuring out how you did that now :)
-
- 3StarLounger
- Posts: 310
- Joined: 19 Apr 2010, 16:18
- Location: middle of the state of Washington
Re: average calls per hour
grr ok so how did you get the am/pm not to display?
-
- Administrator
- Posts: 78481
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: average calls per hour
Did you group by Hours?
Do you still see AM/PM when you do that?
Do you still see AM/PM when you do that?
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 310
- Joined: 19 Apr 2010, 16:18
- Location: middle of the state of Washington
Re: average calls per hour
I did group by hours and it displays the am/pm
You do not have the required permissions to view the files attached to this post.
-
- 3StarLounger
- Posts: 310
- Joined: 19 Apr 2010, 16:18
- Location: middle of the state of Washington
Re: average calls per hour
gack. I picked hours promise! just did the wrong screen snip
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78481
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: average calls per hour
Could it be because I use the 24-hour clock in my system settings, and you use a 12-hour clock?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 310
- Joined: 19 Apr 2010, 16:18
- Location: middle of the state of Washington
Re: average calls per hour
In case you want to see what I did in the actual file, I'm attaching it again after I tried to do the pivot.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78481
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: average calls per hour
According to Changing format for grouped hours, it does indeed depend on your system settings:
The format for the grouped hour in pivot tables comes from the long hour format in Regional Settings. (...) When I change the format in Regional Settings and then refresh the pivot, the hour changes from 13 and 1 p.m.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 310
- Joined: 19 Apr 2010, 16:18
- Location: middle of the state of Washington
Re: average calls per hour
awesome. Thank you for your help. My manager said he is fine with the am / pm showing.
-
- 3StarLounger
- Posts: 310
- Joined: 19 Apr 2010, 16:18
- Location: middle of the state of Washington
Re: average calls per hour
We are transferring our electronic records to another software program so my exported files need to match up with the format required of the new program.
I've attached a sample of the spreadsheet I need help with.
I have one worksheet that lists all dogs in our kennel reservation system and the expiration of each of their vaccines.
I need to update a different worksheet with that same data but as one record for the dog, not a separate one for each vaccine.
So, in the example,
Jack has 3 records - one for each vaccine
Simon has 2 records - one for each vaccine
I need to update just the expiration date for each of these dogs' vaccines on their one record in the new format. I typed the information in the first record to show what we need.
We have about 1150 records for 525 dogs. Ugh.
I thought about a vlookup but I don't know how to do it with an IF.
My thoughts were, in the Bordatella (example) cell, use a formula that says, look up Jack in the first table and if column E is Bordetella, then insert the expire date (col F); then do the same for the other vaccine columns.
This is a sample so the actual column numbers aren't the same in my real data. But, they are tables, with these same headings.
Can you help me please?
Thank you!
I've attached a sample of the spreadsheet I need help with.
I have one worksheet that lists all dogs in our kennel reservation system and the expiration of each of their vaccines.
I need to update a different worksheet with that same data but as one record for the dog, not a separate one for each vaccine.
So, in the example,
Jack has 3 records - one for each vaccine
Simon has 2 records - one for each vaccine
I need to update just the expiration date for each of these dogs' vaccines on their one record in the new format. I typed the information in the first record to show what we need.
We have about 1150 records for 525 dogs. Ugh.
I thought about a vlookup but I don't know how to do it with an IF.
My thoughts were, in the Bordatella (example) cell, use a formula that says, look up Jack in the first table and if column E is Bordetella, then insert the expire date (col F); then do the same for the other vaccine columns.
This is a sample so the actual column numbers aren't the same in my real data. But, they are tables, with these same headings.
Can you help me please?
Thank you!
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78481
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: average calls per hour
Do you already have the complete list of dogs (one per row) for the second spreadsheet, or should that be constructed automatically?
(An example of the actual layout, with irrelevant columns left empty, would be helpful)
(An example of the actual layout, with irrelevant columns left empty, would be helpful)
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78481
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: average calls per hour
Another thing: does one sheet really have "Bordetella" and the other "Bordatella"?
(And I assume that "Speices" is just a typo for "Species")
Here is a macro that will populate the second sheet from scratch, assuming that the first row (the headings) has been filled.
See the attached version. It should work if the columns are in other locations too.
(And I assume that "Speices" is just a typo for "Species")
Here is a macro that will populate the second sheet from scratch, assuming that the first row (the headings) has been filled.
Code: Select all
Sub CopyData()
Dim wshS As Worksheet
Dim wshT As Worksheet
Dim s As Long
Dim m As Long
Dim t As Long
Dim c As Long
Dim n As Long
Dim d As Long
Dim colS As New Collection
Dim colT As New Collection
Application.ScreenUpdating = False
Set wshS = Worksheets("Expiry")
n = wshS.Cells(1, wshS.Columns.Count).End(xlToLeft).Column
For c = 1 To n
colS.Add Item:=c, Key:=wshS.Cells(1, c).Value
Next c
Set wshT = Worksheets("New")
n = wshT.Cells(1, wshT.Columns.Count).End(xlToLeft).Column
For c = 1 To n
colT.Add Item:=c, Key:=wshT.Cells(1, c).Value
Next c
t = 1
m = wshS.Cells(wshS.Rows.Count, 1).End(xlUp).Row
For s = 2 To m
If wshS.Cells(s, colS("Customer")).Value <> wshS.Cells(s - 1, colS("Customer")).Value Or wshS.Cells(s, colS("Pet Name")).Value <> wshS.Cells(s - 1, colS("Pet Name")).Value Then
t = t + 1
wshT.Cells(t, colT("Customer ID")).Value = wshS.Cells(s, colS("ID")).Value
wshT.Cells(t, colT("Pet Name")).Value = wshS.Cells(s, colS("Pet Name")).Value
wshT.Cells(t, colT("Column1")).Value = wshT.Cells(t, colT("Customer ID")).Value & " " & wshS.Cells(s, colS("Pet Name")).Value
wshT.Cells(t, colT("Species")).Value = wshS.Cells(s, colS("Pet Type")).Value
End If
Select Case True
Case wshS.Cells(s, colS("Expiring Vaccine")).Value Like "Bordetella*"
c = colT("Bordatella Expiration Date (dog)")
Case wshS.Cells(s, colS("Expiring Vaccine")).Value Like "Rabies*"
c = colT("Rabies Expiration Date (dog)")
Case wshS.Cells(s, colS("Expiring Vaccine")).Value Like "DHLLP*"
c = colT("DHPP Expiration Date (dog)")
End Select
wshT.Cells(t, c).Value = wshS.Cells(s, colS("Expire Date")).Value
Next s
Application.ScreenUpdating = True
End Sub
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 310
- Joined: 19 Apr 2010, 16:18
- Location: middle of the state of Washington
Re: average calls per hour
Yes, I have a worksheet with one row per dog. There are 542 records.
The macro worked perfectly! Thank you!
I struggle with creating/writing macros, so I don't usually even attempt that. I was trying to force a vlookup of some kind.
Thank you so very very much!
The macro worked perfectly! Thank you!
I struggle with creating/writing macros, so I don't usually even attempt that. I was trying to force a vlookup of some kind.
Thank you so very very much!