Calculate the MEDIAN of the last 10 entries in a column. Bill uses OFFSET with COUNT. Mike uses an array formula from his upcoming Ctrl + Shift + Enter book to find the last 10 non-blank cells. To pre-order Mike's book: Amazon.com: Ctrl+Shift+Enter Mastering Excel Array Formulas: Do the Impossible with Excel Formulas Thanks to Array Formula Magic (0783324907186): Mike Excelisfun Girvin: Books
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 ExcelIsFun, this is our episode 129 - Median of The Last 10 Cells!
Alright, so today's question sent in by digiderek123 at YouTube, "if there's a way to use Excel to calculate our rolling median where, as new entries are added to the column, Excel will calculate the median for the 10 most recent entries (ignoring all previous entries in the column)." So here we want to get the last 10. I'm watching the name box up there, above A1, 10 rows * 1 column, we want to get a median of that. But he doesn't want to have to manually select the last 10 entries, each time a new entry is put in the column. So to me, that is a perfect use for OFFSET! OFFSET's going to need to calculate where the start is. So how many numeric entries, I'm going to use COUNT, not COUNTA, we're going to ignore the heading. And of course, my method is going to assume that there are no blanks, you can't skip any sales, you're just going to enter numeric entries all the way down. Where does entry #1 start? Alright, so we have 13 entries, means the last one's in row 14, and the first one is in row 5. So the formula here is = 13-8!
But there's a funny thing with OFFSET, we can't specify where something starts, we have to say how many rows it is below an actual cell. So I can start from F1, I can't start from F0. So I'm going to change this question: Where does entry 1 start in relation to F1?
And that is going to have to be H2-9. So that way from F1, we're going to go down 4 cells.
Alright, so to build the OFFSET, I'll call this "Median of Last 10". I always build the OFFSET first, =OFFSET(F1 , and we'll lock that with the F4 key, comma, how many rows down, it is the answer from there, comma, how many columns over, 0 columns over, how many rows tall, 10 rows tall, how many columns wide, 1 column wide. Alright, so there's our formula up there, =OFFSET, OFFSET is going to return 10 cells. So we have to put it in a wrapper function, and luckily for us, the wrapper function that we need is MEDIAN. Easy to do, so right there =MEDIAN, close(?) the whole thing, 2580, let's add a really small cell here, and 2474. Let's test =MEDIAN of these last 10. This is what Derek did not want to have to do, but we'd get the exact same answer. Let's see, delete here, delete that last entry, and edit this just by grabbing that blue outline, not in a corner, press Enter, and 2580, it looks like it's working.
Alright, now did we need these extra helper cells here, certainly not! So that was count of F and -9. So right here where we had pointed to H5, we could say =COUNT of F:F-9, and there we go. As we enter more items, let's just enter a 1000 here in 4 new cells, and it calculates based on the last ten. How am I changing the yellow boxes there, that's Alt O D, Conditional Formatting, Edit Rule, I said "Use a formula determine which cells to format." ROW of F2 >= COUNT(F) -8. Why 8 here and 9 here, because this had to be in relation to F1, this was just using row numbers, so it will highlight for me which ones are included in the MEDIAN.
Alright Mike, let's see what you have!
Mike: Thanks MrExcel! Hey, OFFSET function to create a dynamic range for the last 10 values entered in a column, absolutely beautiful!
Now the MEDIAN then takes these the OFFSET, spits out a range of values, F9, and what does the MEDIAN do? It actually takes the list, sorts them, if there's an odd number, it takes the average of the middle two. If you look down here 1607, that's exactly what that does. Now why is the OFFSET such a beautiful solution? Let's go over here. And the funny thing is, in chapter 13 of my upcoming array formula book, I use that, I do this exact example, but I show you how to create a dynamic range with the INDEX function. And it was kind of silly of me not to show OFFSET, because OFFSET is much easier, the actual formula is much shorter, easier to type out.
So why would you ever use INDEX to create a dynamic range? Well, OFFSET is volatile, and for large data sets volatile just means it recalculates often, and if you have a large data set, it slows down calculation. So if you want to create a dynamic range with the INDEX, here's how you can do it: Now INDEX, normally you give it an array of values, and I'm going to highlight as far down below to accommodate all possible data entry, and values are going to be entered. I'm going to lock it, and then it just needs to know the relative position in the row number argument.
Now, I want to see if I can look up the last number here, so I'm going to use the MATCH function, which looks up a number and returns the relative position. And the trick to looking up last number is, you give the look-up value some big number, so 9.9E+307. That's approximately the biggest number that Excel knows, you say "Hey, big number, look-up through here, F4, and leave that last argument off." It'll do the approximate match, and it will always get the last relative position. So if I highlight that in F9, 13, that is the last position.
Now watch this. Right now INDEX simply looks up the last number in the column, 2044. Now that's totally silly, INDEX's programmed to look-up values, unless you put the INDEX function into the context of a cell reference. So watch this, I copied it, Ctrl+V, and what is the context? You put a colon! Now this would be a silly dynamic range, it would be A14:A14, so all I need to do is change this row number!
Right now it's, F9, 13, and I need it to be 1-2-3-4. So I simply subtract from the MATCH very carefully, the 10, it will give me one too many, I need 9 like MrExcel, so I add 1 back in.
Now if I highlight this, F9, that 4th position will work perfectly. So two INDEXes will look up, highlight F9, the first value, which was a cell reference the last values, and return a range of values just like the OFFSET. Now notice it's an array of values. And you think, that any formula element that returns an array of values would require Ctrl+Shift+Enter, but actually INDEX and MATCH return the range.
So when I put it inside a MEDIAN or any other wrapper function like SUM, it will understand it perfectly. So that's how you can do it with INDEX, but what if you had empty cells?
Notice relative position for both OFFSET and INDEX are actually calculating this using this value here, and they're seeing a 0 which might mess up the calculation. What we need is are those values right there, because there's an empty cell, I want the last 10 actual values, relative position 1-2-3, not 4. So check this out, we can amend this, copy, and really, it's this first INDEX looking up the first cell reference, we don't want A5, we want A4. And if I highlight this, F9, remember that's 4, and I want 3. So let's see if we can create a formula element, whoops I clicked Esc, that will get relative position 3, not 4.
Now the way you create an array of relative positions in a formula that is robust, is you highlight all of the cells there, F4, that will give me 2-3-4-5-6, and I don't want that, I want 1-2-3, so I'm going to subtract ROW of the first one. Now, right now that will give me 2-2 which would be 0, so you have to add 1 back in. Now that little formula element to create an array of relative positions will work, no matter if you insert rows below or move it around, F9. Notice that's all the relative positions, and I'm really after this 3! So guess what, I'm going to eliminate all the row positions that have empty cells, all these ones down here, by using the IF function.
So I'm going to say IF, and the logical test right here will do an array operation, which means this will require Ctrl+Shift+Enter, ISNUMBER, right? We're after only the cells with numbers, F4, that's the logical test, comma, the value of TRUE will be all those relative positions. And guess what, we don't put value of FALSE, because if we leave this argument out of the IF, and it gets a FALSE, it will put the actual value FALSE. So when I hit F9, there is a way to get all the relative positions, only the positions that have numbers.
Now we need what, the 10th biggest, which would be 3, so guess what? I put that array of values into the LARGE, and I say ,k , give me the 10th largest. Now this will require Ctrl+Shift+Enter, I use Ctrl+Shift+Enter to enter the array for me, you can see the curly brackets up there, that's the 3 we need. So now this will work no matter how many empty cells there are, it will always get the last 10 values. So I come up here and right there, instead of using that, I'm going to Ctrl+V, and use that whole big thing right there.
I click on that argument, now this is going to require Ctrl+Shift+Enter, and boom, there we have it. Now if you were to calculate the MEDIAN for all of those, it would, that's the correct answer. Now if you wanted to do it with OFFSET, guess what, if we have empty cells, we're still going to have to do something crazy, like that little bit right there. Alright, throwback to MrExcel!
Bill: Wow Mike, that was an awesome trick, using the LARGE ,10 to get the position, what an awesome thing. People should go preorder your book "Ctrl+Shift+Enter" at Amazon, that is an amazing trick.
Alright, I want to thank everyone for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun!
Alright, so today's question sent in by digiderek123 at YouTube, "if there's a way to use Excel to calculate our rolling median where, as new entries are added to the column, Excel will calculate the median for the 10 most recent entries (ignoring all previous entries in the column)." So here we want to get the last 10. I'm watching the name box up there, above A1, 10 rows * 1 column, we want to get a median of that. But he doesn't want to have to manually select the last 10 entries, each time a new entry is put in the column. So to me, that is a perfect use for OFFSET! OFFSET's going to need to calculate where the start is. So how many numeric entries, I'm going to use COUNT, not COUNTA, we're going to ignore the heading. And of course, my method is going to assume that there are no blanks, you can't skip any sales, you're just going to enter numeric entries all the way down. Where does entry #1 start? Alright, so we have 13 entries, means the last one's in row 14, and the first one is in row 5. So the formula here is = 13-8!
But there's a funny thing with OFFSET, we can't specify where something starts, we have to say how many rows it is below an actual cell. So I can start from F1, I can't start from F0. So I'm going to change this question: Where does entry 1 start in relation to F1?
And that is going to have to be H2-9. So that way from F1, we're going to go down 4 cells.
Alright, so to build the OFFSET, I'll call this "Median of Last 10". I always build the OFFSET first, =OFFSET(F1 , and we'll lock that with the F4 key, comma, how many rows down, it is the answer from there, comma, how many columns over, 0 columns over, how many rows tall, 10 rows tall, how many columns wide, 1 column wide. Alright, so there's our formula up there, =OFFSET, OFFSET is going to return 10 cells. So we have to put it in a wrapper function, and luckily for us, the wrapper function that we need is MEDIAN. Easy to do, so right there =MEDIAN, close(?) the whole thing, 2580, let's add a really small cell here, and 2474. Let's test =MEDIAN of these last 10. This is what Derek did not want to have to do, but we'd get the exact same answer. Let's see, delete here, delete that last entry, and edit this just by grabbing that blue outline, not in a corner, press Enter, and 2580, it looks like it's working.
Alright, now did we need these extra helper cells here, certainly not! So that was count of F and -9. So right here where we had pointed to H5, we could say =COUNT of F:F-9, and there we go. As we enter more items, let's just enter a 1000 here in 4 new cells, and it calculates based on the last ten. How am I changing the yellow boxes there, that's Alt O D, Conditional Formatting, Edit Rule, I said "Use a formula determine which cells to format." ROW of F2 >= COUNT(F) -8. Why 8 here and 9 here, because this had to be in relation to F1, this was just using row numbers, so it will highlight for me which ones are included in the MEDIAN.
Alright Mike, let's see what you have!
Mike: Thanks MrExcel! Hey, OFFSET function to create a dynamic range for the last 10 values entered in a column, absolutely beautiful!
Now the MEDIAN then takes these the OFFSET, spits out a range of values, F9, and what does the MEDIAN do? It actually takes the list, sorts them, if there's an odd number, it takes the average of the middle two. If you look down here 1607, that's exactly what that does. Now why is the OFFSET such a beautiful solution? Let's go over here. And the funny thing is, in chapter 13 of my upcoming array formula book, I use that, I do this exact example, but I show you how to create a dynamic range with the INDEX function. And it was kind of silly of me not to show OFFSET, because OFFSET is much easier, the actual formula is much shorter, easier to type out.
So why would you ever use INDEX to create a dynamic range? Well, OFFSET is volatile, and for large data sets volatile just means it recalculates often, and if you have a large data set, it slows down calculation. So if you want to create a dynamic range with the INDEX, here's how you can do it: Now INDEX, normally you give it an array of values, and I'm going to highlight as far down below to accommodate all possible data entry, and values are going to be entered. I'm going to lock it, and then it just needs to know the relative position in the row number argument.
Now, I want to see if I can look up the last number here, so I'm going to use the MATCH function, which looks up a number and returns the relative position. And the trick to looking up last number is, you give the look-up value some big number, so 9.9E+307. That's approximately the biggest number that Excel knows, you say "Hey, big number, look-up through here, F4, and leave that last argument off." It'll do the approximate match, and it will always get the last relative position. So if I highlight that in F9, 13, that is the last position.
Now watch this. Right now INDEX simply looks up the last number in the column, 2044. Now that's totally silly, INDEX's programmed to look-up values, unless you put the INDEX function into the context of a cell reference. So watch this, I copied it, Ctrl+V, and what is the context? You put a colon! Now this would be a silly dynamic range, it would be A14:A14, so all I need to do is change this row number!
Right now it's, F9, 13, and I need it to be 1-2-3-4. So I simply subtract from the MATCH very carefully, the 10, it will give me one too many, I need 9 like MrExcel, so I add 1 back in.
Now if I highlight this, F9, that 4th position will work perfectly. So two INDEXes will look up, highlight F9, the first value, which was a cell reference the last values, and return a range of values just like the OFFSET. Now notice it's an array of values. And you think, that any formula element that returns an array of values would require Ctrl+Shift+Enter, but actually INDEX and MATCH return the range.
So when I put it inside a MEDIAN or any other wrapper function like SUM, it will understand it perfectly. So that's how you can do it with INDEX, but what if you had empty cells?
Notice relative position for both OFFSET and INDEX are actually calculating this using this value here, and they're seeing a 0 which might mess up the calculation. What we need is are those values right there, because there's an empty cell, I want the last 10 actual values, relative position 1-2-3, not 4. So check this out, we can amend this, copy, and really, it's this first INDEX looking up the first cell reference, we don't want A5, we want A4. And if I highlight this, F9, remember that's 4, and I want 3. So let's see if we can create a formula element, whoops I clicked Esc, that will get relative position 3, not 4.
Now the way you create an array of relative positions in a formula that is robust, is you highlight all of the cells there, F4, that will give me 2-3-4-5-6, and I don't want that, I want 1-2-3, so I'm going to subtract ROW of the first one. Now, right now that will give me 2-2 which would be 0, so you have to add 1 back in. Now that little formula element to create an array of relative positions will work, no matter if you insert rows below or move it around, F9. Notice that's all the relative positions, and I'm really after this 3! So guess what, I'm going to eliminate all the row positions that have empty cells, all these ones down here, by using the IF function.
So I'm going to say IF, and the logical test right here will do an array operation, which means this will require Ctrl+Shift+Enter, ISNUMBER, right? We're after only the cells with numbers, F4, that's the logical test, comma, the value of TRUE will be all those relative positions. And guess what, we don't put value of FALSE, because if we leave this argument out of the IF, and it gets a FALSE, it will put the actual value FALSE. So when I hit F9, there is a way to get all the relative positions, only the positions that have numbers.
Now we need what, the 10th biggest, which would be 3, so guess what? I put that array of values into the LARGE, and I say ,k , give me the 10th largest. Now this will require Ctrl+Shift+Enter, I use Ctrl+Shift+Enter to enter the array for me, you can see the curly brackets up there, that's the 3 we need. So now this will work no matter how many empty cells there are, it will always get the last 10 values. So I come up here and right there, instead of using that, I'm going to Ctrl+V, and use that whole big thing right there.
I click on that argument, now this is going to require Ctrl+Shift+Enter, and boom, there we have it. Now if you were to calculate the MEDIAN for all of those, it would, that's the correct answer. Now if you wanted to do it with OFFSET, guess what, if we have empty cells, we're still going to have to do something crazy, like that little bit right there. Alright, throwback to MrExcel!
Bill: Wow Mike, that was an awesome trick, using the LARGE ,10 to get the position, what an awesome thing. People should go preorder your book "Ctrl+Shift+Enter" at Amazon, that is an amazing trick.
Alright, I want to thank everyone for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun!
