Kambiz sends in a question today that is far more difficult than the example from podcast 668. Kambiz needs running diagonal totals. In Episode 669, we take a look at Chips formula from Episode 668 and figure out how to modify it to solve this tricky problem.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Now, finally we're gonna get two Cannabis question.
Cannabis sent this question in last week and when I saw it I had a laugh because I instantly reminded me of that conversation with Eric.
about totaling numbers diagonally and then subsequently.
Chip Pearson teaching me how to tell the numbers diagonally.
Yesterday, we took a look at how the total numbers diagonally and Cannabis question is a little bit more difficulty He says hey I have this data set in right here.
I need to add up the number immediately adjacent, but then as I go over additional columns, I need to go back additional row.
So, basically we wanted to total up all of those blue cells here in column A.
Now, yesterday we used the formula from chip.
It was an array formula that said hey we're gonna go through and compare the row number for each cell in this data set with the column number for each cell in this data set and when they're equal And we're going to get a 1 and multiply that by the whole data set.
Basically just to give us the numbers along the diagonal.
Well, this is a little bit more difficult because we kind of need to go backwards.
And the first thing I do when I think about this is I say okay! Let's take this five row by five column area And If we ask for the column number of all of those columns, I'm gonna get column two, three, four, five and six.
So, I need a way for the row piece to return the numbers two, three, four, five and six.
In this fashion this should say two.
This should say three.
This should say four and so logically what I'm thinking about is basically, I want to take the active cell plus two rows.
So here, I'm working in row 16 but I want to go down to row 18 and say take the row number 18 minus the row number that we find in this range, and so as I built this formula, I had to adjust Chips formula a little bit.
Basically said hey we're gonna take the column of that whole range B12 to F16 and see if it's equal to the row number that is two rows below us.
In this case that's 18 minus the row number of each cell in the five row area.
If all of that is true then multiply it by B12 to F16 and sure enough that works and because we've used no dollar signs throughout.
As we copy that formula, it very easily goes through and gives us the totals of the last five rows in the diagonal.
I have no idea why Cannabis has to do this.
As soon as I saw the question that will reminded me of the web page from chip.
I had to go back to the website and figure out that formula.
It's one of these things that I've dealt with twice now in 10 years.
So, I always had a kind of piece it through that's why we did two netcast about it Thanks to Cannabis for sending in this question.
And thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
Now, finally we're gonna get two Cannabis question.
Cannabis sent this question in last week and when I saw it I had a laugh because I instantly reminded me of that conversation with Eric.
about totaling numbers diagonally and then subsequently.
Chip Pearson teaching me how to tell the numbers diagonally.
Yesterday, we took a look at how the total numbers diagonally and Cannabis question is a little bit more difficulty He says hey I have this data set in right here.
I need to add up the number immediately adjacent, but then as I go over additional columns, I need to go back additional row.
So, basically we wanted to total up all of those blue cells here in column A.
Now, yesterday we used the formula from chip.
It was an array formula that said hey we're gonna go through and compare the row number for each cell in this data set with the column number for each cell in this data set and when they're equal And we're going to get a 1 and multiply that by the whole data set.
Basically just to give us the numbers along the diagonal.
Well, this is a little bit more difficult because we kind of need to go backwards.
And the first thing I do when I think about this is I say okay! Let's take this five row by five column area And If we ask for the column number of all of those columns, I'm gonna get column two, three, four, five and six.
So, I need a way for the row piece to return the numbers two, three, four, five and six.
In this fashion this should say two.
This should say three.
This should say four and so logically what I'm thinking about is basically, I want to take the active cell plus two rows.
So here, I'm working in row 16 but I want to go down to row 18 and say take the row number 18 minus the row number that we find in this range, and so as I built this formula, I had to adjust Chips formula a little bit.
Basically said hey we're gonna take the column of that whole range B12 to F16 and see if it's equal to the row number that is two rows below us.
In this case that's 18 minus the row number of each cell in the five row area.
If all of that is true then multiply it by B12 to F16 and sure enough that works and because we've used no dollar signs throughout.
As we copy that formula, it very easily goes through and gives us the totals of the last five rows in the diagonal.
I have no idea why Cannabis has to do this.
As soon as I saw the question that will reminded me of the web page from chip.
I had to go back to the website and figure out that formula.
It's one of these things that I've dealt with twice now in 10 years.
So, I always had a kind of piece it through that's why we did two netcast about it Thanks to Cannabis for sending in this question.
And thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
