Today's Dueling Excel Podcast, with Mike 'ExcelisFun' Girvin and Bill 'MrExcel' Jelen, looks at finding the Minimum and Maximum between Two Dates for Each Region. Follow along with Mike and Bill, in Episode #1697, as they find individual solutions to reach the objective for the today's Duel.
Dueling Excel Podcast #120...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! 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 #120...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! 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: Hey welcome back, it's time for another Dueling Excel podcast. I'm Bill Jelen from MrExcel, I'll be joined by a Mike Girvin from ExcelIsFun, this is our episode 121 - MIN and MAX Between Two Dates for Each Region!
Alright, today's question sent in from YouTube: "I have to find the MIN and the MAX between two dates for each region. How can I do this?" Alright well, I'm going to do it using a DMIN and DMAX, so those require a criteria range, the criteria range has to be set up with headings at the top and the values just below. So, I'm going to build a criteria here of ">="& the lower date, and then also "<="& the greater date, and then we're just going to build our first region with Canada. Alright, and we'll get the formulas working. First, =DMIN at the database, that's this data over here, press F4, comma, which field, we can say field 3 because it's the 3rd field, and then the criteria range is this range up here, press F4. Alright, so between those dates, Canada, 6770, copy, paste, will edit this one and change to a DMAX. Alright, now the big hassle with DMIN DMAX is, it needs this big criteria range for each of these regions, but I'm going to beat the system here by using a data table.
So we select this whole range, and on the Data, Tab, What-If Analysis, Data Table. We're going to ignore the row input cell, we're going to leave that blank, and the column input cell, that's these items we're going to take one at a time, and put them into this cell in the criteria range, click OK. And there we go, there is our answer. Now this is going to look strange up here, so I'm going to beat the system. I'm going to do Ctrl+1, Custom, and in quotes I'm going to put my heading, so that says "Hey, no matter what number's there, just put the word MIN", and then here, Ctrl+1, Custom, and in quotes put the "number" MAX. There we go. I was wondering(?) about this corner cell here, I think it's OK, yeah we could put Region back there. I don't know, I was kind of super superstitious that we'd have to get rid of that, because in a data table 2, where you have both a row input and a column input, that cell has to have the formula. But in this particular type of table, we're already specifying the call, because it's OK to use that heading there.
Alright Mike, let's see what you have!
Mike: Thanks MrExcel! Wow, I love this DMIN, I love the way you lock the cell references, and then copy it over, and just change the "AX" here, absolutely beautiful. And then that custom number formatting and the data table, THAT is a lot easier than the way I'm going to do it. Now, if you didn't want to use DMIN DMAX criteria range up here, etc., we can do an array formula, right? But there's, how many conditions, there's 3, and there's no MAXIF or MINIF, so we have to do some sort of array formula. Now I'm going to look at a 2010 function, so AGGREGATE function, and then we'll look at the old MAX and MIN with IF IF IF! Three conditions will have to use 3 IFs. Let's look at the AGGREGATE, it came in Excel 2010, it's a great function, it can handle array calculations, and you don't have to do Ctrl+Shift+Enter. Now the first argument is function, so I'm just going to use the MAX and the MIN- oh, wait a second. The problem with AGGREGATE is, the first 13 functions cannot handle array calculations, it's only 14 to 19.
So what do we do if we have MAX? We use LARGE, and say "Hey, give me the first largest value!" And then SMALL, and we'll say "Hey, give me the first smallest value!" Alright now, to create a single format to(?) copy down and over, I put the number 14 and 15, 14's for LARGE, 15's for SMALL. So I'm going to click in that cell reference, and when I copy down I need it locked, but when I copy the formula over, I need that "dancing ants" to move to the 15. So I'm going to hit the F4 key 2 times, lock the row reference but not the column, comma, the second argument is options, and our array calculations going to have some divide-by-zero errors- Beautiful option, I'm going to say "Ignore error values". By the way, if you're not using an AGGREGATE, if you have something simple like the SUM function and you have some errors, just choose AGGREGATE and say ignore errors. Alright, so the option, comma, and then there's our array. By the way, this argument has two ways we can use AGGREGATE. This bottom one is if you want to add, like using the SUM function, and then you just put references, right, and then option would be 6. Here we're using the array option, because we're using functions 14 and 19. Alright so we have some numbers here in three conditions, so I'm going to first highlight all the numbers, Ctrl+Shift+Down arrow, F4.
Now we have MAX and MIN, and the MIN function, if we did normal Boolean multiplying of TRUEs and FALSEs in arrays, we get some zeroes, and the MIN function would pick up the 0.
So instead of multiplying all of our arrays of TRUEs and FALSEs, we're going to use division!
Now we're going to take all those numbers and divide by three conditions, so I'm going to use two parentheses. The first condition, I'm going to highlight the dates, Ctrl+Shift+Down arrow, F4. I'm going to say "Any of those dates >= to the lower limit" F4. Nonetheless that'll give me a bunch of TRUEs and FALSEs.
If I were to highlight this and hit the F9, and get the old famous "8192 characters, that's all we can display" error message, right, I can't show you all the TRUEs and FALSEs, but no problem. We're going to take that array calculation, right there we're doing a direct comparative operator calculation on an array of values, then take that array and multiply by our second condition. Ctrl+Shift+Down arrow, F4, <= to the upper value, F4. And then finally, the * by the region column anything in there equal to that particular region. Now, when we copy this way need it locked, but when we copy it down it needs to move relatively.
So I'm going to hit the F4 3 times, lock the column but not the row.
Alright now why do we have two parentheses right here? I'm going to close this off because we need this multiplying to calculate before the division, right? Alright, so that whole thing, TRUE TRUE TRUE times the value here, that'll pull this value out, so only when we have three TRUEs will it pull this value out. All of those values will be there, comma, and we need a k, 1. Ctrl+Enter, oh, what is so great about AGGREGATE? AGGREGATE will make that array calculation, I just did Ctrl+Enter, I don't see any curly brackets up there. Right, and copy it down and over! Now I can see, come to the last one and see sure enough, all the cell references are working, and the formula's calculating correctly. Now we did 14 and 15, if you couldn't have 14 and 15 in the cells, then we had to do two separate formulas. Let's go look at that over here, and we can see if we're doing MAX, guess what?
We can do straight multiplying all the way across for every single value and array of TRUEs and FALSEs, because 0 is not going to hurt our MAX calculation. If you're doing the MIN, there you go, we're going to use that division. Finally, if you had two 2007 or earlier, or you liked this construction here, you can say MAX with three IFs, three conditions so three IFs. Only when it gets TRUE TRUE TRUE will it pick out the value here, in that value of TRUE. All those values would be dumped into the MAX, and same for MIN Alright, throw back to MrExcel!
Bill: Hey alright Mike, that was good, I like the MAX IF IF IF, because it seems more straightforward to me. Although, I have to give you credit, the AGGREGATE MAX doesn't do the array formula, and say "Hey, let's just do large combo one!" What a great way to go.
Alright well hey, I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and ExcelIsFun!
Alright, today's question sent in from YouTube: "I have to find the MIN and the MAX between two dates for each region. How can I do this?" Alright well, I'm going to do it using a DMIN and DMAX, so those require a criteria range, the criteria range has to be set up with headings at the top and the values just below. So, I'm going to build a criteria here of ">="& the lower date, and then also "<="& the greater date, and then we're just going to build our first region with Canada. Alright, and we'll get the formulas working. First, =DMIN at the database, that's this data over here, press F4, comma, which field, we can say field 3 because it's the 3rd field, and then the criteria range is this range up here, press F4. Alright, so between those dates, Canada, 6770, copy, paste, will edit this one and change to a DMAX. Alright, now the big hassle with DMIN DMAX is, it needs this big criteria range for each of these regions, but I'm going to beat the system here by using a data table.
So we select this whole range, and on the Data, Tab, What-If Analysis, Data Table. We're going to ignore the row input cell, we're going to leave that blank, and the column input cell, that's these items we're going to take one at a time, and put them into this cell in the criteria range, click OK. And there we go, there is our answer. Now this is going to look strange up here, so I'm going to beat the system. I'm going to do Ctrl+1, Custom, and in quotes I'm going to put my heading, so that says "Hey, no matter what number's there, just put the word MIN", and then here, Ctrl+1, Custom, and in quotes put the "number" MAX. There we go. I was wondering(?) about this corner cell here, I think it's OK, yeah we could put Region back there. I don't know, I was kind of super superstitious that we'd have to get rid of that, because in a data table 2, where you have both a row input and a column input, that cell has to have the formula. But in this particular type of table, we're already specifying the call, because it's OK to use that heading there.
Alright Mike, let's see what you have!
Mike: Thanks MrExcel! Wow, I love this DMIN, I love the way you lock the cell references, and then copy it over, and just change the "AX" here, absolutely beautiful. And then that custom number formatting and the data table, THAT is a lot easier than the way I'm going to do it. Now, if you didn't want to use DMIN DMAX criteria range up here, etc., we can do an array formula, right? But there's, how many conditions, there's 3, and there's no MAXIF or MINIF, so we have to do some sort of array formula. Now I'm going to look at a 2010 function, so AGGREGATE function, and then we'll look at the old MAX and MIN with IF IF IF! Three conditions will have to use 3 IFs. Let's look at the AGGREGATE, it came in Excel 2010, it's a great function, it can handle array calculations, and you don't have to do Ctrl+Shift+Enter. Now the first argument is function, so I'm just going to use the MAX and the MIN- oh, wait a second. The problem with AGGREGATE is, the first 13 functions cannot handle array calculations, it's only 14 to 19.
So what do we do if we have MAX? We use LARGE, and say "Hey, give me the first largest value!" And then SMALL, and we'll say "Hey, give me the first smallest value!" Alright now, to create a single format to(?) copy down and over, I put the number 14 and 15, 14's for LARGE, 15's for SMALL. So I'm going to click in that cell reference, and when I copy down I need it locked, but when I copy the formula over, I need that "dancing ants" to move to the 15. So I'm going to hit the F4 key 2 times, lock the row reference but not the column, comma, the second argument is options, and our array calculations going to have some divide-by-zero errors- Beautiful option, I'm going to say "Ignore error values". By the way, if you're not using an AGGREGATE, if you have something simple like the SUM function and you have some errors, just choose AGGREGATE and say ignore errors. Alright, so the option, comma, and then there's our array. By the way, this argument has two ways we can use AGGREGATE. This bottom one is if you want to add, like using the SUM function, and then you just put references, right, and then option would be 6. Here we're using the array option, because we're using functions 14 and 19. Alright so we have some numbers here in three conditions, so I'm going to first highlight all the numbers, Ctrl+Shift+Down arrow, F4.
Now we have MAX and MIN, and the MIN function, if we did normal Boolean multiplying of TRUEs and FALSEs in arrays, we get some zeroes, and the MIN function would pick up the 0.
So instead of multiplying all of our arrays of TRUEs and FALSEs, we're going to use division!
Now we're going to take all those numbers and divide by three conditions, so I'm going to use two parentheses. The first condition, I'm going to highlight the dates, Ctrl+Shift+Down arrow, F4. I'm going to say "Any of those dates >= to the lower limit" F4. Nonetheless that'll give me a bunch of TRUEs and FALSEs.
If I were to highlight this and hit the F9, and get the old famous "8192 characters, that's all we can display" error message, right, I can't show you all the TRUEs and FALSEs, but no problem. We're going to take that array calculation, right there we're doing a direct comparative operator calculation on an array of values, then take that array and multiply by our second condition. Ctrl+Shift+Down arrow, F4, <= to the upper value, F4. And then finally, the * by the region column anything in there equal to that particular region. Now, when we copy this way need it locked, but when we copy it down it needs to move relatively.
So I'm going to hit the F4 3 times, lock the column but not the row.
Alright now why do we have two parentheses right here? I'm going to close this off because we need this multiplying to calculate before the division, right? Alright, so that whole thing, TRUE TRUE TRUE times the value here, that'll pull this value out, so only when we have three TRUEs will it pull this value out. All of those values will be there, comma, and we need a k, 1. Ctrl+Enter, oh, what is so great about AGGREGATE? AGGREGATE will make that array calculation, I just did Ctrl+Enter, I don't see any curly brackets up there. Right, and copy it down and over! Now I can see, come to the last one and see sure enough, all the cell references are working, and the formula's calculating correctly. Now we did 14 and 15, if you couldn't have 14 and 15 in the cells, then we had to do two separate formulas. Let's go look at that over here, and we can see if we're doing MAX, guess what?
We can do straight multiplying all the way across for every single value and array of TRUEs and FALSEs, because 0 is not going to hurt our MAX calculation. If you're doing the MIN, there you go, we're going to use that division. Finally, if you had two 2007 or earlier, or you liked this construction here, you can say MAX with three IFs, three conditions so three IFs. Only when it gets TRUE TRUE TRUE will it pick out the value here, in that value of TRUE. All those values would be dumped into the MAX, and same for MIN Alright, throw back to MrExcel!
Bill: Hey alright Mike, that was good, I like the MAX IF IF IF, because it seems more straightforward to me. Although, I have to give you credit, the AGGREGATE MAX doesn't do the array formula, and say "Hey, let's just do large combo one!" What a great way to go.
Alright well hey, I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and ExcelIsFun!
