A cineplex has 18 theatres that show movies. Given the schedule of when each movies begins and ends, how many movies are being shown at any given moment? Bill and Mike offer competing formulas.
Transcript of the video:
Alright, it's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, I’ll be joined by Mike Girvin from Excel Is Fun.
This is our episode 162.
How Many Movies are Showing At a Certain Time?
Hey Mike, this question emailed in to me.
I love movies, so I thought this one is really cool.
Let's say, you have the Megaplex down the street with, you know, 18 different screens.
And those movies start at a certain time and end at a certain time, based on how long the movie is.
But the next movie doesn't start right away, there's like a 20 minute gap between one showing and the next showing, in order to clean the theater and get people in and out.
So the question is: given the schedule over here, for any time period showed along this left hand side.
So, at 11:45: how many of those theaters are actually showing movies.
In theory, you might get 18 theater showing a movie all at the same time, but there's going to be a lots of times, when there's less than 18 theaters, because they're in that 20 minute gap or they haven't started showing the first movie yet.
All right, so to do this, I'm going to build a helper column, I'm going to choose any number initially from the left hand side, and I'm going to build a formula here, that says: is there a movie showing right now at 4:15.
Two things have to be true.
The first thing that has to be true is: the start time has to be less than or equal to our, let's call it a hurdle time, F4 (=IF(AND(B2<=$E$1,))).
And then the other thing that has to be true is: the end time has to be greater than or equal to that same time, up in cell A1, again press F4 to put the dollar signs in (,D2>=$E$1).
If both of those are true, then we have one movie showing, otherwise we have zero movie showing.
Double click to shoot that down and then right up here, above my table, I'm going to do =SUM of all of those helper cells (=SUM(E2:E84).
And so at 4:15 we have 16 movies showing.
At, let's say, 4:45 there's 15 movies showing.
So one movie got out or something like that.
Alright, now, you know, this number is only telling me the answer for one particular cell.
But we can use the What-If tools, the Data Table… So, I go to the DATA tab, What-If Analysis, Data Table… And normally we have a table with variables along the top and variables down the side, but in this case we only have variables down the side.
So we're only going to use the Column input cell.
It says: take all of these numbers, from the column down the side and plug them in, one at the time into cell E1.
And then report what number we get from that formula.
So we click OK and bam, that fast we have all of our results all the way down.
So, start out with 4 movies showing at 11:45, 10 movies at 12:15 and so on, all right.
And next week, when we get the new runtimes, we can fill in this calendar over here, the schedule over here and these numbers will automatically update.
Yeah, so we can possibly plan staffing or, you know, electricity or something, right?
You can see where this would be useful.
All right, Mike, let's see what you have.
Mike: Thanks, MrExcel.
Oh, you got to love the Data Table feature.
And check this out, up in the formula bar, those are curly brackets, which means it's an array formula, it's automatically created by the Data Table, I love it.
All right, I'm going to come over to this sheet here and I'm going to think of this column here, as the lower end and this is the upper end.
And for each time I have to say: are you, time, greater than or equal to the lower end, less than or equal to the upper.
I'm going to start off with COUNTIFS.
If you have 2007 or later, since as MrExcel pointed out, this is AND criteria, that's what the COUNTIFS and SUMIFS do, they do AND criteria.
So for the criteria_range: Ctrl+Shift+Down arrow, F4, comma (=COUNTIFS($V$2:$B$84,))and then the criteria.
Remember, I need to ask the question: are you, time, greater than or equal to.
So in double quotes: greater than or equal to, the equal sign always comes after the “greater than” symbol, in double quote (“<=”) and I have to join that using Shift+7 (&) to our time.
So, the “greater than” is asking the question: hey, are you, time, greater than the lower end, comma, criteria_range_2 - this will be the upper end, Ctrl+Shift+Down arrow, F4 (G3,$D$2:$D$84,), comma.
Now I have to ask the question: are you less than or equal to, end double quote, join, this time right here.
Notice that “less than” is pointing towards there, close parenthesis (“>=”&G3)), Ctrl+Enter, double click and send it down.
So that will give us our COUNTS for each time.
Now, if you don't have 2007, you need to go out and get 2013, that's what you need to do.
But in that case, there's no COUNTIF, so you'd have to use SUMPRODUCT.
Now, we're going to take those two arrays and multiply.
Remember, we have this column and this column here.
Ah, we're going to have to use double negative (--), because we're going to get a column of TRUEs and FALSEs, and really, SUMPRODUCT can’t understand TRUEs and FALSEs, so we're going to need to convert them to 1s and 0s with that double negative.
And now I'm going to ask the question, Ctrl+Shift+Down arrow, F4: are any of you less than or equal to, this time right here, close parenthesis (=SUMPRODUCT(--($B$2:$B$84<=G3)).
If I highlight this, right here, that's the inside piece, and hit the F9 key to evaluate, we get our TRUEs and FALSEs, right.
But SUMPRODUCT can't understand them, Ctrl+Z, now with the double negatives it’ll convert those to, F9, 1s and 0s, Ctrl+Z.
Now we do the same thing, we have the first array and we need to multiply it by a second array of TRUEs and FALSEs.
So we've got our double negative, open parentheses, column, Ctrl+Shift+Down arrow, F4.
And I'm going to ask the question: are any of you greater than or equal to, this time right here (,--($D$2:$D$84>=G3)).
Notice, like in our last one, the “greater than” symbol is pointing towards the time for the lower end and the “smaller than” symbol is pointing towards our time for the upper end.
Close parenthesis, the SUMPRODUCT right there, array times array, and it will give us our count.
Only when it sees one times one, will SUMPRODUCT, then ah… double click and send it down.
All right, throw it back to MrExcel.
Bill: Hey, alright, Mike, that is awesome.
Two different approaches.
I've always heard that the plural COUNTIFS is a lot faster than SUMPRODUCT, I don't know that we have enough data points here to actually test it out, but let's see.
Charles: Charles Williams here.
Now, let's see how those solutions stack up, using FastExcel.
Bill: So, alright.
So here we're going to use Charles Williams’ FastExcel.
First thing we do, is turn the Calculation Options to Manual.
And let's come back and first try the Data Table.
I know that the Data Table is horribly, horribly slow.
So we select this range here and FastExcel V3, we will Calc the Range: 0.2...
0.201 Milliseconds.
We'll try a few different, try… 0.1 and 0.093… So less than 0.2.
Let's try these two, though.
The COUNTIFS, the new, fast way to go.
Calc Range… 0.945… Calc Range… 1.483… and Calc Range… 2.262.
What's up with that?
It's getting slower every time.
And then SUMPRODUCT, the old, old way, which is allegedly a lot slower than COUNTIFS.
Calc Range… 1.346… Calc Range… 1.309… and 1.374.
So, actually, SUMPRODUCT is consistently coming out faster than COUNTIFS, isn't that crazy?
I like all the three methods though, they really seem to be about equivalent in calculation speed and… for this small data set anyway.
Interesting problem, the person who asked me this question, has been asking for a couple weeks, so hopefully one of these three will solve the problem.
Well, I want to thank everyone for stopping by, will see you next week for another Dueling Excel podcast from MrExcel and Excel is fun.
I'm Bill Jelen from MrExcel, I’ll be joined by Mike Girvin from Excel Is Fun.
This is our episode 162.
How Many Movies are Showing At a Certain Time?
Hey Mike, this question emailed in to me.
I love movies, so I thought this one is really cool.
Let's say, you have the Megaplex down the street with, you know, 18 different screens.
And those movies start at a certain time and end at a certain time, based on how long the movie is.
But the next movie doesn't start right away, there's like a 20 minute gap between one showing and the next showing, in order to clean the theater and get people in and out.
So the question is: given the schedule over here, for any time period showed along this left hand side.
So, at 11:45: how many of those theaters are actually showing movies.
In theory, you might get 18 theater showing a movie all at the same time, but there's going to be a lots of times, when there's less than 18 theaters, because they're in that 20 minute gap or they haven't started showing the first movie yet.
All right, so to do this, I'm going to build a helper column, I'm going to choose any number initially from the left hand side, and I'm going to build a formula here, that says: is there a movie showing right now at 4:15.
Two things have to be true.
The first thing that has to be true is: the start time has to be less than or equal to our, let's call it a hurdle time, F4 (=IF(AND(B2<=$E$1,))).
And then the other thing that has to be true is: the end time has to be greater than or equal to that same time, up in cell A1, again press F4 to put the dollar signs in (,D2>=$E$1).
If both of those are true, then we have one movie showing, otherwise we have zero movie showing.
Double click to shoot that down and then right up here, above my table, I'm going to do =SUM of all of those helper cells (=SUM(E2:E84).
And so at 4:15 we have 16 movies showing.
At, let's say, 4:45 there's 15 movies showing.
So one movie got out or something like that.
Alright, now, you know, this number is only telling me the answer for one particular cell.
But we can use the What-If tools, the Data Table… So, I go to the DATA tab, What-If Analysis, Data Table… And normally we have a table with variables along the top and variables down the side, but in this case we only have variables down the side.
So we're only going to use the Column input cell.
It says: take all of these numbers, from the column down the side and plug them in, one at the time into cell E1.
And then report what number we get from that formula.
So we click OK and bam, that fast we have all of our results all the way down.
So, start out with 4 movies showing at 11:45, 10 movies at 12:15 and so on, all right.
And next week, when we get the new runtimes, we can fill in this calendar over here, the schedule over here and these numbers will automatically update.
Yeah, so we can possibly plan staffing or, you know, electricity or something, right?
You can see where this would be useful.
All right, Mike, let's see what you have.
Mike: Thanks, MrExcel.
Oh, you got to love the Data Table feature.
And check this out, up in the formula bar, those are curly brackets, which means it's an array formula, it's automatically created by the Data Table, I love it.
All right, I'm going to come over to this sheet here and I'm going to think of this column here, as the lower end and this is the upper end.
And for each time I have to say: are you, time, greater than or equal to the lower end, less than or equal to the upper.
I'm going to start off with COUNTIFS.
If you have 2007 or later, since as MrExcel pointed out, this is AND criteria, that's what the COUNTIFS and SUMIFS do, they do AND criteria.
So for the criteria_range: Ctrl+Shift+Down arrow, F4, comma (=COUNTIFS($V$2:$B$84,))and then the criteria.
Remember, I need to ask the question: are you, time, greater than or equal to.
So in double quotes: greater than or equal to, the equal sign always comes after the “greater than” symbol, in double quote (“<=”) and I have to join that using Shift+7 (&) to our time.
So, the “greater than” is asking the question: hey, are you, time, greater than the lower end, comma, criteria_range_2 - this will be the upper end, Ctrl+Shift+Down arrow, F4 (G3,$D$2:$D$84,), comma.
Now I have to ask the question: are you less than or equal to, end double quote, join, this time right here.
Notice that “less than” is pointing towards there, close parenthesis (“>=”&G3)), Ctrl+Enter, double click and send it down.
So that will give us our COUNTS for each time.
Now, if you don't have 2007, you need to go out and get 2013, that's what you need to do.
But in that case, there's no COUNTIF, so you'd have to use SUMPRODUCT.
Now, we're going to take those two arrays and multiply.
Remember, we have this column and this column here.
Ah, we're going to have to use double negative (--), because we're going to get a column of TRUEs and FALSEs, and really, SUMPRODUCT can’t understand TRUEs and FALSEs, so we're going to need to convert them to 1s and 0s with that double negative.
And now I'm going to ask the question, Ctrl+Shift+Down arrow, F4: are any of you less than or equal to, this time right here, close parenthesis (=SUMPRODUCT(--($B$2:$B$84<=G3)).
If I highlight this, right here, that's the inside piece, and hit the F9 key to evaluate, we get our TRUEs and FALSEs, right.
But SUMPRODUCT can't understand them, Ctrl+Z, now with the double negatives it’ll convert those to, F9, 1s and 0s, Ctrl+Z.
Now we do the same thing, we have the first array and we need to multiply it by a second array of TRUEs and FALSEs.
So we've got our double negative, open parentheses, column, Ctrl+Shift+Down arrow, F4.
And I'm going to ask the question: are any of you greater than or equal to, this time right here (,--($D$2:$D$84>=G3)).
Notice, like in our last one, the “greater than” symbol is pointing towards the time for the lower end and the “smaller than” symbol is pointing towards our time for the upper end.
Close parenthesis, the SUMPRODUCT right there, array times array, and it will give us our count.
Only when it sees one times one, will SUMPRODUCT, then ah… double click and send it down.
All right, throw it back to MrExcel.
Bill: Hey, alright, Mike, that is awesome.
Two different approaches.
I've always heard that the plural COUNTIFS is a lot faster than SUMPRODUCT, I don't know that we have enough data points here to actually test it out, but let's see.
Charles: Charles Williams here.
Now, let's see how those solutions stack up, using FastExcel.
Bill: So, alright.
So here we're going to use Charles Williams’ FastExcel.
First thing we do, is turn the Calculation Options to Manual.
And let's come back and first try the Data Table.
I know that the Data Table is horribly, horribly slow.
So we select this range here and FastExcel V3, we will Calc the Range: 0.2...
0.201 Milliseconds.
We'll try a few different, try… 0.1 and 0.093… So less than 0.2.
Let's try these two, though.
The COUNTIFS, the new, fast way to go.
Calc Range… 0.945… Calc Range… 1.483… and Calc Range… 2.262.
What's up with that?
It's getting slower every time.
And then SUMPRODUCT, the old, old way, which is allegedly a lot slower than COUNTIFS.
Calc Range… 1.346… Calc Range… 1.309… and 1.374.
So, actually, SUMPRODUCT is consistently coming out faster than COUNTIFS, isn't that crazy?
I like all the three methods though, they really seem to be about equivalent in calculation speed and… for this small data set anyway.
Interesting problem, the person who asked me this question, has been asking for a couple weeks, so hopefully one of these three will solve the problem.
Well, I want to thank everyone for stopping by, will see you next week for another Dueling Excel podcast from MrExcel and Excel is fun.
