Excel 2010 Statistics #17 Ogive Chart Formula PivotTable Data Analysis Toolpak Add-in & Pareto Chart

Download file: https://people.highline.edu/mgirvin/E...
1. Ogive Chart and Cumulative Frequencies -- Formulas (1:40 min)
2. Ogive Chart and Cumulative Frequencies -- PivotTable Pivot Table(21:00 min)
3. Pareto Chart (Sorted Column Chart for Categorical Data with Cumulative Percent Line) (28:20 min)
4. Ogive w Data Analysis Toolpak Add-in (30:51 mins)

This is for the Highline Community College Busn 210 Statistical Analysis for Business and Economics taught by Michael Girvin.

---

Closed Caption:

welcome to excel 2010 statistics video
number seventeen
if you don't wanna download this
workbook business to 10
Excel 2010 chapter 2 click on the link
below the video
in this video here we're still talking
about frequency distributions but we
gotta talk about cumulative
frequency distribution a percent
cumulative and the end goal here is to
create a chart like this
or like this this one shows are
histogram this is
our purchases from our to hundreds I am
sample up transactions at a boomerang
website the
columns rips into height but this
represents
the cumulative percentage and here's how
you interpret it in fact why am i
i can assume in here the upper limit for
this class is a 150 bucks it means
ninety percent of the transactions were
for 150 bucks or less
now and this is called a I
oj I've chart this is kind of a combined
chart
histogram and oj I've here's a more
typical
old job i've chart again we get the same
information
right here it's about ninety percent
at a hundred and fifty so this is the
upper limit for that class it means
ninety percent of the boomerangs were
sold for her transactions
were for a hundred and fifty dollars or
less here
good about seventy-five percent of the
transactions were for
a hundred bucks or less so that's and oj
I've chart or cumulative
are that shows a cumulative frequency so
that's what we're going to do here
I'm good she call OGI alright we're
gonna create
are upper lower values based on
increments are labels are frequency and
then are cumulative frequency now we've
done all this already sold to this kinda
fast
we have our revenue data he commits a
50s I so hate the lower end
plus im not a role to sell reference
that locked
I hit the f4 key to lock that's our
offense I copied down one
now here I say
equals the upper from the previous class
now highlight those two cells the work
all the way down so now we have
are upper and lower our label this
we're going to create this label for
char because knows we have our 50's
where it is included for us its clued in
on the
upper its included on the lower and not
upper
so this 100 not included in this class
it's included down here so we're gonna
make a label
and we saw earlier how to make a couple
different types of labels
I miss a that lower and and the joint
symbol
ampersand that shift seven and then in
taxed in double quotes space
up to space and double quote
half-percent so we've joined the lower
with the taxed and then the upper so we
created a text formula
control enter double click and send it
down
now now let's do our frequency again
we've done this a few times so far
we have an upper and lower which means
our criteria
to criteria in the interval
so see Cal if it's criteria range
this column right here control ship down
our at four
comma and then in double quotes we need
our comparative operator great in their
equal to ampersand to join it with the
lower and remember the equal sign for us
is going to be on the low end
comma I can just copy this highlight
control C control be
when I get my
second criteria and in double quotes
less than
and double-click enjoying it with are
upper these are locked
these are not are for no work just fine
control enter double click and send it
down
their cumulative now there's a two ways
we can do this for me a
but the idea is here it's 97 here
it's the addition a bowl for these in
this cell it's addition of all these
so one way to do this is to say equals
this
and enter equals
and click here that's the total from the
previous class
plus that right there
intro letter that gives us a 143 want to
copy it down
we quicken the last cell and hit the f2
key cuz they sure enough
that's very club before we know it works
just fine
there's are cumulative numbers but we
want percent
cumulative frequency well which is
compare this to the whole
this to the total or whole this to the
toll or whole
so we say equals
relative sa reference wanna my left
divided by
are two hundred notice the 200's always
going to be in the last class because
up through the last class we've in we've
accounted for all two hundred
transactions
of course I have to lock that with the
f4 key
patrol enter notice its pre format it
I perform at it if it wasn't it go to
here and copy it out
now there's a rule book shows your
relative cumulative frequency obviously
it's just
without the percentage number format so
100 percent
that means a hundred percent because
we've built are
categories correctly member make sure
the
lower and on the first class in the
upper and
on the last class include all the values
between them
when we get to this class right here of
course we've accounted for a hundred
percent of the transactions
now again the way you you look at this
is ninety percent
it means from the upper end 150 bucks
we sold ninety percent in the boomerangs
for a hundred and fifty dollars or less
you could also say are
ninety percent less than or equal to
a hundred and fifty dollars those are
the transactions now with I want to
label here too
explicitly are state this and I'm gonna
show you
I told her I talk show you to form those
for this I'm also gonna show you two
ways to label this
now for this what I'm just gonna say
revenue fifty dollars or less
here for the cat a girl say revenue a
hundred dollars
or less so we'll build our text for only
equals
revenue
N double quotes so this for not only has
text so far
ship $7 for ampersand so revenue
I cut control and recover damages can
revenue about
but you know I'd actually like to see a
dollar sign there some to click at the
top and
after two I'm in a very carefully click
right there
notice I love to space but I wanna put
my cursor right
next to the double quotes a type a
dollar sign
control enter double click on Send and
I'll catholic nun
now click here in aft to now I need to
finish its member revenue 50 bucks
and last so I have to chill warning
double quotes
space an
Las books
sorry not a hand or last
in double quotes you know if I spell a
word wrong here and enter it spell check
what
catch it so you better f7 charm f7 is a
keyboard shortcut for spell check wire
and
attitude control enter DoubleClick and
Senate and now
it's already easier read/write ninety
percent
had revenue transactions had revenue a
hundred fifty dollars or less
71 percent a hundred dollars or less
tell me show you two alternatives
I'm gonna put the phone over here but I
actually wanna just
copy this right here now watch this I've
highlighted this
I'm gonna copy just the format and I'm
gonna click on this button appear for
Matt painter
notice it's got that paper cyclic when
there is
now I'm also now in a hoppy straight
copy control C
control be now for the label instead of
by the way the dancin answer control
those off by hitting escape
revenues 50 bucks or less another way to
say this woods
say be to say less than our equal to
fifty dollars so I'm gonna create that
label here
dick wilson in in taxed
less on a equal to now the same
situation here
I want that dollar signs from a very
carefully put a dollar sign right there
and then joined that taxed in double
quotes
but the ampersand to
the upper limit there it s
control enter double click on Saturday
11 at work double-click cuz there's
nothing to the left below or to the
right so you have to manually pull it
down
now cumulative frequency
there's my frequency there
now here we do this single formula to
start a
and then a second formula in the column
that one plus the one above and then
copied it down
by the way that a great formula for
running balance in anything
how but there's another way to do this
now this is kinda nice it's night its
simple to do sometimes I forget you know
and I come here and I forget that
there's two foreigners in it
messes me up somehow so this example
give us one formula
to calculate a running total so
in this cell right here we use the sum
function
and I'm in a click on that many to my
left its
that many F-four but I mean hit colon
in close friend to see it now that's a
silly for not
F-four colon F-four ha
look center this not actually arrange a
single cell range which is silly but
thats enter this control enter
and copy it down to feel
now that doesn't work at all but let's
notice something
f6 to f6 those a relative cell
references
F-four a for when the down here they're
both
f6 f6 what if we locked
that one F for but not this one
when I got down here would be F-sixteen
F-four
that means this blue box would expand
as a copy the formula down that's called
an expandable range
so with the cell touching the first
F-four
hit f4 key to lock it those dollar signs
pretty cool control enter
to write it down and now check this out
look at this site
edit mode here the big blue box is to
enter f2 the blue boxes 3
enter after to the blue boxes for
expandable range
alright so those are some alternatives
now let's calculate our chart now
reduced to ways
I'll the there's going to be combined
chart
with both label frequency a and the
cumulative
percent line on top of the histogram
column
and then we'll see how to do an actual
technical came over frequency which is
called an
old job live chart out using XY scatter
Bowl are okay both you know reveal
a the patterns in the data now let's
create a histogram right up front I got
my labels and my frequency this is just
a straight
frequency distribution and histogram
my keyboard shortcut for the default
chart
column instead of going to insert column
on the UES
alt f1 I'm gonna delete this
Italy delete the lines to Lee
click here say I am
histogram and Folge I
tasteful coach I
I wanna at a label down at the bottom
I'm gonna go to lay out
access horizontal below
and the come up here
transactions transaction
revenue in dollars enter
I want to click on the column select
them all
control lawn opens up format cells
change the gap with 20
I either change the color so they very
or come to border colors and I
for the border cuz the blue ones are
kinda hard to see solid line and I miss
like
black if it's not black cock block that
just helps us to differentiate
click OK click over here you can see we
have us now
let's go ahead in add a second
data series to this there's a few ways
we could do this but i wanna see if it
could be kinda cool quick way
highlight control C
click on the chart somewhere and control
V
I copied some cells I clicked on the
chart and when I paste control
be it pace it right into the chart now
that's not what we want we wanna
selected and change
two things we want to change the chart
type and we want to change it to a
second
access now it is very hard
because these are all less than the
number one
and these numbers are you know integers
it's hard to select that so another way
if you're having trouble selecting just
want to lay out
and appear in the current selection you
can select the drop down and select
frequent series frequency and highlights
the columns
or whatever aspect to or element you
wanna miss a series percentage cue more
frequently now that I have that selected
I want to change the chart type some to
go to design
change chart type two-line
with markers thats to help us and help
us it easier to select out now
I wanna format this now
control one is not working for me when I
have this double chart I'm not quite
sure why
so I'm gonna right click and point to
format data series
there's actually a ribbon way up in
format
to do that also and look at this we
wanna change
to secondary axis watch this right now
now this these both these numbers are
plotted of this
but as soon as I say secondary axis it
jumps up and look at that
that is so call that's how to combine a
chart alright
now I would like to
select these add some percentages above
some good ole I'll
data labels and I'm gonna say
above that cut that's kinda cool I'm
going to
I'll belief that here
now one thing a putt putting these here
is that we now have duplicate and
actually for delete this
it it doesn't do any good whole I'll
try that again I think no I don't think
it if you to leave it there got that
doesn't work at all controls the
some actually kill a trick this summer
right click and go to
fought color on the mini toolbar white
to stricken
flat-out trick now the way you interpret
this chart I
is it's always the upper number
forty-eight percent of the sales
were 50 bucks alas seventy-one percent
of the sales
or 100 bucks or less ninety percent
cells were I 150 bucks or less so this
is kinda mashing
two charts together now I'm gonna pull
this out of the way
turn off the dancin and escape scroll
down
right just a little bit I wanna okay so
they're gonna pull this without my move
cursor
right down here now wanna create an
actual XY scatter and the trick is for a
no jive is you wanna
attach the first part of the
line and this is going to be an XY
scatter worry actually used XY scatter
not what markers but with lines
it's gotta be attached to the who are
access at whatever point grew the
smallest points so
for the upper we want upper limits for
our
X value except for the first point which
is the lower limit the first class
now here it happens to be 0 in our age
data in the last couple videos it was
fifteen so here I am gonna say
equals and it's always the lower limit
up the first class that the
X and then obviously if we take the
lower limit
what's the percentage of transactions we
sold for less than or equal to
0 0 percent if it was the age data
we have from before 0 love the per
a transactions were less than 50 so this
is an
extra are
item we have to add above and beyond
these data points up here now the
restive it is straightforward because
the point of this is less than or equal
to the upper limit so the rest to these
are all
upper limits equals a click on the
upper limit noticed that a relative cell
references I copy this down the blue box
will move down
control enter and drag it down
click on the last sell it after you can
see sure enough that work perfect
same with the cumulative frequencies cuz
remember the whole point of this is Ed
any particular
keel over percentages its less than or
equal to the %uh proviso
that's the first when there's a copy it
down the box will move
double click and send it down alright
the only thing different
here is that we now have and anchor
for arlyne on the horizontal access
now I highlight this and this is an axe
why somebody got
insert scatter not that one weird
we don't have our actual raw data points
we actually have
some more or less predicted values here
and I'm in a click this one
nariño on a click on this to Lee
maybe a delete the lines percent
cumulative frequency that okay
I'll you know I've been shown you how to
palsy come up here which is fine you
need to make formulas or
edit your text are you can also if you
get the dashed lines
you can come to the and and type
something
minimal click over here now
all looks add some percentages here
layout
data labels may be above
we can actually delete this if we want
it won't hurt but the
chart because it doesn't have to
both primary and a secondary so Diana
maybe you want it or maybe you don't
control Z if you want it here
too little got a little bit a repetition
here
maybe we get rid of this to leave but
maybe we
at the lines back format plot area:
I don't think I remember how to add the
lines back up
grid line 30 s horizontal grid lines
what's to
major so there we go there I don't think
I've ever done that before
so however you wanted to do your chart
but clearly we can see ninety percent
with this oj I've chart 150 bucks or
less per transactions
that was ninety percent at the
transactions up for
71 percent about 71 percent transactions
for 100 bucks or less so
at San BOJ I right so we did it with the
XY scatter
and the combined chart now wanna see her
do with a pivot table which is
a quick n Easy and also than the data
analysis
adian nomina click up here in the status
on actually zoom in a little bit
by the way you know after Blake column
all the way down when you're doing
pivot tables are sorted the fact that
this chart is hanging over
will not affect click on a single cell
insert
pivot table pivot table escape
the keyboard shortcut is alt NVT I'm
gonna put on a new sheet
if you know it's on a new sheet and you
have a data set set up correctly it's
alt NVT enter
all right now we need to do a
oj I've chart we're gonna do the
histogram
and oj I've together some the drag
revenue down to Rose
I have to group this right click group
I'm gonna start at 0 tab tab three
hundred
tab $50 increments member this
is we saw how to calculate these values
on in an earlier video but here we
haven't so we click OK and their group
now the pivot table as we saw earlier
when it has
decibels like this a gives us these
ambiguous categories we could leave
these like these online lotsa
to go out there looking to work in were
lots of people do because they just know
it up rent
we saw our little trick
if you want to replace the dash with
something like
up to you can use home
its replace so in the fire so
fine select replace or the keyboard
shortcut control
H control each can you find what
a dash tab find what a dash
replace with space up space to space
replace all click OK
now we've group debt and we need two
columns
one and too so we drag
twice down denies we click here in a min
a type frequency notice the default
is scalped so these labels here
will roll labels I'm gonna come up to
design a quickly change I think we've
done that like
10 times already in this class now here
we need to change his calculation we
have a frequency that'll be a histogram
we need cumulative
now there's two things actually I'm
gonna drag this down one more time and
show you
what's possible mentally wanna from cuz
we won't need it for our chart
right click and value field since we
talked about how this has that the power
you can do everything here
if you want cumulative you go to show
values ass
calculation it scroll down
and say runnnn total
in and then you'd have to say what feel
that is it you want the running total in
revenue
I'm gonna click OK knows now we get
perfectly are running told I'm gonna
control
are to see another way to do that is to
right-click
and we have values sunrise eyes by which
are functions or show values ass
absolutely awesome this I ribbon is
this is also open the ribbon to summer
so running total him
ask us revenue click OK I'm gonna taper
cumulative frequency
here I'm gonna say
a percent
I think I'm spell in some words wrong
their frequency spark
K frequency f7
okay to halsey I looks like in the right
all right
it's okay more frequently now we saw we
can either do or die field settings but
you can also right-click show by his ass
you're not gonna believe this this one
is brand new in 2010 percent running
total in
earlier diva 2007 didn't have how this
there some amazing new pivot table
calculations
percent running total and and there it
is ask you which field revenue we want
to click OK
while absolutely awesome now I'm it up
drag this cumulative frequency way cuz
we don't need it
for our chart pivot table will show all
from I just want to affirm
on the clickin any cell here
and he is like ebert record either
insert
column or design
RT options pivot char or key Porter cut
alt f1 that's the default chart
make it this your list out of the way I
miss gonna close
I'm a right-click these in say hide all
field buttons
I'm in a click on this and i wanna move
it to the top
i need ur right click Format or control
one
shown at the top
how about layout chart title above chart
and and histogram
and pro-child I'm gonna go to
access horizontal click up here
transactions dollars
now we still have the problem I love
selecting remember
we saw just a while ago it's hard to
click so you can go up to
to actually ok so they have one format
either one right there in this group see
can select whichever one you want
want to have it selected you can also
right-click it's hard to right-click
there is a right click key on your
keyboard you can right click and go to
change chart type or with its elected
just got to design
change chart type line
click OK click on that and we need to
change it to a secondary access
right click format data series
what's if control one works iraq I don't
know why it didn't work that last time
so all I did was format this second this
line here
put it on the second hour access I
absolutely beautiful looks
click on the columns control one change
the gap width
I'm going to philomena oh no it doesn't
happen here in the pivot chart it
doesn't have it there so we are going to
have to go to border colors
and solid line close
alright I am
so there you go thats a with a pivot
table and pivot chart
every few pivot this or change it the
chart
automatically update one last option are
actually two
to last points on a click on the sheet
paredo
a certain type I of chart
that involve some sometimes involves a
cumulative total
is called operate all chart and all this
is categorical data
right so we have these categories people
were dissatisfied with their boomerangs
and we
we had the reasons why and these were
the categories bad finish unattractive
design did not work broken when thrown
rough wood no instructions
now earlier we talked about the
categories for categorical data
the they really don't I have any
particular
order but when we sort the numbers
then we have we sort it's all right
click sort
z to a then we get the biggest ones on
top
when you do that then we do get some
order here but it's from the heights of
the columns and that's called
are paredo chart and usually a quality
control
you'll see charts like this the item the
cause the most trouble
will be the highest column and to the
left most
right and then we did what we created a
cumulative
perset now I will show you how to
to do this right here I notice that one
away but I'll come back in just a moment
what I put this here
in
our last year we had a cumulative total
and then a separate column for percent
cumulative
but here I'm gonna say equals
some have this colon
close parentheses and lock it that's our
expandable ranged divided by
the Celt F-four so this kinda like our
pivot table we just wanted frequency and
just came later
control enter and double click and send
are drag it down so we get a hundred
percent and now we see
are chart and then we made this chart a
the same way as we may not last couple
charts when we combine them
added this copy/paste change the chart
type
added the secondary access you can see I
I cool
to show you that trip back over here oh
yes it did for change the font to why
right so but this is a certain type
I love are cumulative chart
for the Heights have the column
when you have categorical data and the
frequencies are sorta called a a
to rate of chart one last awesome
feature
I'm gonna come over to dati a
data analysis tool at an
that we wanted to the same thing we want
to quickly create
he hissed a frequency distribution a
histogram
and in OGI now this method is great
when I your in a hurry and you don't
want
anything you do in the cells linked to
are
the original data set like the formulas
would be or even the pivot table
right you we we
saw back in Chapter 00 how to Addie and
I'm gonna go to the data
and we added this data analysis tool
packet so the way you do that it's a go
to File
options then in option to go to ad in
and went down here you have to select
this Excel at an
and click Go and then you have to select
analysis tool box check it and click OK
but once you have it it's Chris gonna
show up there data analysis and the dow
Tour events on the click it
and we'll see if you wander few more
times in this class we get to use it
from the click on histogram by the way
have a little screen shots at the dialog
boxes if you download this
click OK and are in port range
I'm not gonna check this label summer to
highlight just the numbers
control ship down arrow and like we saw
with the frequency function
you have to type bids these are the
upper
ends so again this is a very fast and
dirty method of doing it
but you're stuck with us this this
typeof
arm categorization these are just like
our frequency function we saw Nvidia 15
at the very end to that video
these are the upper ends and the Cal
will include the upper and that's
different than our
countess auction right bins upper
upper ends at each class the data
we want this on a new worksheet actually
let's put this
output range and I'm gonna put it like
right here
all their separate OSA you could do that
cumulative percentage in
chart output so the chemo percentage in
turn up at a click OK
so what do that just like that it
creates are are
frequency and it calculated
these as the upper hands so the operands
and it created in extra category called
more that begins
more than 300 so there's arc help for
frequency that's what we got this gave
us one more
this is what we got with our count yes
if you all
watch the the part about the frequency
function we got that many
here's our cumulative a purse percent
that we got to in our formulas
arm and it adds an extra mic because
that Cara here here's our histogram
notice the people who program this in
some people
I'll don't actually put maker columns
touching
right they just assume hey I conceive
the
patterns in the doubt out what
regardless if they're touching or not
but
having them touch is a visual way of
saying these
are there are no numbers that can fit in
between these
I'm a click on this control one put at
the top
you can do anything you want to this you
could actually absolutely fix this
control one
slight the gap border solid line
black so you can do whatever you want
once you got it
these numbers are not connected their 16
Iran the data analysis is just dumped in
the cell but
pan at it's fast and easy now 4
this class for testing on all three
methods are open for testing usually
what I do is I see if you can do one of
each do it with her some formulas
do with a pivot table into it with the
data analysis
tool pack at it alright that's a lot
about
cumulative frequencies percent
cumulative and 00 jive charts when we
come back in our next video actually
about
one for short video that talks about the
histogram shape
and skew alright see you next video