Welcome to Excel Magic Trick number 1,397.
Hey, if you want to download this Excel workbook,
Excel Magic Trick 1,396 to 1,397 so you can follow along,
click on the link below the video.
Hey, in this video we got to talk more
about running total calculation and percent of running
total calculation, and the Ogive chart, also known as a percent
cumulative frequency chart.
Now in our last video, 1,396, we saw
how to do these calculations with a pivot table.
In this video, we're going to see
how to calculate the actual running total and percent
running total with formulas.
And then we're going to see how to create the chart.
Now back in the pivot table video,
we saw how to do running total and percent running total.
And the pivot table is much easier
than the formula solutions.
Now the reason that you'd want to do formulas
is if you want your report linked to the source data.
So when the source data changes, it instantly updates.
Then that's a good use for formulas.
Not only that, but if you have a series of calculations
made with a pivot table and you try
to highlight just two of the columns to make a chart,
that doesn't work when you're using a pivot table.
But for formulas, we can make whatever formulas we want
and highlight whichever columns we want, and make a chart.
Now last video we talked in great detail
about when you can have a running total or a percent
running total.
And in both examples, we did the last video,
we had a lower and upper limit.
And we need to first count for, in our case,
how many quantities fell between the lower limit 51
and the upper limit 100.
Then we need our cumulative total or running total
and percent cumulative.
All right, let's go over to the sheet 1,397 formula
and see how to do this.
Now the first thing I did was I looked through the quantity
column and calculated the min and the max,
because if I'm going to create categories
with the lower and upper, all of the categories
together better cover all of the quantities that we have.
Now I have a little assumption table or start
in our increment.
So equal sign, we'll start at one, Enter.
And I always need to add 50.
So equals relative cell reference one above,
plus the increment.
And I'll use the F4 key to lock it.
Control-Enter to put the formula in the cell.
And then point to that fill handle.
And when I see my crosshair or angry rabbit,
I can click and drag.
So there are the lower limits.
Now we need to get the upper limit.
Equal-- and I'm using my arrow key, Left arrow there.
I'll take whatever the lower is plus--
whoops.
The increment-- would give me one too many.
I'm still going to hit F4, but then subtract one.
I could've put 49 over here, but I'll
leave it as an increment of 50.
Control-Enter, and now I can double
click with my angry rabbit and copy it all the way down.
That knew how to copy all the way down,
because it kept looking.
And when it saw nothing, it stopped right there.
Now if we wanted a category label in our chart,
we could make a text formula.
Equals sign-- I'm going to click on the lower--
and now I need a dash, and then I need the upper.
So to create a text formula and join things,
I use the ampersand Shift-7.
Now I need a dash.
I can't just type a dash.
I have to make sure and put it in double quotes.
So double quotes, space, dash, space, then I end double quote.
And there's three characters within double quotes.
So every formulas I copy this down,
will have a space dash and space.
And then I join that text to the upper.
Now I Control-Enter, and double click and send it down.
Of course, you could see that this is considered text,
because it's a line to the left.
These of course, are numbers aligned to the right.
Now when I get to frequency, there's
actually two different formulas we can use.
And I'm going to show you both of them.
The first one is anytime we're counting anything--
and for us, we have two conditions.
I need to look through this column and find any quantity
and count it if it's greater than or equal to the lower
and less than or equal to the upper.
Any time we're counting with conditions,
we can use the COUNTIFS function.
Now criteria range-- that needs the entire column of quantity.
So I'm going to click on the top cell.
Control-Shift Down arrow to jump all the way to the bottom.
F4 key to lock it, putting the dollar signs in,
so as I copy down it's locked.
Notice F4 jumps the screen back up too.
Comma, now criteria, I need the comparative operator
greater than or equal to, and I need the lower limit.
Now the way COUNTIFS works is you
have to put your comparative operator in double quotes.
So double quote, greater than or equal to.
Now there's no such thing as a single operator,
that means greater than or equal to length there is in math.
You have to type two characters, and then end double quote.
Now I need to join in that to using the ampersand the lower.
Now notice, the greater than or equal to
will be the same all the way down,
but that relative cell reference will move.
So each time the formula copies down,
it will see a new lower limit.
That's the first condition.
Comma, criteria range too, I need the same column again.
Click on the top cell, Control-Shift Down arrow, F4,
comma, now criteria too.
Now if we're asking the question are
you greater than or equal to the lower,
it also as an and logical test, has to be less than
or equal to the upper.
So in double quotes less than or equal to end double quotes.
And join it to, and I'll use my Left arrow
to get as a relative cell reference, the upper limit.
And that formula will work.
Close parentheses, Control-Enter, double click
and send it down.
Now I'm going to go to the last cell and hit F2,
verify that all my cell references are working.
I should have done that over here too.
F2, that's working there.
F2, that's working.
F2.
So those are all working.
Now backup here.
F2, that was option number one COUNTIFS.
Now one advantage of COUNTIFS over the frequency
function that we're going to use here,
is that you actually have complete control
over the comparative operators.
You could put equal signs or not have the equal signs, which
is sometimes the case when you have not an integer value,
but a continuous variable.
Sometimes you have the equal sign
on one side, but not the other.
So COUNTIFS gives you complete control
over the comparative operators.
But if you're really counting the frequency between the upper
and lower limit, and you have the upper limits
for each category, then using the frequency function
is going to be a little bit easier.
Now there is a twist.
The frequency function, you actually
have to pre-highlight all of the cells
before you enter the frequency array
function into the active cell.
And then we're going to have to use
a special keystroke to get this special array function to work.
Now active cell at the top, that's the light colored cell.
I type equals FR.
And there is our frequency function.
Now frequency function, all it needs is the data.
That's the whole quantity column, and then
the bins array.
Those are the upper limits.
So I'm going to highlight the data array.
Click on the top Control-Shift Down arrow.
And I'm going to use Control-Backspace
because we do not have to lock those cell references
with the dollar signs.
When we enter the frequency array function
with a special keystroke, the actual same exact formula
will automatically go into each cell, comma.
Bins array-- these are the upper limits.
Now when you're using frequency in bins,
these upper limits will always be included.
And most of the time, that's fine.
Cases where the upper limit is not included,
then you can't use the frequency function.
But that's it.
When we close parentheses and enter this,
it will automatically calculate the same numbers.
Now there's a number of different array
functions in Excel, frequency, line S, transpose function.
And these functions deliver multiple answers
simultaneously.
So if you want those multiple answers
to go into multiple cells, you have
to use the special keystroke, Control-Shift and Enter.
Now I'm going to hit Enter and just show you
that it's not going to work.
It happened to calculate the first category, but not
the rest.
So very carefully I'm going to hit F2.
And now I'm going to enter it properly with Control-Shift
and Enter.
Now Control-Shift Enter is us telling Excel
that that's a special array function.
Immediately, you have to look up to the Formula bars.
Those curly brackets are automatically put in by Excel.
And that means Excel understood that this
was an array calculation.
All right.
So we have either frequency function or COUNTIFS
for calculating frequency between an upper and lower
limit.
Now we can calculate cumulative frequency.
Now there's a couple we could do this.
We could actually point this cell to that.
And then in this cell, we could say
please add the latest to the one above.
That would be appropriate if you have a really large data set.
But this is a small data set.
So I'm going to use the Sum function
with an expandable range.
Now I'm going to click in cell I2, Shift-Colon
and close parentheses.
Now very carefully, I'm going to put my cursor in the first I12
and hit F4.
What I've done is lock the first cell in the range.
The second one, 112, because there's no dollar sign there,
that 12 will move to 13, 14, 15.
So it will be an expandable range as we copy down.
Control-Enter, double click and send it down.
You can go to any particular cell and hit F2.
You can see sure enough, that is an expandable range.
The 12 is locked, but that number, in this case, 16,
is not someone to hit Enter F2.
It keeps moving and expanding.
There is our cumulative frequency.
Now of course, when we get to the last category,
that's the total for everything.
Now percent cumulative-- equals sign,
and I'm going to Left arrow divided by--
and you could either click on the last total cell here
or you can do it with keyboards, Left arrow, Control-Down arrow,
and then F4 to lock it.
Notice the numerator will be each one
of the cumulative totals, always compared to the total over all.
Control-Enter, double click and send it down.
And of course, the last one is 100% because 409 divided by 409
is 1.
Now I've already applied the percentage number formatting
over here on Home, using the dropdown
percentage with two decimals.
Now we can create our chart.
Now I have done other videos on this Ogive chart or percent
cumulative frequency.
And in other videos, I've done the x, y scatter
because we want to attach the line to the x-axis at the zero
point.
But here, I don't really care about that.
I just want the categories listed along
the horizontal axis, and then these percentages as a line.
I'm going to highlight the category column.
Using the Control key, I'm going to come over and get the label
and our cumulative percentages.
Now as I mentioned earlier in this video,
if you try to do this with a pivot table,
this particular trick won't work, because the pivot table
will make a chart based on all the numbers in the pivot table.
If you have a single category and a percent
cumulative frequency or a percent running
total calculation as a single pivot table, then of course,
you can make a chart.
Let's go up to Insert.
I'm going to use the line.
Line is for when we have a category along the horizontal
and one number along the vertical axis.
And I'm going to select this one, Line With Markers.
Now we're going to do a bunch of things to make this look cool.
First thing is I'm going to click on the chart title.
And I want to link it to this chart title I have over here.
So to link it using a formula, make
sure that the solid line is around
the outside of the chart title.
Type an equal sign, which jumps you up to the Formula bar.
And then click on the cell M6.
We can see our formula up there, and enter.
Now normally, in a business chart,
you probably wouldn't have a title this long.
But to be explicit of what this chart is showing,
I said percent of transactions that had a quantity less than
or equal to the upper limit.
Now I want to come over and I want the percentages
not on the vertical axis, but actually right on the line.
So I come to the green plus.
And I'm going to check Data Labels.
Now if I click back in the chart, now we have chart jump.
I have percentages here and here.
So I'm going to click on the vertical axis.
With the solid line selected, I use the Delete key.
Now as I'm looking at this, sometimes this
is an appropriate label along the horizontal axis.
But I'm thinking so that it's less cluttered,
I want just the upper limits.
So somewhere in the chart I'm going
to disconnect this, because that axis is looking
at that category label there.
Somewhere in the chart, I'm going to Right click,
select Data, or you can go up to the Design Select Data.
This is the real power for charting.
We have complete control.
We're allowed to add a new series.
Series is the name they use to describe the numbers.
We can edit the series or numbers or remove.
Ours is fine.
Now I want to click and edit the horizontal category,
so I click Edit.
And now I'm going to highlight the upper transaction quantity.
Click OK, click OK.
Now by itself, that is not a good axis
because you're like, what are those numbers?
Well, I mean we might get a hint from up here
that it says the upper limit.
But I want to label down here describing
exactly what this is.
I'm going to come up to the plus,
and over to the axis titles.
Click that little arrow and say Primary Horizontal.
With the solid line, I'm going to type an equal sign, which
jumps me up to the Formula bar.
And then I'm going to click on the label here, Upper
Transaction Quantity and Enter.
Now we know exactly what those numbers mean.
Now those horizontal lines are fine.
That's a matter of preference.
I'm going to click on them.
I can see that they're selected.
The little dot's on the outside.
And I'm going to delete them.
I'm going to try and add a back ground color.
Right click.
Format chart area, or you can actually
click any element in here and Control-1
will open up the Task pane for that particular element.
So Format Chart Area, there is the fill bucket, solid fill.
Now I think I had already selected this color.
That wasn't the default, but I selected that one right there.
Now while I'm here, I'm going to actually add
a black border to the outside.
So under here, under Border, I'm going to say Solid Line
and make sure black.
Now I want to click on the chart title,
Control-B to make it bold.
Click once on the percentage labels.
Control-B, click on Access, Control-B,
click on horizontal axis, label Control-B. Now that's
looking pretty good here.
And now we can clearly see the meaning of this chart.
75.8% of the transactions from this data set
had a quantity of 250 or less.
65.5% of the transactions had a quantity of 200 or less.
Now to emphasize the connection between the upper limit here
and this percentage, I want to add a line
from each one of the percentages down to the upper limit.
I'm going to come over to the plus.
And there's something called error bars.
Now I want to click on this black triangle
to expose and say More Options.
And over here on the Task pane we see Format Error bars.
Now I only want minus.
So it only comes down below the line.
And check this out.
We don't want Percentage, Standard Deviation, Standard
Error.
We want Custom.
And over here Specify Value.
I click, and it allows me to put values in here.
Now we do not want positive.
So with it highlighted, hit the Delete key.
We actually want to do the same thing here, highlight
and delete negative error bars.
Now I can simply come over and highlight.
And notice the proper height will be our percentages.
So our Percent Cumulative Frequency,
those will be the heights of those error bars.
Click OK.
That is pretty cool.
Now I want to change the color on those lines.
So I go over to the Paint Bucket.
And for Line, I'm going to select something
like dark blue.
And there we go.
There is our Ogive chart or percent cumulative frequency.
And of course, 100% were for 500 or less.
All right.
So in this video, we saw how to with formulas,
create the lower and upper limit, a category.
We saw how to calculate frequency
with COUNTIFS and the array function frequency.
We also saw SUM function and expandable range
for cumulative frequency, and then
percent cumulative frequency.
And our line chart with some formatting and some error bars.
All right.
That was a lot of fun with running total and percent
running total.
In our next video, we'll see how to do
running total percent running total
with DAX formulas in our PowerPivot data model.
All right.
We'll see you next video.