Welcome to Excel 2013 Statistical Analysis
video number 9.
Hey, if you want to download this workbook and follow along,
click on the link below the video.
Hey, we're still looking at this data
set for transactions from our Boomerang Sales website.
And in this video, we want to analyze the Revenue column.
But we want to do what's called a cumulative frequency
distribution.
That'll answer questions like, at $150 per transaction,
what percentage of the transactions are $150 or less?
Now we want to jump ahead to a sheet called CD--
for Cumulative Distribution-- Quant Revenue.
Now here's that Revenue column.
It's 200 records.
I've already calculated the max and the min.
Now, in the last couple of videos,
we did frequency distributions either
for categorical or quantitative.
With the quantitative, we had a lower and upper limit.
And we had to create classes.
But when we're doing cumulative, we're
only interested in the upper.
Now, I've already done the max and the min.
Min-- about $20.
Max-- almost $300.
So I'm just going to make the start
and the increment for these up.
I'm going to say an increment of $25.
And I'm going to start at 0--
so 0 and $25.
I better turn my Speak Cells on Enter off.
Now, we only need an upper category to use for COUNTIFS.
So I'm going to start not with 0, because these are the upper.
The first upper limit is 25.
I'm going to hit Enter.
The increment is also 25.
So I'm going to make a formula--
relative cell reference one above plus the increment
and lock it on F4.
Control Enter, and copy it down.
Now, there is the upper limit.
I simply-- I'm going to use COUNTIFS with one condition
and say, hey, count all of the items over here
that are less than or equal to 25, less than or equal to 50,
and so on.
So in cell D7, =COUNTIFS.
I only have one condition.
But I still like the COUNTIFS in all counting situations,
or almost all situations.
Click on the top cell.
Control-Shift-Down Arrow-F4 key to lock it.
And put those dollar signs in.
When we copy it down, we need it locked up there--
comma criteria.
We need to join a comparative operator and our upper limit.
Comparative operator goes in double quotes.
So I say less than or equal to in double quotes and then
join it with Shift-7 the ampersand the upper limit.
That's a relative cell reference.
So when we Control-Enter and double click and send it down,
at any particular point, F2, that
is looking at the right upper limit.
So we can see that when we get down
to the last category, of course, that's
going to include all 200 transactions.
We were careful to make sure that the last upper value was
bigger than anything we found in our data set.
Now, cumulative percent-- hey, I'm going to say
equals the cumulative frequency for this upper limit
relative cell reference divided by the total.
And I'm going to lock that with the F4 key.
Control-Enter.
I've already preformatted it with percentages
showing two decimals.
When I double click and send it down,
you better guess that the last cell is 100%.
If the last cell here is the total count, then this is 100%.
Now, this is our cumulative table.
And it's really quite amazing.
I can say for any particular line here,
71.5% of all the transaction we had were for $100 or less.
90% were for $150 or less.
Now, there's a few ways we can make charts from this.
One way is to use an xy scatter with the upper all along the x
and the cumulative frequency along the y.
That means that the actual line will keep going
up and up until it hits 100%.
Now, if we're going to do that, I
want to attach the xy scatter line to the x-axis at $0, 0%.
So I actually type those in.
The rest of this is straightforward-- relative cell
reference the upper.
Control-Enter.
Copy it down.
Relative cell reference cumulative.
Control-Enter.
Copy it down.
Relative just means when I copy it all the way down,
that blue one kept moving.
So boom, the last one is $300, 100%.
Now we can highlight this and go up to Insert and Scatter,
not line.
We're not using this.
This is for when we collect sample data.
This is the one we use when we have a model.
And this is the type of model that we built.
So I'm going to click on that.
And there we go-- attached to the 0 line here.
And there it goes, all the way up to 100%.
I'm going to click on the title and click inside and type
space.
And then I'm going to type Ogive.
That is another name for this percent cumulative frequency.
And then I'm going to say "for Revenue."
Now I want to click on the chart.
And I definitely want to add some labels to our axes--
the plus axis titles.
Now, I don't think we really need one here.
I'm going to delete that.
And I think I'm going to delete this too,
because I think I'll add the percentages actual as data
labels.
But down here, I want to click--
equal sign jumps to me up here.
Click in cell A1 and Enter.
I was just lazy.
I didn't want to type Revenue there.
Now we can go up to the Plus, Data Labels.
And I'm going to check that.
Now, that's a little cluttered.
So I'm either going to right click Format Data
Labels, or Control-1.
We've seen that Control-1 a lot for charts.
Any element, that keyboard opens up the task pane.
I want to come down to Number.
Make sure that's the column one here.
If it's not on Percentage, then select that.
Select the 2, 0, and Tab.
Close the Task pane.
And boom, there we have a percent cumulative frequency,
or Ogive, chart.
It's very nice, because at any particular point,
I can say $200--
that means 97% of our transactions
were for $200 or less.
90%, $150 or less--
and about 87%.
That's about 125.
Now, you know what?
The vertical ones are helpful, but because I
don't have access over here, I'm going to try and select
the horizontal ones and Delete.
I'm always trying to remove chart junk,
have as little there as possible to get the message effectively
across.
Now, that's with a formula and an xy scatter.
I want to show you one other way to do this.
And it involves a pivot table.
Click in the cell.
Insert Pivot Table, or Alt-N-V. And now
I want to put it on a new sheet.
I can click OK or simply hit Enter.
I'm going to double click this and call this Percent
Cumulative Distribution and Enter.
Now we're going to have to group here.
So I'm going to drag Revenue down to the row area.
Right click Group.
Start at 0.
Tab.
Go to 300 and 10.
I want to highlight that and type 25.
Click OK.
I don't like the ambiguity.
So I'm going to highlight all of the labels there.
Home.
Over to Editing.
Find and Select and Replace.
Or there's a keyboard-- you don't got to know this one--
Control-H.
And now I want to search for a dash.
And I want to replace it with space up space to space.
Replace All.
And instantly they've all been replaced.
I click OK.
Close.
Now I'm going to drag Revenue down to Values.
It defaults to Count, because we've grouped
our quantitative variable.
Click in the cell here.
Revenue Category, Tab, Frequency, Tab.
Maybe change the column width here.
Now I want to add another column to this.
Drag Revenue down to Values.
And lo and behold, there's two ways.
In fact, let's drag two down here--
1, 2.
And although I'm going to take this away,
I want to show you how to do the cumulative.
Right click.
Oh, we've used Show Values As a bunch.
And we come down to Running Total In.
And it's going to ask, which is the field
you want the running total in--
Revenue.
Click OK.
There's the count, exactly as we got with our formulas.
Right click Show Values As.
And there's a running total.
There's a % Running Total In too.
And that's for cumulative percent.
Revenue.
Click OK.
Now, I actually don't want this one.
I just-- for our chart, I want the frequency.
I actually want the columns.
And then we want a percent going up
from that showing the cumulative.
So I'm going to go over to my pivot table field list.
Drag down the corner.
And make sure I see Revenue2, and click and drag.
Actually, you can drag it anywhere.
Just boop-- there it goes.
Now let's close our pivot table field list.
And we want to change this and Enter.
Change the column with.
Now we can make a chart.
And notice we have two different calculations.
We want to make a column chart from the frequency
but have our percent cumulative as a line going up.
And in 2013, there's a great new combo
feature to have column and a line together.
Insert.
We'll go over to Charts, and here it is.
Now, this doesn't always work.
And you have to hover until you see-- and boom.
There's the one we want.
So I'm going to click.
In earlier versions, we used to have to hack this together.
But here, it's just click a single button.
Now we have to improve this.
Right click.
Hide All Field Buttons.
Click on the legend.
And right click Format Legend or Control-1.
I'm going to put it at the top.
Now let's click on the columns.
That moves the Task pane.
I'm going to change the gap width.
Go over to the Bucket, down to Border, Solid.
And I want black.
Now I actually want to remove this.
But if I delete this, it's going to mess up
this line, so Control-Z. I'm actually
going to do number formatting to hide this.
And this is a pretty cool trick.
Select the axis, Columns, Number.
And we're going to add a custom number format.
And there's a way to show nothing in number format.
And this works in the cells too.
It doesn't matter what you put in the cell.
It will always show nothing.
So check this out--
semicolon semicolon semicolon.
In some of my advanced Excel classes,
we learned that there's four sections to number formatting.
And if you leave all four blank separated by semicolons,
it shows nothing.
So I'm going to click Add.
It's still there.
We retain our line.
But we don't see it, because I want to add data labels.
We can actually close Task pane.
Data labels come from our Plus.
Data Labels-- check.
Actually, we do need to go back to the Task pane, More Options,
because we want to show this above.
So there it is above.
Now, in our last cumulative percent chart,
we ended up formatting with zero decimals.
And it actually went 100, 100, 100.
So I'm going to change this over here--
Number.
And we will select Percentage.
And we'll show one decimal.
Tab.
So that's a little bit better.
Oh, I like that very much.
So we have our actual frequency count
and the cumulative percentage.
Now here when we look at this and interpret it,
we have to look down here.
200 is our upper level.
So 96.5% of the transactions are $200 or less.
Now we do need to add some labels here.
Plus, Axis Titles, check.
I'm going to select this one and Delete.
Come over to this one.
Equal sign jumps up there.
Cumulative Frequency and Enter.
Click on the axis.
Equal sign jumps up to the formula bar.
Revenue Category, and Enter.
I'm not going to add a title.
I'm going to let this suffice.
That is pretty cool.
Remember, what are we doing?
Data analysis with statistical methods.
This is a descriptive statistic, charting and our tabular method
to give us a cumulative frequency.
We actually combine.
We have the actual frequencies here
and the cumulative frequency.
And for a revenue sales pattern, this is pretty normal.
The earlier categories, meaning the smaller transactions
amount, there's going to be a lot of them
and many fewer large transaction sales.
Hey, we changed this.
That looks much better.
Let's come back to our Quant Revenue here.
Close this.
Actually we're going to open it right back up.
Control-1.
Down to Number.
Scroll down.
And we'll change the decimal to 1.
Tab.
Now we can close this.
And actually, hey, look at this.
These are formulas.
If I'm going to be consistent, I better
add some green here also.
Click in the top cell.
Right click.
And we'll use that slightly different green,
because this is a slightly different formula to start off.
Wow, that was a lot of amazing things
about cumulative frequency distribution,
percent cumulative frequency distribution, and the charts.
We did it with formulas.
We did a straight Ogive.
And we even did with pivot tables and combined frequency
and cumulative percent frequency.
Wow, that was a lot of fun.
Next video, we'll briefly talk about dot plot and stem
and leaf.
All right, we'll see you next video.