Looking for formula to split data into groups.

Zabe

New Member
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!

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

hayden

Board Regular
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

Active Member
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"

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

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

Zabe

New Member
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

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

Replies
0
Views
345
Replies
11
Views
918
Replies
7
Views
390
Replies
4
Views
631
Replies
3
Views
277

Forum statistics

1,141,153
Messages
5,704,607
Members
421,359
Latest member
Edwardvanschothorst

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back