Looking for formula to split data into groups.

[Zabe]

Hello. I work for a call center, and have been asked to collate some data. I'm hoping I can get Excel to do one of the tedious tasks for me.

I've got a spreadsheet where calls are listed and the times they came in. What I'm looking to do is find out how many calls came in during each half hour increment.

So column "A" shows the times the calls came in. I picture column "B" having a list of half hour increments (10:30-11:00, 11:00-11:30, 11:30-12:00, etc) then column "C" showing the number of calls in column "A" that fit within each increment of column "B".

Any tips on how I might pull this off? Thanks alot!

 

[hayden]

Welcome to the board Zabe.

I am not sure if the round function works for times but if it does you could use it to round your times from column a to the nearest half hour. Then you could use the sumif function to get the values in column C. You could probably also use the sumproduct function. I would read the help for those three things and see what you can come up with. Of course if you can't figure it out post back and I am sure that someone can help you out.

Good Luck

Hayden

 

[cmhoz]

The way I would do this is by creating a function in VB, rather than trying to use nested IFs or something in a formula.

1. Change the format of these cells containing times to 'General' ... you'll see that they change to decimals, this is how excel understands time, as pieces of the day.

2. Press Alt+F11 to open VB. Choose Insert --> Module

3. Paste this into the window that opens up:

Function timegroup(time)
Select Case time
Case time <= 0.375
timegroup = "Before 9am"
Case time <= 0.0395833
timegroup = "9 - 9:30am"
Case time <= 0.416666667
timegroup = "9:30 - 10am"

' insert additional times here...

Case Else
timegroup = "unknown time"
End Select

End Function

4. Now, go back to your excel workbook and in column B2 (for example) use the formula you just created by typing... =timegroup(A2)

It will bring back the appropriate timegroup.

5. You will need to continue the code, to include all the time groups you want to have... just copy and paste what is there where it tells you to, changing the <= number and time group.

To find out the number that goes with each time, just run all the times you want in one column of a blank worksheet, copy and paste them into the next column, and change the second column to 'general' format.

Hope that makes some kind of sense!
Cheers

 

[Zabe]

Sounds like a good way to go. I'll give it a shot. Thanks for the help guys!

 

[Zabe]

Hmmm, I am apparently missing something.

To test cmhoz's suggestion, I used the following module:
Function timegroup(time)
Select Case time
Case time <= 0.375
timegroup = "Before 9am"
Case time <= 0.395833
timegroup = "9 - 9:30am"
Case time <= 0.416666667
timegroup = "9:30 - 10am"

Case Else
timegroup = "After 10am"

End Select

End Function

Now, the issue is, I keep getting a return of the Case Else statement for each entry, even if it should fit in one of the designated case times.

for example, in:
A2 I have 0.3542939814
B2 I have =Timegroup(A2)
the results should come back Before 9am, but are instead coming back After 10am.

Am I missing a key quotation mark or something in my formula? Thanks again!

 

[Zabe]

ahah. Switched it from Case time <= 0.375 to Case Is <= 0.375 and that seems to work!