OK. Yes. Sorry. I spent two days on insanely hard formulas. Thanks for the letters, phone calls, and e-mails. There is a simple way to total five cells diagonally. Episode 670 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:
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Well, it is a third day I came.
This is the question here, the last two podcasts have been you know, the kind of formulas that just make your head spin.
Canvas is needed to total along the diagonal and so we spent a couple of days, creating a huge array formula.
It would do that.
I'm going to assume that in real life, canvas has many many more columns in this.
But if you really just had to total the last five cells...
Sometimes the simple way is just the way to go.
What if you just build a simple formula here?
How long would it take?
Type the plus sign, grab the next cell.
Type the plus sign, grab the cell.
Type the plus sign, grab the last cell.
There we go.
That's built a form that grabs the last five values.
Of course now because of the relative referencing.
As we can go certainly up to row 5 and everything will work.
Copy that formula down and you will always be grabbing the diagonal.
So, you don't have to worry about the insanely hard Chip Pearson Array Formula.
You can just do it using a simple way, your call.
Simple way or the insanely hard way from yesterday, depends on whether you want anyone else to understand what's going on or not.
Sometimes the simplest solutions are the best.
Thanks to everyone who yell at me yesterday.
Hey, what are you going to all this trouble for?
It was a really simple formula, and you're right.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
Well, it is a third day I came.
This is the question here, the last two podcasts have been you know, the kind of formulas that just make your head spin.
Canvas is needed to total along the diagonal and so we spent a couple of days, creating a huge array formula.
It would do that.
I'm going to assume that in real life, canvas has many many more columns in this.
But if you really just had to total the last five cells...
Sometimes the simple way is just the way to go.
What if you just build a simple formula here?
How long would it take?
Type the plus sign, grab the next cell.
Type the plus sign, grab the cell.
Type the plus sign, grab the last cell.
There we go.
That's built a form that grabs the last five values.
Of course now because of the relative referencing.
As we can go certainly up to row 5 and everything will work.
Copy that formula down and you will always be grabbing the diagonal.
So, you don't have to worry about the insanely hard Chip Pearson Array Formula.
You can just do it using a simple way, your call.
Simple way or the insanely hard way from yesterday, depends on whether you want anyone else to understand what's going on or not.
Sometimes the simplest solutions are the best.
Thanks to everyone who yell at me yesterday.
Hey, what are you going to all this trouble for?
It was a really simple formula, and you're right.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
