Another Dueling Excel Episode with Mike 'ExcelisFun' Girvin and Bill 'MrExcel' Jelen! Today, a really confusing title with a simple objective: "1234Burchy" wants to Find the Slowest time that is 'within' Two (2) Seconds of the Fastest Time. Bill reaches back to a Function =DMIN, which he hasn't used in years... Mike opts for a twist to Bill's set up and takes a practical look at the =SMALL Function. Follow along with Episode #1636 to see how Mike and Bill arrive at a Duel-Solution to this quest.
Dueling Excel Podcast #110...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel Podcast #110...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Bill Jelen: 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 episode 111: Slowest Time within 2 Seconds of the Fastest Time.
Okay, 1234burchy sends this question, what is the slowest time that's within two seconds of the fastest time by sector?
This is racing information so, whoever had the fastest time, we want to find the slowest time.
All right so, here's what I'm going to do -- I'm going to go way back to a function I haven't used since the mid-nineties, of the old database functions and that’s for: =DMIN.
All right with the database functions we have to specify the database, that's the whole thing including the headings, so I’ll press F4 there, comma -- which field?
I think they're numbered 1-2 so this is the second field and then the criteria.
The criteria range has to have at least one of the headings, so we want from sector, sector one and that should give us the fastest time; the minimum time for each sector, right?
Okay, good, that's working – now, I'm just going to take the sector that I entered over there in E and I'm going to copy it over; why am I copying it over?
Because this range, this range right here is about to become another criteria range and so we want to find times that are within two seconds of the fastest, so I'm going to build on the fly – criteria range says it's less than, maybe even less than or equal to ampersand (&), this number plus two.
Alright see that yellow range now says -- hey in the sector column we want sector one, in the numbers column we're looking for less than or equal to 25.5 499, and then here I want =DMAX the same database -- comma, the field is the second field, comma, the criteria is now this for range area and we have it.
Okay and if we change this, sector two, sector three, all right everything's good -- but the problem with these database functions is, you need these kludgy criteria ranges here to get the answer.
Check this out -- I'm going to thank professor Simon Benninga for giving me this idea, we are going to build a table that returns my answer; I want to select this entire range back on the Data tab, What-If Analysis, Data Table.
I'm only going to use the column input cell and say that it is pointing to right there, so usually in a data table we have something up there in that top left-hand corner, not in this case -- click OK and we have all three answers.
There you go, Mike let's see what you have.
Mike Girvin: MrExcel, now I know why my life has been so less well-off over the last nine months, because I have been doing duels with you.
This is just awesome, two D-functions and then a data table?
And what's so cool about this is -- these D-functions are great but their limitation as you mentioned, that criteria argument needs this set up and you can't copy it down a formula, but these two D’s and a data table, table what an ingenious solution.
Ah man, all right I’m going to go over to this sheet and I still want to try and build a formula that I can put in the cell and copy down, right?
Now, the first thing is where I need to calculate the MIN for each sector -- so I’m simply going to use: =MINIF.
Oh wait a second, there's no MINIF function right?
There’s COUNTIF, SUMIF and all that but no MINIF.
No problem we could use the MIN function with an IF and use Control-Shift-Enter but in 2010 there's a great new function called AGGREGATE.
Now I’ll show you the MINIF in just a moment but, let's check out this new function AGGREGATE.
Now it’s great there's 19 functions and guess what?
One of them is MIN, so I’m simply going to select this 5, but wait a second, no that won't work either know why?
Because functions 12 to 13, MODE.SNGL and above, they can't handle array calculations and we have an array calculation because we don't want all the numbers, we want just the numbers from this column where it's equal to the sector right?
So we can't use that one.
Luckily, functions 14 to 19 can handle array calculations and we can simply use SMALL, number 15.
Now SMALL for number 15 will just, when we get to the K, will also give us the first smallest which is the same as MIN.
Now comma, our array calculation is going to have some errors for the, so for the second argument here, options we want -- number 6, comma, now array.
The trick to the array is if you have some numbers, you put the numbers in and then divide by the criteria -- so the numbers, Ctrl-Shift-Down on F4, those are going to be our numbers and then we divide -- our condition is anything equal to sector one so I’m going to put open parenthesis, I’ll write the column F4 equal to relative cell reference.
The parentheses are important because this equal sign calculates in Excel’s order of operations way at the bottom after division so we want to force that equal sign first.
Now check this out, if I highlight this condition right here and hit F9 of course it's going to give us TRUE’s and FALSE’s.
So now we're going to have numbers divided by TRUE which will be 1, and numbers divided by FALSE which is 0, and divided b0 will give us a divide-by-zero error, Ctrl-Z.
Now I'm going to click on this screen here, boop and then highlight that whole argument and F9 –notice divided by 0, what a cool way to isolate only the numbers we want so the divide-by-zero in essence gets rid of all the numbers that don't match our criteria, and that 6 absolutely will ignore those divide-by-zeros.
Ctrl-Z, comma 1, 4K that gives us the smallest and watch this -- Ctrl-Enter I didn't use Ctrl-Shift-Enter, I don't see any curly brackets up there and I copy it down.
There are our MIN values for each one of these sectors.
Now, we have two conditions -- now we need to find the biggest value or the slowest time that's within two seconds of the fastest so now we have to re-look through here and say is anything less than or equal to that plus two-seconds and then the second condition will have is, is it in the sector.
All right, guess what?
I’m using the AGGREGATE again; not 15 for SMALL but 14 for LARGE, comma, 6 to ignore the divide-by-zeros, the numbers; remember the numbers come first, Ctrl-Shift-Down on F4 divided by -- now we have two conditions so putting two parentheses, the first one, Ctrl-Shift-Down on F4; anything in there equal to our sector.
And I’ll use Boolean multiplying -- I've got a bunch of TRUE’s and FALSE’s, I’m going to multiply by a bunch of other TRUE’s and FALSE’s; these TRUE’s and FALSE’s will be from the numbers and I’m going to say -- If those numbers are less than or equal to the MIN we've already calculated, plus and I put the two seconds I'm going to, right arrow, right there and F4.
So now our criteria is anything in here is less than or equal to, 2 seconds above the slowest.
All right that's our second condition, close parentheses twice guess what if I highlight this because we're doing Boolean multiplying and won't give me TRUE’s and FALSE’s, it'll give me ones and zeros.
Anytime you do a math operation on, I'm going to hit F9, TRUE’s and FALSE’s it gets into ones and zeros.
So those ones represent where we got both conditions met Ctrl-Z.
Now I'm going to click on this argument F9 -- absolutely beautiful, in essence we're using division and divide-by-zero to filter out all the numbers we don't want or that don't meet our two conditions.
Ctrl-Z -- I'm back to this K, comma, 1 for the biggest, the MAX and Ctrl-Enter, no curly brackets, and copy it down.
So there we have our values – now, what would you do if you didn't have 2010?
Well go out and buy it because AGGREGATE is so cool, no, no.
You simply use MIN and the IF; notice the condition comes in the logical test and then we dump the numbers in based on TRUE’s and FALSE’s into the MIN.
Over here we're using MAX, we have 2 IFs first, second set of TRUE’s and FALSE’s then dump these numbers into the MAX.
All right I'm going to throw it back MrExcel.
Bill Jelen: Mike, my heart skipped a beat when you start talking about MINIF -- it's like what?
Huh, oh no we're going to use AGGREGATE.
That was a great array formula, I love the fact you can just copy that down.
Reminding me I need to come out and pre-order your book Ctrl-Shift-Enter, publication date June 1st.
I can't wait for that to get here, great, great solution.
All right, I want to thank everyone 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 episode 111: Slowest Time within 2 Seconds of the Fastest Time.
Okay, 1234burchy sends this question, what is the slowest time that's within two seconds of the fastest time by sector?
This is racing information so, whoever had the fastest time, we want to find the slowest time.
All right so, here's what I'm going to do -- I'm going to go way back to a function I haven't used since the mid-nineties, of the old database functions and that’s for: =DMIN.
All right with the database functions we have to specify the database, that's the whole thing including the headings, so I’ll press F4 there, comma -- which field?
I think they're numbered 1-2 so this is the second field and then the criteria.
The criteria range has to have at least one of the headings, so we want from sector, sector one and that should give us the fastest time; the minimum time for each sector, right?
Okay, good, that's working – now, I'm just going to take the sector that I entered over there in E and I'm going to copy it over; why am I copying it over?
Because this range, this range right here is about to become another criteria range and so we want to find times that are within two seconds of the fastest, so I'm going to build on the fly – criteria range says it's less than, maybe even less than or equal to ampersand (&), this number plus two.
Alright see that yellow range now says -- hey in the sector column we want sector one, in the numbers column we're looking for less than or equal to 25.5 499, and then here I want =DMAX the same database -- comma, the field is the second field, comma, the criteria is now this for range area and we have it.
Okay and if we change this, sector two, sector three, all right everything's good -- but the problem with these database functions is, you need these kludgy criteria ranges here to get the answer.
Check this out -- I'm going to thank professor Simon Benninga for giving me this idea, we are going to build a table that returns my answer; I want to select this entire range back on the Data tab, What-If Analysis, Data Table.
I'm only going to use the column input cell and say that it is pointing to right there, so usually in a data table we have something up there in that top left-hand corner, not in this case -- click OK and we have all three answers.
There you go, Mike let's see what you have.
Mike Girvin: MrExcel, now I know why my life has been so less well-off over the last nine months, because I have been doing duels with you.
This is just awesome, two D-functions and then a data table?
And what's so cool about this is -- these D-functions are great but their limitation as you mentioned, that criteria argument needs this set up and you can't copy it down a formula, but these two D’s and a data table, table what an ingenious solution.
Ah man, all right I’m going to go over to this sheet and I still want to try and build a formula that I can put in the cell and copy down, right?
Now, the first thing is where I need to calculate the MIN for each sector -- so I’m simply going to use: =MINIF.
Oh wait a second, there's no MINIF function right?
There’s COUNTIF, SUMIF and all that but no MINIF.
No problem we could use the MIN function with an IF and use Control-Shift-Enter but in 2010 there's a great new function called AGGREGATE.
Now I’ll show you the MINIF in just a moment but, let's check out this new function AGGREGATE.
Now it’s great there's 19 functions and guess what?
One of them is MIN, so I’m simply going to select this 5, but wait a second, no that won't work either know why?
Because functions 12 to 13, MODE.SNGL and above, they can't handle array calculations and we have an array calculation because we don't want all the numbers, we want just the numbers from this column where it's equal to the sector right?
So we can't use that one.
Luckily, functions 14 to 19 can handle array calculations and we can simply use SMALL, number 15.
Now SMALL for number 15 will just, when we get to the K, will also give us the first smallest which is the same as MIN.
Now comma, our array calculation is going to have some errors for the, so for the second argument here, options we want -- number 6, comma, now array.
The trick to the array is if you have some numbers, you put the numbers in and then divide by the criteria -- so the numbers, Ctrl-Shift-Down on F4, those are going to be our numbers and then we divide -- our condition is anything equal to sector one so I’m going to put open parenthesis, I’ll write the column F4 equal to relative cell reference.
The parentheses are important because this equal sign calculates in Excel’s order of operations way at the bottom after division so we want to force that equal sign first.
Now check this out, if I highlight this condition right here and hit F9 of course it's going to give us TRUE’s and FALSE’s.
So now we're going to have numbers divided by TRUE which will be 1, and numbers divided by FALSE which is 0, and divided b0 will give us a divide-by-zero error, Ctrl-Z.
Now I'm going to click on this screen here, boop and then highlight that whole argument and F9 –notice divided by 0, what a cool way to isolate only the numbers we want so the divide-by-zero in essence gets rid of all the numbers that don't match our criteria, and that 6 absolutely will ignore those divide-by-zeros.
Ctrl-Z, comma 1, 4K that gives us the smallest and watch this -- Ctrl-Enter I didn't use Ctrl-Shift-Enter, I don't see any curly brackets up there and I copy it down.
There are our MIN values for each one of these sectors.
Now, we have two conditions -- now we need to find the biggest value or the slowest time that's within two seconds of the fastest so now we have to re-look through here and say is anything less than or equal to that plus two-seconds and then the second condition will have is, is it in the sector.
All right, guess what?
I’m using the AGGREGATE again; not 15 for SMALL but 14 for LARGE, comma, 6 to ignore the divide-by-zeros, the numbers; remember the numbers come first, Ctrl-Shift-Down on F4 divided by -- now we have two conditions so putting two parentheses, the first one, Ctrl-Shift-Down on F4; anything in there equal to our sector.
And I’ll use Boolean multiplying -- I've got a bunch of TRUE’s and FALSE’s, I’m going to multiply by a bunch of other TRUE’s and FALSE’s; these TRUE’s and FALSE’s will be from the numbers and I’m going to say -- If those numbers are less than or equal to the MIN we've already calculated, plus and I put the two seconds I'm going to, right arrow, right there and F4.
So now our criteria is anything in here is less than or equal to, 2 seconds above the slowest.
All right that's our second condition, close parentheses twice guess what if I highlight this because we're doing Boolean multiplying and won't give me TRUE’s and FALSE’s, it'll give me ones and zeros.
Anytime you do a math operation on, I'm going to hit F9, TRUE’s and FALSE’s it gets into ones and zeros.
So those ones represent where we got both conditions met Ctrl-Z.
Now I'm going to click on this argument F9 -- absolutely beautiful, in essence we're using division and divide-by-zero to filter out all the numbers we don't want or that don't meet our two conditions.
Ctrl-Z -- I'm back to this K, comma, 1 for the biggest, the MAX and Ctrl-Enter, no curly brackets, and copy it down.
So there we have our values – now, what would you do if you didn't have 2010?
Well go out and buy it because AGGREGATE is so cool, no, no.
You simply use MIN and the IF; notice the condition comes in the logical test and then we dump the numbers in based on TRUE’s and FALSE’s into the MIN.
Over here we're using MAX, we have 2 IFs first, second set of TRUE’s and FALSE’s then dump these numbers into the MAX.
All right I'm going to throw it back MrExcel.
Bill Jelen: Mike, my heart skipped a beat when you start talking about MINIF -- it's like what?
Huh, oh no we're going to use AGGREGATE.
That was a great array formula, I love the fact you can just copy that down.
Reminding me I need to come out and pre-order your book Ctrl-Shift-Enter, publication date June 1st.
I can't wait for that to get here, great, great solution.
All right, I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel Podcast from MrExcel and Excel Is Fun.
