In our latest Dueling Excel Podcast, Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen look at using =SUM, =AVERAGE, =STDEV, =MIN, =MAX with a Pivot Table and More to show a set of Descriptive Statistics from the current Data Set. Follow along with Mike and Bill in Episode #1568 to see a few very important Functions in action!
Dueling Excel Podcast #108...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel Podcast #108...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Bill Jelen: Hey, welcome back. It's another Dueling Excel Podcast.
This is Episode 108. I'm Bill Jelen from MrExcel.com.
We’ll be joined by Mike Girvin from Excel Is Fun.
Descriptive Statistics.
Oh, hey, everyone.
Hey, Mike.
Back after a long break.
I have to apologize, I was writing a book, a book for beginning Excel users.
And of course, I can never get any podcast done when I’m writing a book but that book is out and we’re back.
So, today’s question: They want to have a large data set here in UnitsSold, coming from B2 down to, looks like, B1206.
We want to come up with a SUM, AVERAGE, STANDARD DEVIATION, MIN and MAX.
And Mike said, “Hey, maybe you can do a pivot table.” Wait a second, I don’t think I need to do a pivot table.
I don’t think I need to because if I just select the whole range, and then come down here to the bottom-right hand corner, there’s the Sum, there’s the Average, there’s the Min, there’s the – Oh, I don’t know – Standard Deviation.
And if I right-click, it’s not available to me in those quick statistics.
Shoot!
That would have solved it.
We don’t write there.
Ahh!
But, I want to Alt+TI.
Alt+TI, we’ll have the Add-Ins.
When I turn on the Analysis ToolPak and that gets me this Data Analysis button over here.
And these always strike me as being a little, little hard to use, let’s say.
But I remember there’s something called Descriptive Statistics.
Click OK and here’s our input range: B1 to B1206.
The data is in a column, yes.
Labels in first row, Output Range D4, and just choose stump Summary statistics, click OK, and BAM!
There we go.
So: Sum, there it is 484; Min, that’s the average, right?
Standard Deviation is there, the Min, the Max, the Range, a few other things they didn’t even ask for.
And we’re done.
Alright, but let’s try it with a pivot table, if we want to do all these.
So, Insert, Pivot Table, I’m going to put it right here in D, click OK.
And Units Sold, I’m going to take it 5 times.
One, two, three, four, and five.
I’m going to put this going down.
So it stays in your screen.
So, there’s Sum, we’ll come here and say: Field Settings, I want to see the Average, click OK.
Come to the next one.
Field Settings, I want to see the Min.
Field Settings, the Max.
Field Settings, Standard Deviation or Standard Deviation of the entire population, one of those two.
This is funny, I, all of a sudden just had a flash.
I just had a flash.
I understand why Mike ‘Excel Is Fun’ Girvin sent this through.
Because sometime in the last two months, I made this, I said the sentence.
I’ve never had a chance to create a pivot table or use Standard Deviation.
Mike, thank you for getting that off my pocket list.
Alright, so two different ways.
The Analysis Toolpak, Data Analysis or a Pivot Table.
Mike, let’s see what you have.
Mike Girvin: Thanks, MrExcel man.
Great to be back and doing a duel.
I love the three ways you did it.
That first way you did it, you used the Status bar, if only we had Standard Deviation.
And the second way, this data analysis.
I'm going to have to give you the point and I'm doing that if I took a vote in the statistics class I teach, they like this method because you highlight, you click it.
It does a bunch of things.
Pivot table totally awesome.
We didn't get to do it here but standard deviation of pivot table is the best way when you have more than one criteria.
So, if there's no standard deviation IF function, so when you have one or two or three criteria, man, pivot tables rock.
So, okay.
I'm going to do- use functions and define names.
Now, you know, I can't type.
I see something like this.
I have the choice of typing SUM, AVERAGE, STANDARD DEVIATION, but I’m going to down a totally cheat here.
I'm going to go to Text to Columns, let me say Delimited, Next.
And notice it split it across, oh why, it comes up by default like this.
I'm going to select Comma but notice it's splitting it there.
I don't want those spaces so I'm going to click Space.
And then I noticed that there's a colon there, so I'm going to say, Other and type a colon.
That will split it all out into the cells and then I'm going to highlight this, Ctrl+C, right-click, Paste Special, Transpose.
Look at that.
And then I can delete that.
Now I want to use Define Names.
I'm going to click in the field name, Ctrol+Shift+Down Arrow, and I'm going to use the keyboard shortcut Ctrl+Shift+F3, that creates names from selection.
It says, hey, where's the name?
It's at the top.
I'm going to click OK, so now there is a defined name.
Now, I'm just going to come here Alt+= and then type U, and notice the screen tip.
That icon is for functions, the dog tag is for defined names.
So I Tab =AVER Tab, and then U, Tab, Enter.
=STDE, now in 2010, they came up with .P, .S and a lot of the statistical functions have this dot convention.
This little insignia down here means these are in here for compatibility only but they want to use the new one.
Population since we have a sample, we're going to use S and then U, Tab, Enter.
=MIN, Tab, U, Tab, Enter.
And finally, =MAX, Tab, U, Tab, Enter.
And there we have standards.
Some average standard deviation, Min and Max.
Alright, MrExcel, totally great to be doing duels again.
I'm glad that pocket list is one item less, of course, the master that you are, I’m sure that pocket list is not very big.
Alright, we'll see ya.
Thanks, see you next duel.
Bill Jelen: Hey, alright, Mike, that's great.
In an episode on Descriptive Statistics, I show us a cool way to use data Text to Columns and Paste Special, Transpose to prevent a lot of typing.
As far as the fewest number of keystrokes, that was awesome.
Hey, I want to thank everyone for stopping by.
We'll see you next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
This is Episode 108. I'm Bill Jelen from MrExcel.com.
We’ll be joined by Mike Girvin from Excel Is Fun.
Descriptive Statistics.
Oh, hey, everyone.
Hey, Mike.
Back after a long break.
I have to apologize, I was writing a book, a book for beginning Excel users.
And of course, I can never get any podcast done when I’m writing a book but that book is out and we’re back.
So, today’s question: They want to have a large data set here in UnitsSold, coming from B2 down to, looks like, B1206.
We want to come up with a SUM, AVERAGE, STANDARD DEVIATION, MIN and MAX.
And Mike said, “Hey, maybe you can do a pivot table.” Wait a second, I don’t think I need to do a pivot table.
I don’t think I need to because if I just select the whole range, and then come down here to the bottom-right hand corner, there’s the Sum, there’s the Average, there’s the Min, there’s the – Oh, I don’t know – Standard Deviation.
And if I right-click, it’s not available to me in those quick statistics.
Shoot!
That would have solved it.
We don’t write there.
Ahh!
But, I want to Alt+TI.
Alt+TI, we’ll have the Add-Ins.
When I turn on the Analysis ToolPak and that gets me this Data Analysis button over here.
And these always strike me as being a little, little hard to use, let’s say.
But I remember there’s something called Descriptive Statistics.
Click OK and here’s our input range: B1 to B1206.
The data is in a column, yes.
Labels in first row, Output Range D4, and just choose stump Summary statistics, click OK, and BAM!
There we go.
So: Sum, there it is 484; Min, that’s the average, right?
Standard Deviation is there, the Min, the Max, the Range, a few other things they didn’t even ask for.
And we’re done.
Alright, but let’s try it with a pivot table, if we want to do all these.
So, Insert, Pivot Table, I’m going to put it right here in D, click OK.
And Units Sold, I’m going to take it 5 times.
One, two, three, four, and five.
I’m going to put this going down.
So it stays in your screen.
So, there’s Sum, we’ll come here and say: Field Settings, I want to see the Average, click OK.
Come to the next one.
Field Settings, I want to see the Min.
Field Settings, the Max.
Field Settings, Standard Deviation or Standard Deviation of the entire population, one of those two.
This is funny, I, all of a sudden just had a flash.
I just had a flash.
I understand why Mike ‘Excel Is Fun’ Girvin sent this through.
Because sometime in the last two months, I made this, I said the sentence.
I’ve never had a chance to create a pivot table or use Standard Deviation.
Mike, thank you for getting that off my pocket list.
Alright, so two different ways.
The Analysis Toolpak, Data Analysis or a Pivot Table.
Mike, let’s see what you have.
Mike Girvin: Thanks, MrExcel man.
Great to be back and doing a duel.
I love the three ways you did it.
That first way you did it, you used the Status bar, if only we had Standard Deviation.
And the second way, this data analysis.
I'm going to have to give you the point and I'm doing that if I took a vote in the statistics class I teach, they like this method because you highlight, you click it.
It does a bunch of things.
Pivot table totally awesome.
We didn't get to do it here but standard deviation of pivot table is the best way when you have more than one criteria.
So, if there's no standard deviation IF function, so when you have one or two or three criteria, man, pivot tables rock.
So, okay.
I'm going to do- use functions and define names.
Now, you know, I can't type.
I see something like this.
I have the choice of typing SUM, AVERAGE, STANDARD DEVIATION, but I’m going to down a totally cheat here.
I'm going to go to Text to Columns, let me say Delimited, Next.
And notice it split it across, oh why, it comes up by default like this.
I'm going to select Comma but notice it's splitting it there.
I don't want those spaces so I'm going to click Space.
And then I noticed that there's a colon there, so I'm going to say, Other and type a colon.
That will split it all out into the cells and then I'm going to highlight this, Ctrl+C, right-click, Paste Special, Transpose.
Look at that.
And then I can delete that.
Now I want to use Define Names.
I'm going to click in the field name, Ctrol+Shift+Down Arrow, and I'm going to use the keyboard shortcut Ctrl+Shift+F3, that creates names from selection.
It says, hey, where's the name?
It's at the top.
I'm going to click OK, so now there is a defined name.
Now, I'm just going to come here Alt+= and then type U, and notice the screen tip.
That icon is for functions, the dog tag is for defined names.
So I Tab =AVER Tab, and then U, Tab, Enter.
=STDE, now in 2010, they came up with .P, .S and a lot of the statistical functions have this dot convention.
This little insignia down here means these are in here for compatibility only but they want to use the new one.
Population since we have a sample, we're going to use S and then U, Tab, Enter.
=MIN, Tab, U, Tab, Enter.
And finally, =MAX, Tab, U, Tab, Enter.
And there we have standards.
Some average standard deviation, Min and Max.
Alright, MrExcel, totally great to be doing duels again.
I'm glad that pocket list is one item less, of course, the master that you are, I’m sure that pocket list is not very big.
Alright, we'll see ya.
Thanks, see you next duel.
Bill Jelen: Hey, alright, Mike, that's great.
In an episode on Descriptive Statistics, I show us a cool way to use data Text to Columns and Paste Special, Transpose to prevent a lot of typing.
As far as the fewest number of keystrokes, that was awesome.
Hey, I want to thank everyone for stopping by.
We'll see you next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
