Today, we look at how to total a data set diagonally. Episode 668 shows you how.
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:
Welcome back to the MrExcel netcast. I'm Bill Jelen.
Well today we have a question sent in by Kambiz actually tomorrow, we're going to get to his question.
Today I just want to do some preliminary work on this.
One of my favorite statements and this came to me from a fellow named Eric that I used to work with.
Eric had a real wry sense of humor and he would talk about impossible request that managers would give.
Something like hey, can you take all those worksheets and total them diagonally for me.
Well you know that you have to laugh with that because that's something that's really hard to do, if you needed to go through and total the data set diagonally.
But it turns out that there is a formula that will total diagonally. I've got this from Chip Pearson.
If you don't know Chip, Chip is another MVP. A long time MVP, has a great website at CPEARSON.COM Chip wrote in to me one day and said.
Hey, you know, I saw that on your website, it turns out there's a formula.
That'll do that. Let's take a quick look at this formula. So it's an array formulas.
We had to use "Contro+Shift+Enter" Basically it says we're going to take the row of this entire range and see if it's equal to the column of the entire range.
And then multiply that by the range.
Now let me show you exactly how this works.
I'm going to go to another worksheet, where I set up each bit of that. In the first part, what is the row of the range.
So that just gives us the numbers 1 2 3 4 5.
What's the column of the range, well that gives us, basically the numbers 1 through 5 but going the other way, so everything in column A is 1, everything column C is 3, everything in column E is 5 and then the formula says. See if those two numbers are equal.
So here's an IF statement.
It says if the row number equals the column number then basically give us a 1 otherwise give us a 0.
And you'll see what that does is, it gives us 1's just along the diagonal of the Matrix.
And then here finally I've put our original numbers and the final result says take that array of zeros and ones and multiply it by the original numbers, so what we get then is just the numbers along the diagonal, the 30 the 50 the 29 the 40 and the 46.
Finally because we have a sum around the whole formula, sums it up. We get 195 We'll go back to the original formula here 195.
You'll see that It works if I would change one of these numbers.
44 and it does accurately work. Now Kambiz sent in a question I'll talk about that tomorrow because it's much more difficult, but it uses the exact same trick.
So thanks for stopping by. I will see you tomorrow, to figure out how we can create a more robust diagonal formula to solve Kambiz's problem.
Well today we have a question sent in by Kambiz actually tomorrow, we're going to get to his question.
Today I just want to do some preliminary work on this.
One of my favorite statements and this came to me from a fellow named Eric that I used to work with.
Eric had a real wry sense of humor and he would talk about impossible request that managers would give.
Something like hey, can you take all those worksheets and total them diagonally for me.
Well you know that you have to laugh with that because that's something that's really hard to do, if you needed to go through and total the data set diagonally.
But it turns out that there is a formula that will total diagonally. I've got this from Chip Pearson.
If you don't know Chip, Chip is another MVP. A long time MVP, has a great website at CPEARSON.COM Chip wrote in to me one day and said.
Hey, you know, I saw that on your website, it turns out there's a formula.
That'll do that. Let's take a quick look at this formula. So it's an array formulas.
We had to use "Contro+Shift+Enter" Basically it says we're going to take the row of this entire range and see if it's equal to the column of the entire range.
And then multiply that by the range.
Now let me show you exactly how this works.
I'm going to go to another worksheet, where I set up each bit of that. In the first part, what is the row of the range.
So that just gives us the numbers 1 2 3 4 5.
What's the column of the range, well that gives us, basically the numbers 1 through 5 but going the other way, so everything in column A is 1, everything column C is 3, everything in column E is 5 and then the formula says. See if those two numbers are equal.
So here's an IF statement.
It says if the row number equals the column number then basically give us a 1 otherwise give us a 0.
And you'll see what that does is, it gives us 1's just along the diagonal of the Matrix.
And then here finally I've put our original numbers and the final result says take that array of zeros and ones and multiply it by the original numbers, so what we get then is just the numbers along the diagonal, the 30 the 50 the 29 the 40 and the 46.
Finally because we have a sum around the whole formula, sums it up. We get 195 We'll go back to the original formula here 195.
You'll see that It works if I would change one of these numbers.
44 and it does accurately work. Now Kambiz sent in a question I'll talk about that tomorrow because it's much more difficult, but it uses the exact same trick.
So thanks for stopping by. I will see you tomorrow, to figure out how we can create a more robust diagonal formula to solve Kambiz's problem.
