Today, in Episode #1298, Bill "MrExcel" Jelen and Mike "Excel Is Fun" Girvin set out to create a 'median' of test scores by moving down rows and across column by simply working in the Formula Bar. Also the F9 Evaluation Feature is highlighted today!
Transcript of the video:
Hey! All right, it's another to Excel podcast.
I'm Bill Jelen from MrExcel will be joined by Mike Girvin from Excel Is Fun.
I love these, this is called Copy Down for Across.
It's are 56th episode of the dueling Excel podcast.
This questions send in...
Someone wants to create a Median of these test scores they want enter that right here in cell C2, but they want to be able to copy that formula down and each time it goes down a row, they wanted to go over a column.
All right, they don't have to re-enter the formula each different time.
So, here's how I would attack this.
I want to use the medium function, but i'm not going to specify, I'm not going to hard code the range, I'm going to use i'm going to use the OFFSET function.
OFFSET is an amazing function that lets us do so many different things.
So, we always start from a top left corner cell.
I'm going to start here from cell A8, I'm going to press F4 to lock that down and then it says, all right, how many rows down you want to go to get to the starting point of the range.
0 in this case, how many columns over you want to go.
Okay well, for this first one, for test one I want to go over one column, but I'm not going to put the number one in there.
I'm going to use the world's geekiest way to write the number one that's called row of A1, the beautiful thing about row A1, it tells me that A1 is in cell, is in row 1.
So, I get a one that's great, but as I copy that down it's going to change to row of A2, which is going to give me a two and then three and then a four and then so on.
All right, a comma how tall we want it to be, while we wanted to be 12 rows tall and how wide one column wide.
So, that finishes off our OFFSET, close off our MEDIAN, and 71.
Now, I want to copy this down it automatically changes see right here changes to be row of A2, that's going to give me the second column over from that starting cell and then the third column over and then fourth column over and so on.
So, using OFFSET a great way to make things as you copied them down go across.
Mike let's see what you have.
Bill: Thanks MrExcel.
Hey! You gotta love that OFFSET inside a MEDIAN.
I'm going to hit enter let's come over here to this sheet and I got to use the MEDIAN. right?
MEDIAN, oh! And instead of OFFSET, I'll try the INDEX.
Now, INDEX can look up a whole column and as we go down we're going to tell the INDEX look up this column first, then this column, then this column, array means all the values and index is specifically built.
I'm going to hit F4 there to lock it.
The INDEX function is specifically built to do a 2 way look up.
So, if I were to say, hey ray look through here and find the second row 1, 2 and the second column, it would return the number 54.
So, comma after you put the array in and there's a row and a column.
So, all you got to do is say which row and column.
Well, the cool thing is row if you leave it blank, it'll give you all the rows.
So, for example, for test one we do we want column one, but all of the row.
So, I'm just going to leave it blank comma and then column number.
Now, we just need for test one, we need to say column one test two we need to say column two.
Now, I could use the row or the rows which probably, I would tend to do most of the time since these are probably in the same order as these, but if they're not, you could switch over to the MATCH, the MATCH can still give us the column number, but it will actually do a lookup. It'll lookup test 1 and look through here and say oh that's the first item.
So, the lookup value is going to be that one there comma look up array these ones here F4 comma and we want an exact match we're looking up words that are not necessarily sorted.
So, I'm going to type 0 for Exact match, close parentheses on the MATCH, close parentheses, oh, that's our column number, right? Close parentheses on the INDEX, close parentheses on the MEDIAN.
All right, Control+Enter+double click and send it down.
Let's come and look at this one right here the MATCH should be given us one, two, three.
So, I'm going to highlight this and hit the F9 key, sure enough it is Control+Z.
and the INDEX better give us this whole column 70, 23, 46.
I'm going to hit the F9 key that's the evaluate key and sure enough it's given us the same values is there.
So, it's looking up the whole column in essence Control+Z, the match looks up the column number the INDEX delivers the whole column.
All right! Now, let's test this we use the MATCH instead of row or rows.
So, that if these were out of order, which they probably wouldn't be here, right?
So, I have test three listed first for whatever reason and sure enough because it's the MATCH when we come here it's looking that up, right?
So, it's going to say that's the third item here which will be our column number and sure enough that's working.
All right! Throw it back over to MrExcel.
Bill: Hey, Mike, good trick as always.
Well! Hey, I want to thank everyone for stopping by.
See you next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel will be joined by Mike Girvin from Excel Is Fun.
I love these, this is called Copy Down for Across.
It's are 56th episode of the dueling Excel podcast.
This questions send in...
Someone wants to create a Median of these test scores they want enter that right here in cell C2, but they want to be able to copy that formula down and each time it goes down a row, they wanted to go over a column.
All right, they don't have to re-enter the formula each different time.
So, here's how I would attack this.
I want to use the medium function, but i'm not going to specify, I'm not going to hard code the range, I'm going to use i'm going to use the OFFSET function.
OFFSET is an amazing function that lets us do so many different things.
So, we always start from a top left corner cell.
I'm going to start here from cell A8, I'm going to press F4 to lock that down and then it says, all right, how many rows down you want to go to get to the starting point of the range.
0 in this case, how many columns over you want to go.
Okay well, for this first one, for test one I want to go over one column, but I'm not going to put the number one in there.
I'm going to use the world's geekiest way to write the number one that's called row of A1, the beautiful thing about row A1, it tells me that A1 is in cell, is in row 1.
So, I get a one that's great, but as I copy that down it's going to change to row of A2, which is going to give me a two and then three and then a four and then so on.
All right, a comma how tall we want it to be, while we wanted to be 12 rows tall and how wide one column wide.
So, that finishes off our OFFSET, close off our MEDIAN, and 71.
Now, I want to copy this down it automatically changes see right here changes to be row of A2, that's going to give me the second column over from that starting cell and then the third column over and then fourth column over and so on.
So, using OFFSET a great way to make things as you copied them down go across.
Mike let's see what you have.
Bill: Thanks MrExcel.
Hey! You gotta love that OFFSET inside a MEDIAN.
I'm going to hit enter let's come over here to this sheet and I got to use the MEDIAN. right?
MEDIAN, oh! And instead of OFFSET, I'll try the INDEX.
Now, INDEX can look up a whole column and as we go down we're going to tell the INDEX look up this column first, then this column, then this column, array means all the values and index is specifically built.
I'm going to hit F4 there to lock it.
The INDEX function is specifically built to do a 2 way look up.
So, if I were to say, hey ray look through here and find the second row 1, 2 and the second column, it would return the number 54.
So, comma after you put the array in and there's a row and a column.
So, all you got to do is say which row and column.
Well, the cool thing is row if you leave it blank, it'll give you all the rows.
So, for example, for test one we do we want column one, but all of the row.
So, I'm just going to leave it blank comma and then column number.
Now, we just need for test one, we need to say column one test two we need to say column two.
Now, I could use the row or the rows which probably, I would tend to do most of the time since these are probably in the same order as these, but if they're not, you could switch over to the MATCH, the MATCH can still give us the column number, but it will actually do a lookup. It'll lookup test 1 and look through here and say oh that's the first item.
So, the lookup value is going to be that one there comma look up array these ones here F4 comma and we want an exact match we're looking up words that are not necessarily sorted.
So, I'm going to type 0 for Exact match, close parentheses on the MATCH, close parentheses, oh, that's our column number, right? Close parentheses on the INDEX, close parentheses on the MEDIAN.
All right, Control+Enter+double click and send it down.
Let's come and look at this one right here the MATCH should be given us one, two, three.
So, I'm going to highlight this and hit the F9 key, sure enough it is Control+Z.
and the INDEX better give us this whole column 70, 23, 46.
I'm going to hit the F9 key that's the evaluate key and sure enough it's given us the same values is there.
So, it's looking up the whole column in essence Control+Z, the match looks up the column number the INDEX delivers the whole column.
All right! Now, let's test this we use the MATCH instead of row or rows.
So, that if these were out of order, which they probably wouldn't be here, right?
So, I have test three listed first for whatever reason and sure enough because it's the MATCH when we come here it's looking that up, right?
So, it's going to say that's the third item here which will be our column number and sure enough that's working.
All right! Throw it back over to MrExcel.
Bill: Hey, Mike, good trick as always.
Well! Hey, I want to thank everyone for stopping by.
See you next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
