Bill: Hey, I’m Bill Jelen from Mr. Excel.com, I’ve got a cool Excel tip for you today.
Mike: Hey, this is Mike Gel Girvin at Excel is Fun in Youtube and I have a different way to do that.
Bill: Hey, welcome back to the Mr. Excel netcast, this is a Dueling Excel podcast, I’m Bill Jelen, Mr. Excel.com, Mike Girvin from Excel is Fun. Question of the day, came in, how do we count times that are greater than five minutes? So here’s some sample times you wanna count. How many of those are greater than five minutes, and before I jump in to that, let’s just talk about using the count-if function a little bit. Normally, you know, so we have some numbers here, the way to count it typically is to use equal-count-if, go look at this range, and see how many of those are equal to the number three. Three being the criteria, so, that shows us that there are one, two, three, four, five cells are equal to three. But it is possible to build somewhat complex criteria in here, you can put in quote, say everything that is greater than or equal to three and that will count all eight cells that are greater than three. So, we’re gonna talk today about building some sort of complex criteria. Couple of ways to go, one is to, say, equal-count-if, and we specify this range of cells over here and then I’m going to build a complex criteria where I used the greater than sign and then an ampersand and to get that time in there, I’m gonna used time function. Time, zero hours, five minutes and zero seconds and then another parenthesis to close the count-if, and we will see that there are in fact four cells that work. Now, if it’s a hassle to you to use the time function, you could just come over here to the left and put in the actual time, so we have 12:05 am, which is for added show is five minutes and then use a formula here that says we’re gonna use the greater than sign and can cut that with the value of D7 and that work. And the beautiful thing here is that we were to change the criteria to be, may be 00300,it will do a different calculation, 04, 00, different calculation, and so on. The third method though is the one that seems just the easiest, to me, is to do equal, count-if, we’ll look at our range over here and then we’re gonna build a criteria in quotes, we say greater than 0:05:00. So we’re actually formatting the time to how it looks back here, in Excel takes a look at that and understands what we’re trying to do and sure enough comes up with the answer of four. So three different ways to solve this, and we’re gonna turn it over to Mike, and have Mike take a look at different ways he can come up with formulas to solve the same problem.
Mike: Thanks Mr. Excel, hey, those were three amazing methods. Look at that one, the time, that’s a great one. This one right here, very good because it’s linked to the cell. Ah, and this one, wow, with the time built right in. Now, lots of people like to hard code like this into the cell and if it’s always five minutes, that certainly fine to do. We’ll do variation on that, let’s think about how many hours there are in one day, there’s 24. How many minutes? 60. So total minutes in a day will be the product of those two, and we have five minutes, our number of minutes that we’re interested in is five minutes. Now, what is time anyway? Right, if we type 8 am, eight, colon, zero, zero, space A-M, if we control one and look at the format. Time, right, so time looks something that, but if we get rid of, remember we typed it in this way, that’s how we typed it in, but if we get rid of all the formatting with the general, we can see that it’s a proportion of one 24 hour day. So we can actually build our formula, five divided by 1440, so we could do, equals, count-if, and get that range right there, comma, and then in quote, greater than N, double quote, shift 7 for ampersand, and then, five divided by 140, close parenthesis. And that one will work. Another variation if and for some times, when you’re dealing with time, like 8 hours, the fractional equivalent is really easy, it’s just equals one divided by 3, right? There’s 1/8th out of 3 in 24 hours. So let’s see if we can figure out, we’re always dealing with five minutes, maybe we can see how many five minutes if we can reduce this fraction. So, how about just, we’re interested in five, so I’m gonna take the 1440 and divide that by the 5, and sure enough we get 288. So, probably an even better formula, if we were doing this all the time and we like to do it this way is just 1 divided by 288. Now, really, in this case, maybe you wanna use one of these others, but if you’re dealing with time like 8 hours or something and there’s a simple fractional equivalent, then go ahead and use that. But this one will work, 1 divided by 288, alright? We’ll see you next trick.
Bill: Hey, well alright, well how about that, next time someone should use something you’re gonna say that, be there in five minutes, you’re gonna say, hey, I’m gonna be there in 1/288th of a day. Alright, in behalf of Mike, myself, I wanna thank you for stopping by, see you next time for another Dueling Excel podcast.
Transcription by:
Scribe4you Transcription Services