This is a follow up to yesterday's Duel 176 - with four new ways to solve the problem: Pivot table, AGGREGATE, Array Formula, MINIFS.
Transcript of the video:
Bill: Hey, welcome back 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 176.5 - Start Time and End Time, Part 2!
Alright so it was just yesterday, we put up this Dueling Excel podcast, where we had a situation where on each day, so here's January 7th, we started out with 0s, and then there's an event that happens.
We have these non-zero events, and then it goes back to 0.
We want to know the earliest time and the latest time for those events.
We've got a lot of great comments on YouTube, Bill suggested "Hey, this could be done with a Pivot table!" Alright, so we'll create a pivot table here on an existing worksheet, Click OK, down the left hand side, I want to see the various dates, and then I'm going to take the Time, and put it in the Values area twice, and it shows up as a Count of Time, I'm going to go to the first one, Pivot Table Tools, Analyze, Field Settings, and say that should be a MIN, we'll call it Start, click OK.
And come to this one, Analyze, Field Settings, and say that should be a MAX, and we'll call that End.
Click OK.
Now, a couple of problems, first off, we need to format these times, so CTRL+1, go to the time field, and choose an appropriate time, click OK.
But see, now this still is not the non-zero event, what it is, it's capturing the first and last for that particular day, day but we want to throw out the non-zeros.
Alright, and this is really strange, I was fairly sure that I could come here to the value group, go to Value Filters and say "does not equal 0", but that is not happening, right, that's not what I want to have - happen.
So instead we're going to take the value field, and move it to the filters area like that.
I'll open the filters area, select multiple items, and uncheck zero, that would make sure that I have all of the values except for the zero values, and we then have the correct answers, 17:48 to 17:51!
And the report, they actually wanted this descending, that's easy enough to do, sort Newest to Oldest, and we have the most recent at the top.
Now as Bill pointed out, this is not dynamic, it's just a single snapshot, if you get more data tomorrow, you're going to have to refresh the pivot table, AND probably update this filter here.
But certainly a good way to go.
All right, Mike, let me throw it over to you!
Mike: Thanks MrExcel and Bill Sizis(?)!
Yes, of course, using the MIN and MAX, that is a great idea!
Now last time, I had to look up the last and look up the first, but I'm going to go off Bill Sizis'(?) Pivot table, and do MIN and MAX.
And I'm actually going to use the AGGREGATE function, this is a new function in 2010 Excel or later, and the great thing is, this first argument has a bunch of functions, including LARGE, which is 14, and SMALL, which is 15.
Now, 14 to 19 allow us, to do array operations, which I am going to do here.
So guess what!
I only want to do one formula, so I put 15 for SMALL, 14 for LARGE there, and I need to lock this going down, but not to the side as I copy that formula, so I'm going to hit the F4, 1, 2, $ in front of the two, but not the G, comma, and I'm going to do an array calculation, with ERRORS as filters, so i'm going to say Ignore Errors in the array.
What we need to do is, we need to isolate from the date column only the particular dates, and then from the value columns, we need to isolate non-zero, and then MIN and MAX can choose from those values there.
So first we save the values, CTRL+Shift+Down then F4, and we're going to divide by two conditions: (( , the first condition is: Hey, here's the date column, CTRL+Shift+Down then F4, are you equal to this date?
Now when we copy the formula over to the side, I need it locked, but when I copy down, I need it to move relatively, so I'm going to hit the F4 three times.
Look at that F4 cell reference, and I use the F4 key, ) , that's the first condition's TRUEs and FALSEs, we will multiply TIMES the entire value column, CTRL+Shift+Down then F4, and we need to ask the question: "Are any of you greater than zero?" )) ! Anytime there's a TRUE here and a TRUE here, It'll be TRUE*TRUE = 1 ! And the entire denominator will filter out, and give us only the MIN or MAX values we want.
Now let's highlight this entire array, and F9 to Evaluate, and sure enough, the Divide by Zero error is our filter, there are the values that will be dumped into the MIN function!
CTRL+Z!
Now I simply ,1 , and it will be one for either the MIN or the MAX, ) , CTRL+Enter!
Copy it over and then double click it and send it down!
Go to the last cell F2, looking good!
Alright, throw back to MrExcel!
Bill: Hey, alright, aggregate, it's like subtotal but it has extra functions, including MAX and MIN.
That's a beautiful way to go.
Here's another idea from Michael P, Michael said "Well we do this all the time at work." And he threw out this formula, alright, here's what we do: The first thing we do is we check to see, if all of the times in column C are equal to this time.
Right, if that's true, then, check to see if the value in E is greater than zero, if it is, then give us the corresponding value from the time column, otherwise, "", and also if it's not the right date, then "" ! You can imagine, that this is going to give us an array, of just the times that match this date with a non-zero value.
Throw that whole thing into the MIN function, because this is an array formula we, have to do CTRL+Shift+Enter, like that, then over here, the exact same function, MAX!
That is awesome!
Literally, after I record the first part of the duel, is when the new version of Excel from office 365 came out, offering us the brand-new MINIFS!
MINIFS says "Hey, we're going to look through all of the time here in column D." F4!
See a couple of criteria: first criteria is look through all of the dates over in column C, F4, and see if it's equal to this date, F4 three times, to lock it down to just column G, and then the next criteria, is look through all of these values in column E, F4, and it has to be greater than zero.
So I put that criteria in quotes like that.
No CTRL+Shift+Enter, just simply press Enter, you see we get the exact same answer as before, copy this over here, and change it to the other new function, MAXIFS!
And double click to copy that down, exact same answers as the array formula!
Hey, alright, what a great 2-day, 4-5-parting duel.
With a lot of different ways to solve this problem.
Thanks for stopping by, we'll see you next time 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 176.5 - Start Time and End Time, Part 2!
Alright so it was just yesterday, we put up this Dueling Excel podcast, where we had a situation where on each day, so here's January 7th, we started out with 0s, and then there's an event that happens.
We have these non-zero events, and then it goes back to 0.
We want to know the earliest time and the latest time for those events.
We've got a lot of great comments on YouTube, Bill suggested "Hey, this could be done with a Pivot table!" Alright, so we'll create a pivot table here on an existing worksheet, Click OK, down the left hand side, I want to see the various dates, and then I'm going to take the Time, and put it in the Values area twice, and it shows up as a Count of Time, I'm going to go to the first one, Pivot Table Tools, Analyze, Field Settings, and say that should be a MIN, we'll call it Start, click OK.
And come to this one, Analyze, Field Settings, and say that should be a MAX, and we'll call that End.
Click OK.
Now, a couple of problems, first off, we need to format these times, so CTRL+1, go to the time field, and choose an appropriate time, click OK.
But see, now this still is not the non-zero event, what it is, it's capturing the first and last for that particular day, day but we want to throw out the non-zeros.
Alright, and this is really strange, I was fairly sure that I could come here to the value group, go to Value Filters and say "does not equal 0", but that is not happening, right, that's not what I want to have - happen.
So instead we're going to take the value field, and move it to the filters area like that.
I'll open the filters area, select multiple items, and uncheck zero, that would make sure that I have all of the values except for the zero values, and we then have the correct answers, 17:48 to 17:51!
And the report, they actually wanted this descending, that's easy enough to do, sort Newest to Oldest, and we have the most recent at the top.
Now as Bill pointed out, this is not dynamic, it's just a single snapshot, if you get more data tomorrow, you're going to have to refresh the pivot table, AND probably update this filter here.
But certainly a good way to go.
All right, Mike, let me throw it over to you!
Mike: Thanks MrExcel and Bill Sizis(?)!
Yes, of course, using the MIN and MAX, that is a great idea!
Now last time, I had to look up the last and look up the first, but I'm going to go off Bill Sizis'(?) Pivot table, and do MIN and MAX.
And I'm actually going to use the AGGREGATE function, this is a new function in 2010 Excel or later, and the great thing is, this first argument has a bunch of functions, including LARGE, which is 14, and SMALL, which is 15.
Now, 14 to 19 allow us, to do array operations, which I am going to do here.
So guess what!
I only want to do one formula, so I put 15 for SMALL, 14 for LARGE there, and I need to lock this going down, but not to the side as I copy that formula, so I'm going to hit the F4, 1, 2, $ in front of the two, but not the G, comma, and I'm going to do an array calculation, with ERRORS as filters, so i'm going to say Ignore Errors in the array.
What we need to do is, we need to isolate from the date column only the particular dates, and then from the value columns, we need to isolate non-zero, and then MIN and MAX can choose from those values there.
So first we save the values, CTRL+Shift+Down then F4, and we're going to divide by two conditions: (( , the first condition is: Hey, here's the date column, CTRL+Shift+Down then F4, are you equal to this date?
Now when we copy the formula over to the side, I need it locked, but when I copy down, I need it to move relatively, so I'm going to hit the F4 three times.
Look at that F4 cell reference, and I use the F4 key, ) , that's the first condition's TRUEs and FALSEs, we will multiply TIMES the entire value column, CTRL+Shift+Down then F4, and we need to ask the question: "Are any of you greater than zero?" )) ! Anytime there's a TRUE here and a TRUE here, It'll be TRUE*TRUE = 1 ! And the entire denominator will filter out, and give us only the MIN or MAX values we want.
Now let's highlight this entire array, and F9 to Evaluate, and sure enough, the Divide by Zero error is our filter, there are the values that will be dumped into the MIN function!
CTRL+Z!
Now I simply ,1 , and it will be one for either the MIN or the MAX, ) , CTRL+Enter!
Copy it over and then double click it and send it down!
Go to the last cell F2, looking good!
Alright, throw back to MrExcel!
Bill: Hey, alright, aggregate, it's like subtotal but it has extra functions, including MAX and MIN.
That's a beautiful way to go.
Here's another idea from Michael P, Michael said "Well we do this all the time at work." And he threw out this formula, alright, here's what we do: The first thing we do is we check to see, if all of the times in column C are equal to this time.
Right, if that's true, then, check to see if the value in E is greater than zero, if it is, then give us the corresponding value from the time column, otherwise, "", and also if it's not the right date, then "" ! You can imagine, that this is going to give us an array, of just the times that match this date with a non-zero value.
Throw that whole thing into the MIN function, because this is an array formula we, have to do CTRL+Shift+Enter, like that, then over here, the exact same function, MAX!
That is awesome!
Literally, after I record the first part of the duel, is when the new version of Excel from office 365 came out, offering us the brand-new MINIFS!
MINIFS says "Hey, we're going to look through all of the time here in column D." F4!
See a couple of criteria: first criteria is look through all of the dates over in column C, F4, and see if it's equal to this date, F4 three times, to lock it down to just column G, and then the next criteria, is look through all of these values in column E, F4, and it has to be greater than zero.
So I put that criteria in quotes like that.
No CTRL+Shift+Enter, just simply press Enter, you see we get the exact same answer as before, copy this over here, and change it to the other new function, MAXIFS!
And double click to copy that down, exact same answers as the array formula!
Hey, alright, what a great 2-day, 4-5-parting duel.
With a lot of different ways to solve this problem.
Thanks for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and Excel Is Fun!
