In today's Dueling Podcast, Episode #1434, Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen are asked to work with a Spreadsheet with data that summarizes a list of call times... The objective, in using the Data, is to determine how many calls occurred in each quarter hour? Mike and Bill offer two solutions to reach the quarter hour count.
Transcript of the video:
Hey, welcome back. It's another Dueling Excel podcast. I'm Bill Jalen from MrExcel. I will be joined by Mike Girvin from Excel is fun.
This is episode 84: calls by quarter-hour.
All right, so, Mike sent me this one and I'm going to rename this from dueling podcast to watching a guy do a lot of things that doesn't work. The deal is we have a whole log of calls here. We want to figure out how many calls came in every quarter hour.
Well, hey, pivot table, right? Insert, pivot table, OK. I want to put calls received in row labels and I want to group that up to quarter hour-- shoot. When I go to hours, there is no way to say quarter hours. If I would go to just days, I could say three days, seven days, but when I choose just minutes, see there is no number of minutes or number of hours, so not going to work.
All right, let's go back. I'm going to do a little bit of math here in this sheet. We need to know how many quarter hours are in a day. Well, there are 24 hours in a day, there are four quarter hours. So, that is 24 times 4, 96 and, you know, a day is equal to the number one. It's stored as 24 hours-- as one, so if I take plus 1 divided by-- oops.
Plus 1 divided by 96, that is the fractional portion that I wanted.
Initially, I thought, well, hey, let's try =MROUND of this into that decimal, press F4 to lock it down, that ROUND. So, 2:57 rounds up to 3 o'clock and 6:37 rounds back to 6:30.
That's not exactly what he was asking for.
He's looking for quarter-hour periods. So, let's try this. Equal this time divided by that little tiny decimal, press F4 there and I need to take the INT of that.
So, how many times does that little fraction, that quarter of an hour, fall into that time?
That is formatted incorrectly. We then have to multiply it after we take the integer to get rid of the decimal portion. Multiply it back by that number, F4 and see that always backs us back to the previous quarter hour.
So, even if we're almost up to 2:59:59, it's going back to 2:45. So, I'm going to call this when and I’m going to add something here called count. Count is just simple as 1, double-click to shoot it down, make sure that I have a blank row they're above my headings, and insert, pivot table, OK. Let's bring the field list over so you can see it. We're going to have when going down the left-hand side, count in some values and there we go. By every single quarter hour, how many calls came in?
All right, Mike, let's see what you have.
Mike: Thanks, MrExcel. Man, I love the pivot table. I tried the exact same thing. I tried to group the times, couldn't do it, so I loved your helper columns to get the pivot table because pivot tables are so easy to create.
So, of course, I tried to group the times and couldn't get it to work, so I said, okay, I'll default to formulas.
Now, I have a column of times here and if I'm going to count using some formula, I'm going to just choose to do an upper and a lower time and then count between those. All right, so, I'm going to come back over here.
If I do that, if I have a column with times, in order to create enough categories, I need to figure out what the max and min time is.
Now, I'm going to use this column here a lot of times throughout this formula, so I'm going to name it. I'm going to click in the label, Ctrl Shift down arrow and I'm going to use the keyboard shortcut Ctrl Shift F3 that names, that's going to name this column whatever's in the first cell there. I click OK. If I click just in the times and Ctrl Shift down, I can see the time there or I can go up to formulas, name manager. I can see sure enough it is created. Now, I can use that name throughout all my formulas.
All right, I got to make sure I have enough category, so I'm going to go MAX and I'm going to say C. Notice there's a dog tag there and I can see it in blue, so all I have to do is hit tab. =MIN, C, tab, enter. The start time, I'm looking-- well, I need enough categories to close-- to cover almost to midnight and about 12 noon 18. So I'm going to start at noon. 12:00 space PM and the increment-- Now, I love the way MrExcel did it. He just did 12 times 4. Here’s how I did it. I said equals, well, I know time is a proportion between 0 and 1, so 24 hours is 1. So, I'm going to divide that by 24 and then I need quarter hours, so-- or every 15 minutes, so I said divide by 4. Then I looked and said, well, I don't want to leave it like that.
To reduce the fraction, I need to multiply the top part of the fraction and the lower part by 24, so that would leave me with this in the denominator.
I didn't know how to do that multiplication in my head, so I simply highlighted it and hit the F9 key, which is evaluate, and boom.
I can leave that hard-coded in there. That is 15 minutes as a proportion of one 24-hour day. All right, now, when creating categories, if you look back over here, notice we're going to have a 12:15 and a 12:15 here. So, you have to be careful.
I mean, I just say I'm going to include the lower time but not the upper. That way I won't double count anywhere. So, I'm going to equals that cell, tab, to get the lower and then equal this one plus my increment and F4 to lock it. Now, I can copy that down and here I'm always going to go equals one cell up and over.
All right, so, those two formulas I can copy down and those will give me my categories.
Now, I could have just typed the times in and increment-- oops. Now, notice I went too far here. If I hit the delete key, that just deletes the content. Ctrl Z. So, what I really want to do is home, clear all-- that clears content and formatting. Keyboard shortcut in 2007 is Alt H E A Ctrl Z.
I still remember the old one and since both are three letters long keyboard, I still use the old one; Alt E A A. I actually have those listed up here. All right, so we have our categories. Now, again, when I do my counting over here, I'm going to include-- I’m going to say greater than or equal to 12 p.m., but less than 12:15.
All right, if you're in 2007 and 10, you can use COUNTIFS, criteria range, C, tab, comma, in double quotes, we're going to say greater than or equal to because we want to include the lower, end double quotes and then join, symbol ampersand there, okay? What's so nice about this function-- you have criteria range one, criteria one, comma, criteria range two, C tab, because we've named it, comma, and then I don't want to include the lower one, so I just put a less than symbol. That formula in 2007 will work all the way down. Double-click and send it down.
The defined names are also locked, which is helpful in this situation. All right, what about 2003 and before? Well, we're going to have to kind of back into it. I'm going to build a form that says count all of the ones that are less than 12:15. If I say less than, it won't include that one and I'm going to count all those and then subtract that-- COUNTIF again, but less than this one.
That's what-- when we get down here, we'll say, the logic will be count all the values less than 1:15, so that won't be included, minus all the ones less than 1 p.m. Because we're saying less than 1 p.m., the 1 p.m.
will be included in this category. So, it's two COUNTIFS and the criteria is going to be less than in both cases. I'm going to say, the bigger one count less than all of the bigger ones minus COUNTIF, if only I could type, down arrow tab-- if only I could type.
COUNTIF and then the range is C comma and it's still less than, remember, then we’ll say less than the lower but that'll include the lower here.
All right, so, double-click and send that down. So, there's 2003 and earlier, 2007 and later. All right, I’ll throw it back to MrExcel.
Bill: Alt E A A. I still do that. The Experimental Aircraft Association to clear all cells. I love the COUNTIFS with two different conditions on the same column. I've always done it where I look at column A is something and column B is something to replace the SUMPRODUCT, but that's a great use. It’s something every week with these.
Well, I want to thank everyone for stopping by. We'll see you next week for another Dueling Excel podcast from MrExcel and Excel is Fun.
This is episode 84: calls by quarter-hour.
All right, so, Mike sent me this one and I'm going to rename this from dueling podcast to watching a guy do a lot of things that doesn't work. The deal is we have a whole log of calls here. We want to figure out how many calls came in every quarter hour.
Well, hey, pivot table, right? Insert, pivot table, OK. I want to put calls received in row labels and I want to group that up to quarter hour-- shoot. When I go to hours, there is no way to say quarter hours. If I would go to just days, I could say three days, seven days, but when I choose just minutes, see there is no number of minutes or number of hours, so not going to work.
All right, let's go back. I'm going to do a little bit of math here in this sheet. We need to know how many quarter hours are in a day. Well, there are 24 hours in a day, there are four quarter hours. So, that is 24 times 4, 96 and, you know, a day is equal to the number one. It's stored as 24 hours-- as one, so if I take plus 1 divided by-- oops.
Plus 1 divided by 96, that is the fractional portion that I wanted.
Initially, I thought, well, hey, let's try =MROUND of this into that decimal, press F4 to lock it down, that ROUND. So, 2:57 rounds up to 3 o'clock and 6:37 rounds back to 6:30.
That's not exactly what he was asking for.
He's looking for quarter-hour periods. So, let's try this. Equal this time divided by that little tiny decimal, press F4 there and I need to take the INT of that.
So, how many times does that little fraction, that quarter of an hour, fall into that time?
That is formatted incorrectly. We then have to multiply it after we take the integer to get rid of the decimal portion. Multiply it back by that number, F4 and see that always backs us back to the previous quarter hour.
So, even if we're almost up to 2:59:59, it's going back to 2:45. So, I'm going to call this when and I’m going to add something here called count. Count is just simple as 1, double-click to shoot it down, make sure that I have a blank row they're above my headings, and insert, pivot table, OK. Let's bring the field list over so you can see it. We're going to have when going down the left-hand side, count in some values and there we go. By every single quarter hour, how many calls came in?
All right, Mike, let's see what you have.
Mike: Thanks, MrExcel. Man, I love the pivot table. I tried the exact same thing. I tried to group the times, couldn't do it, so I loved your helper columns to get the pivot table because pivot tables are so easy to create.
So, of course, I tried to group the times and couldn't get it to work, so I said, okay, I'll default to formulas.
Now, I have a column of times here and if I'm going to count using some formula, I'm going to just choose to do an upper and a lower time and then count between those. All right, so, I'm going to come back over here.
If I do that, if I have a column with times, in order to create enough categories, I need to figure out what the max and min time is.
Now, I'm going to use this column here a lot of times throughout this formula, so I'm going to name it. I'm going to click in the label, Ctrl Shift down arrow and I'm going to use the keyboard shortcut Ctrl Shift F3 that names, that's going to name this column whatever's in the first cell there. I click OK. If I click just in the times and Ctrl Shift down, I can see the time there or I can go up to formulas, name manager. I can see sure enough it is created. Now, I can use that name throughout all my formulas.
All right, I got to make sure I have enough category, so I'm going to go MAX and I'm going to say C. Notice there's a dog tag there and I can see it in blue, so all I have to do is hit tab. =MIN, C, tab, enter. The start time, I'm looking-- well, I need enough categories to close-- to cover almost to midnight and about 12 noon 18. So I'm going to start at noon. 12:00 space PM and the increment-- Now, I love the way MrExcel did it. He just did 12 times 4. Here’s how I did it. I said equals, well, I know time is a proportion between 0 and 1, so 24 hours is 1. So, I'm going to divide that by 24 and then I need quarter hours, so-- or every 15 minutes, so I said divide by 4. Then I looked and said, well, I don't want to leave it like that.
To reduce the fraction, I need to multiply the top part of the fraction and the lower part by 24, so that would leave me with this in the denominator.
I didn't know how to do that multiplication in my head, so I simply highlighted it and hit the F9 key, which is evaluate, and boom.
I can leave that hard-coded in there. That is 15 minutes as a proportion of one 24-hour day. All right, now, when creating categories, if you look back over here, notice we're going to have a 12:15 and a 12:15 here. So, you have to be careful.
I mean, I just say I'm going to include the lower time but not the upper. That way I won't double count anywhere. So, I'm going to equals that cell, tab, to get the lower and then equal this one plus my increment and F4 to lock it. Now, I can copy that down and here I'm always going to go equals one cell up and over.
All right, so, those two formulas I can copy down and those will give me my categories.
Now, I could have just typed the times in and increment-- oops. Now, notice I went too far here. If I hit the delete key, that just deletes the content. Ctrl Z. So, what I really want to do is home, clear all-- that clears content and formatting. Keyboard shortcut in 2007 is Alt H E A Ctrl Z.
I still remember the old one and since both are three letters long keyboard, I still use the old one; Alt E A A. I actually have those listed up here. All right, so we have our categories. Now, again, when I do my counting over here, I'm going to include-- I’m going to say greater than or equal to 12 p.m., but less than 12:15.
All right, if you're in 2007 and 10, you can use COUNTIFS, criteria range, C, tab, comma, in double quotes, we're going to say greater than or equal to because we want to include the lower, end double quotes and then join, symbol ampersand there, okay? What's so nice about this function-- you have criteria range one, criteria one, comma, criteria range two, C tab, because we've named it, comma, and then I don't want to include the lower one, so I just put a less than symbol. That formula in 2007 will work all the way down. Double-click and send it down.
The defined names are also locked, which is helpful in this situation. All right, what about 2003 and before? Well, we're going to have to kind of back into it. I'm going to build a form that says count all of the ones that are less than 12:15. If I say less than, it won't include that one and I'm going to count all those and then subtract that-- COUNTIF again, but less than this one.
That's what-- when we get down here, we'll say, the logic will be count all the values less than 1:15, so that won't be included, minus all the ones less than 1 p.m. Because we're saying less than 1 p.m., the 1 p.m.
will be included in this category. So, it's two COUNTIFS and the criteria is going to be less than in both cases. I'm going to say, the bigger one count less than all of the bigger ones minus COUNTIF, if only I could type, down arrow tab-- if only I could type.
COUNTIF and then the range is C comma and it's still less than, remember, then we’ll say less than the lower but that'll include the lower here.
All right, so, double-click and send that down. So, there's 2003 and earlier, 2007 and later. All right, I’ll throw it back to MrExcel.
Bill: Alt E A A. I still do that. The Experimental Aircraft Association to clear all cells. I love the COUNTIFS with two different conditions on the same column. I've always done it where I look at column A is something and column B is something to replace the SUMPRODUCT, but that's a great use. It’s something every week with these.
Well, I want to thank everyone for stopping by. We'll see you next week for another Dueling Excel podcast from MrExcel and Excel is Fun.
