Welcome to Excel Magic Trick number 1,397, part 3.
Hey, if you want to download this Excel workbook file
so you can follow along, click on the link below the video.
Hey, we've got a great video here.
We want a single formula to calculate
percent frequency running total for irregular increments.
And then we want to see how to create the appropriate chart
using an x, y scatter.
Now we have been doing a bunch of running total videos
in a series of videos.
And this is our current video.
Now here's our data set.
And the column we're interested in is Quantity.
Now in this video, unlike the past videos,
if we look at the categories with an upper
and lower limit for this category,
there's an increment of 75.
75 here, 50, 100.
So the increments for each category
with an upper and lower limit are not the same.
Now the last few videos we've been doing,
the increments were the same.
So in this video, we want to see a single formula
to calculate percent cumulative count of quantity.
And then we want to see how to use the x, y scatter to create
the appropriate chart.
Now let's go over to the sheet 1,397 part 3.
Now unlike the last couple of videos,
we're going to have a single column
to calculate our percent cumulative count or frequency.
In our last couple of videos, we actually
calculated frequency then running total, then percent
running total or percent cumulative count.
Now it's going to be a relatively easy formula.
Each formula, as we copy down, needs
to be looking at the upper limit for this category
and saying hey, please count all the items from the Quantity
column that are less than or equal to the upper limit.
Well, any time we're counting with one or more conditions
or criteria, the perfect function to use
is the COUNTIFS function.
Now our criteria range, we need to look at the whole quantity
column.
So I'm going to click on the top cell, Control-Shift
down there to highlight all the way to the bottom.
F4 to lock it and jump the screen back in view, comma.
And a single condition or criteria will work.
We need to ask the question hey, are you less than
or equal to in double quotes.
COUNTIFS, SUMIFS, and those kind of functions
require that our comparative operators in double quotes.
And now we join it using the ampersand, Shift-7
to our upper limit.
And that's it.
Close parentheses.
That's it for the cumulative count.
Let's just enter this--
Control-Enter, and then double click and send it down
and see that part of the formula is working.
I'm going to go to the left cell and F2.
And I see that the cell references are working fine.
Now that's the cumulative total.
So when we get right here, if I'm counting on my fingers
over in this column, I found 331 transactions that
had a quantity of 300 or less.
Now we need percent.
So come to the top cell F2.
And I now need to simply divide it by the total count.
So I'm going to use the Count function which counts numbers.
Click on the top cell, Control-Shift down,
now F4 to lock it.
And that will be our denominator, the total count,
close parentheses, Control-Enter, double click
and send it down.
There are our decimals.
We can apply percent number formatting, Home, Number Group
drop down, Percent with two decimals or Control-1 Percent.
It's got a automatic two decimals.
My OK button is highlighted by default. So I simply hit Enter.
And there you go.
One formula.
We jumped straight to percent cumulative count of quantity
based on these upper limits.
And now instead of making a statement
like 331 of the transactions had quantities
of less than or equal to 300, we could
say 80.93% of the transactions had a quantity of 300 or less.
Now last couple of videos, we simply
highlighted what we wanted on the horizontal axis, either
the upper limits or the actual category label
here, and then our percents.
And we went up to Insert and used a line chart.
But we cannot use a line chart.
The line chart assumes that you have
a category along the horizontal axis that
has the same increment.
Now when we say category, we could also
be using words or these text items over here.
And categories on a line chart are always spaced evenly.
So we can't use that.
Line charts are for when you have a category and one number.
We actually need two numbers, because we
need on the horizontal axis for the x-axis
to go from zero to 75 to 150 to 200 in irregular increments.
No problem.
We can use the x, y scatter, Insert, over to x, y scatter.
And there we go.
I'm going to use that one with the line.
Now also unlike our last couple videos,
I named the y variable here, the actual chart title.
And anytime you do that, it shows up as the title.
That is the title that I want.
Now let's fix this up.
I want percentages on each marker on my x, y line.
So I come up to the green plus, and I'm
going to check data labels.
I also want an axis title or label on the x-axis.
So I click that black arrow, and I say primary horizontal.
Immediately, I see that it's got a solid line.
And I want to link it to the cell.
So I type in equal sign that jumps me up to the Formula bar.
And now I can link that label to the name for the x variable.
Upper limit for quantity, and Enter.
And there we go.
Now I have chart junks.
I have percentage here and percentage here.
So I'm going to click on the vertical axis,
or y-axis, Delete key.
I'm going to try to click on the horizontal lines.
I see those translucent circles of delete.
Click on vertical lines.
I see those translucent.
And you don't want to do that because that's the whole chart.
You want to see those translucent circles and delete.
Now I could leave it right here, but what I really would like
is I would like to have the exact x value right there
and there.
Not only that, but I'd like a line.
Now you would think we could select
the horizontal axis, or x, Control-1
to open up the Task pane.
And I've searched for years through here thinking,
that I'm missing something.
How can I link this x axis to the x values?
I mean, this is number for me, and that's not
going to work, labels, markers, none of this works.
If anyone knows how to link it automatically through the Task
pane, let me know.
No problem.
I'm going to trick it.
Notice that this is an y scatter chart.
So if I had the x values, and the y value is always zero,
I could simply plot another line.
And then just like this line, I could use data labels.
First thing we need to do is I need to get rid of those.
So I come over to Labels and click the dropdown.
I'm going to say None.
Now I've already added--
well the x's are already here.
And I already added a column of our y's.
With those two columns of x and y's,
I can Right click the chart, Select Data,
or go up to Design, Select Data.
This is the real power to charting.
Anytime you have a chart and it's not working right,
we can change it.
We have the power with his dialog box.
These are the y values.
These are the x values.
The words series is what Excel uses
for numbers, and then category, that's mis- kind of labeled
here, because this is really numbers also.
But that's what we'll use for our horizontal or x-axis.
I'm going to click Add.
And this dialog box is pretty polite, the series name.
I'm just going to type it here, Labels, Tab, the x
values right there.
Make sure I go all the way down to row 10.
Now Tab, and make sure and hit Delete first.
That little forced array there sometimes causes trouble.
And now I'm going to highlight my y values.
You can already see it working down there.
Click OK.
Now let's click right on the labels.
Those are the y labels.
That's the default.
We come up to our bars here, Label Options.
I'm going to check x, uncheck y.
That's looking good.
We'll move them below in a bit.
Now let's come down here and I want to remove the color.
Fill Bucket, No Line, Marker, No Fill.
Actually come back up here to Marker option.
I'm going to use built in, and I'm going to put an x.
I don't like that orange, so I'm going
to come down here and change the color to black.
I can see my x's over there are working.
Now I can click on the labels.
Come over here to the series.
I should've done this before--
Below.
That's still not quite good enough,
so I'm going to come over and try and select the whole chart
with that solid line.
Pull this up somewhere, click the label, and drag it down.
Now let's click on the axis here.
And I want to come over and change--
so zero, that's fine.
Tab, Tab, and we want 500 here, Tab.
That way we have the percentage going from side to side.
Now all we need is our Error bar.
So I'm going to come up to the green plus Error bars.
Black Triangle, More Options.
And I definitely like the fact that I named these clearly.
I definitely want that one, so click OK.
We want Minus down here to Custom, Specify Value.
Very carefully, we want to get rid of that little array
delete, Tab.
We need to get rid of that Delete.
If you leave it in there and sometimes try to highlight,
it messes things up.
So deleting it before we highlight
the actual negative Error bar we want to--
and we want to make sure we go all the way down to row 10.
Click OK.
Now I didn't pay attention there.
I didn't see which step got rid of our chart title.
I'm going to click the green plus chart title, bummer.
Equals sign jumps me up to the Formula bar.
There it is.
And Enter.
Click in the chart background.
Come over to the Paint Bucket.
And I'm going to say some solid fill.
And then you can pick a light color.
And I can click on the outside edge, point to the corner,
click and drag.
And there is our x, y scatter percent
cumulative count of quantity with our lines
and our irregular x is listed down on our x-axis.
All right.
So this video, we saw how to create this x, y scatter
for percent cumulative count or frequency.
And we saw how to create a single formula for percent
to running total or percent cumulative count.
Now by the way, if you were doing revenue,
you could do the same formula, but you SUM
IFS in the SUM function here.
All right.
That's continued fun with running totals and percent
running totals.
Next video, we'll finally get to some DAX formulas
to do running total.
All right.
We'll see you next video.