Another cool discovery by Bob Umlas. Say that you need to add a Total, Average, Max, and Min to the right of a data set. The AutoSum drop-down menu in Excel offers all four features. But how can you use them without the previous Sum getting in the way? Bob discovered a cool way.
Transcript of the video:
Learn Excel from MrExcel podcast episode 2403.
Another Bob Umlas Autosum Oddity.
Hey, welcome back to MrExcel Netcast.
I am Bill Jelen.
Now out here on the far right hand side of the Home tab we have the AutoSum drop down.
It has Sum, Average Count, Max and Min.
And that exact same dropdown is here on the Formulas tab. I'm going to use this one on the formulas tab because it will stay in the screen and I won't have to scroll back and forth.
So now you all know this trick, I am sure.
If you want to add totals to the right edge and the bottom of a data set, you select all of your numbers plus the extra row plus the extra column and hit the auto sum.
But here's the new thing that Bob discovered.
So we just hit the auto sum here.
That has the total and that total is B2 to E2, which is correct.
But then if you come out here and say that you want an average, unfortunately the average is not smart enough to exclude the total.
It's going to include it.
That's not going to work.
But Bob discovered that if you choose these four cells and then open the autosum and choose average, it puts it in the right place.
Now this is annoying.
It changed my selection to include the total, but when I come over and look, it totaled the original things that I had selected.
So if I choose those four cells, AutoSum and use Max, I get the max in the right spot.
And then Control Click to get rid of the total.
And Autosum, Min.
I get them in the right spot.
In each case it's doing the right thing.
Cool little trick there from Bob.
I've no idea why that works.
Was that planned?
Is that a feature?
Unknown right?
So a couple of cool little auto some tricks there.
Bob, of course, is the original Excel Trickster.
He can figure out all these weird tricks.
Check out Bob's new book, Cool Excel sh*t.
Click that "i" in the top right hand corner for more information.
If you like these videos, please down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below.
I want to thank you for stopping by.
Thanks to Bob Umlas for that trick.
I will see you next time for another netcast from MrExcel.
Another Bob Umlas Autosum Oddity.
Hey, welcome back to MrExcel Netcast.
I am Bill Jelen.
Now out here on the far right hand side of the Home tab we have the AutoSum drop down.
It has Sum, Average Count, Max and Min.
And that exact same dropdown is here on the Formulas tab. I'm going to use this one on the formulas tab because it will stay in the screen and I won't have to scroll back and forth.
So now you all know this trick, I am sure.
If you want to add totals to the right edge and the bottom of a data set, you select all of your numbers plus the extra row plus the extra column and hit the auto sum.
But here's the new thing that Bob discovered.
So we just hit the auto sum here.
That has the total and that total is B2 to E2, which is correct.
But then if you come out here and say that you want an average, unfortunately the average is not smart enough to exclude the total.
It's going to include it.
That's not going to work.
But Bob discovered that if you choose these four cells and then open the autosum and choose average, it puts it in the right place.
Now this is annoying.
It changed my selection to include the total, but when I come over and look, it totaled the original things that I had selected.
So if I choose those four cells, AutoSum and use Max, I get the max in the right spot.
And then Control Click to get rid of the total.
And Autosum, Min.
I get them in the right spot.
In each case it's doing the right thing.
Cool little trick there from Bob.
I've no idea why that works.
Was that planned?
Is that a feature?
Unknown right?
So a couple of cool little auto some tricks there.
Bob, of course, is the original Excel Trickster.
He can figure out all these weird tricks.
Check out Bob's new book, Cool Excel sh*t.
Click that "i" in the top right hand corner for more information.
If you like these videos, please down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below.
I want to thank you for stopping by.
Thanks to Bob Umlas for that trick.
I will see you next time for another netcast from MrExcel.
