Excel 2013 Statistical Analysis #5 Data Categorical, Quantitative, Nominal, Ordinal, Interval, Ratio

Download files: http://people.highline.edu/mgirvin/excelisfun.htm
Topics in this video:
1. (00:43) Categorical Data vs. Quantitative Data
2. (02:00) Scales of Measurement (Levels of Measurement): Nominal, Ordinal, Interval, Ratio
3. (14:42) Cross Sectional Data vs. Time Series Data
4. (15:48) Graphical Display of types of Data
5. (16:22) How to Enter Data into the spreadsheet and use the Auto Complete (Auto Text) to your benefit
6. (18:50) How to create a new Excel Workbook to do your Homework from the Textbook

---

Closed Caption:

welcome to exhale 2013 statistical
analysis video number five
hey if you want to download this
workbook and follow along click on the
link below the video in this video we
want to talk about different types of
data and in particularly want to talk
about the scales of measurement for raw
data see we have different types of raw
data and with different types of raw
data come different abilities different
levels of information in different
calculations that we can make so there's
four categories there's nominal ordinal
interval and ratio now we'll talk about
those four look at some examples
there's also two broad categories for
the variables we're going to use
categorical variable notice it says
category so usually it's a word or a
name or some text occasionally it's a
number like the number one represents
iphone number 2 represents android the
category categorical variables just
means there's some category and then
quantitative variables quantitative
means number now when you have
quantitative variables there's going to
be two different kinds discreet which is
counting there are gaps between the
numbers and continuous continuous is
like weight and height money is
continuous it depends on the measuring
instrument whereas discreet is counting
and there are gaps between the numbers
like my County 123 are 1.2 1.3 1.4
continuous there are no gaps between the
numbers because it depends on the
measuring instrument right if you're
weighing something
it depends on the measuring instrument
it could be 1.2 pounds or 1.2 3 pounds
or 1.2 348 pounds
alright quantitative categorical let's
go up and look at our one two three four
levels of measurement now I have the
complete notes here i want to go over to
the sheet scales of
measurement now we have a data set here
and our primary key filled with elements
are student named so we have three
students and look at this we have eight
field names are variables phone i call a
ranking class rating of teacher body
temperature s80 score score on finals
from 0 to 100 and money in the bank
what's great about these eight variables
that we have examples of nominal ordinal
interval and ratio scales or levels of
measurement now nominal and ordinal
going to be categorical interval and
ratio or quantitative let's just start
with nominal we have phone it's just a
word or category and we can't really
rank them now you may be thinking we'll
wait a second i can rank the iphone over
the android and samsung but what you'd
probably be doing is ranking costs which
would be a racial level variable or
satisfaction that would be an ordinal
level so in and of themselves the names
have no rank same with I color or car
like Toyota Honda now the only thing we
can really do with this kind of variable
is simply count now we start with
nominal when we jump up to ordinal we
have a rank whereas with Dom we don't
have a right here we have a rank the
thing about or know though is we could
have categories bad
ok good and excellent but you really
don't know the distance between them
when we talk about interval like body
temperature or s80 score you actually
know the exact distance there's a fixed
distance between 1509 and 1510 right
it's one but here you don't really know
the difference
so the characteristics of ordinal level
data is that there's a rank or an order
but you don't know the distance between
them now ranked in the class don't get
tricked by that think about 12 all the
way up to 30 ranking but the rank is
probably based on grade or score
something like that the rank in and of
themselves is simply ordinal level now
whereas or no we have rank and you don't
know the distance between each rank
now interval will have a rank and we
know the distance between each rank so
body temperature s80 score
not only that put 0 if zero exists in
the scale is just too . on the scale
think about Fahrenheit or Celsius
Celsius 0 means freezing Fahrenheit
means 32 degrees below 0 so 0 is just a
point on the scale and guess what
SI t-scores body temperature and other
interval scales of measurement you
cannot divide numbers you can take an
sa80 score and do division and say this
student is twice as smart as this other
student has zeros not even a point on
this scale same with body temperature it
doesn't mean anything because 0 is just
a point on the scale now one interesting
point about temperature if you're
measuring molecular kinetic energy you
could use something called Calvin where
0 means there's no molecular kinetic
energy then that is certainly a scale
but that's a ratio level measurement
because you can do division and in
Kelvin temperature and say one
temperature is double another one so
with interval we can rank with no
distance 0 if it existed just a point on
the scale and ratios not ok now we jump
up to ratio and guess what it's all the
same characteristics is as interval but
now you're allowed to do division and 0
means that nothing exists so for example
if you have a scoring system 0 to 100 to
zero means you didn't get anything right
over here for an essay t-score IQ score
600 doesn't mean you don't have any
smartness right but here on this scale
here 0 to 100 0 means you got no points
money in the bank same thing 0 means you
don't have any money so ratio you can
rank it you know the distance between
each 10 means nothing exists and ratios
ok you can divide to bank account
amounts and say this one's twenty-five
percent more than this other one or
double this other one now let's go over
to the sheet scales of measurement to
and look at some more examples
we have some datasets here for nominal
ordinal interval and ratio will start
with his nominal one
hey we have student named their phone
preference and notice that sometimes
with nominal data you can designate the
use of a number to represent whatever
category is so one is going to be iphone
one is going to be iphone 6 will be
sprint it doesn't mean that you can do
many calculations it's still nominal so
the only thing we left to do is count
well let's go ahead and see if we can
count i'm going to use a pivot table
click in a single cell alt envy and
we're going to see if we can count all
of the iphones and motorola's accelera
click existing i'm going to click on
location and get dangerous put it in e4
click ok i'm going to drag this down
phone type 2 rows phone type remember if
we drag a nominal or text field down to
values it defaults to counting so it's
looking pretty good i'm gonna change the
row labels design report layout and
either outline or tabular i'm going to
use outline
hey let's do a second pivot table and
see what happens if we try to count this
number field never you may think it's
some other variable besides nominal but
it's not all we can do is count alt NV
existing location now I'm gonna get
really dangerous remember if i put a
pivot table and sell a 13 and click ok
if I start pivoting this one up here it
may replace and destroy this time being
pretty careful now i'm going to put
phone type number and then drag phone
type number down two values and look
what happens it's adding that's not at
all what we want
whereas with a text field it defaults to
counting if it's a number field of
defaults to some but pivot tables are
awesome and easy you just right-click in
the value area summarize values by and
I'm changing it to count and just like
that we get the exact same listing
nominal level we're stuck with counting
i'm going to click outside the pivot
table to make that field let's go away
now here in this data set we have
ordinal level we're ranking and I want
to do two examples are going to do a
formula hair
we have all of our category so this is
for of students rating a professor i'm
going to use the count ifs function to
count with one condition
so the criteria range control shift down
our f 4 comma and the criteria that's
going to be left arrow one cell to my
left that's a relative cell references
that copy down it will pick up each one
of the conditions or criteria
control-enter double click and send it
down so you can see down at the bottom
poor man look at that a hundred and
seventy-seven i'm doing a terrible job
teaching better try and do better
now you can choose to do a number to
represent these categories right we've
all seen this in surveys one is going to
represent poor tues okay three is good
etc so we can totally use counts for
this too and i'm going to get the
criteria range control shift down arrow
f4 to lock it
comma and then left arrow to put my
criteria in the number for its going to
look through here and count all the
number for absolute relative reference
control-enter double click and send it
down and we go to the bottom and you can
see f2 we got the same exact numbers now
there's something interesting here
because this is a rating we can come
down and average the whole column
average and we could highlight the whole
column control shift down arrow and
shift enter to put the formula in the
cell and move up so we can see we got an
average there and that's okay here but
with ordinal the nominal you're very
limited in the types of calculations and
in some of the charts you can do you can
see in both cases here we could do a
column chart where the columns were
not touching or a pie chart for that
matter one last important thing
especially about ordinal also about
nominal to see that there's no rank here
or what we got that but there's a rank
here but it's important to realize we do
not know the distance between each one
of the categories when someone selects
excellent or good or ok another person
selecting excellent good okay might have
a completely different set of criteria
we really don't know the distance even
for these numbers when you select one
two three or four one person's for maybe
another person's to so even though we're
ranking here we really don't know the
distance between each category all right
now let's go look at interval and ratio
we have interval level Fahrenheit
Celsius over here we have racial level
temperature scale Kelvin we also for our
interval have s80 scores now I've
already done the calculation here I
wanted to count between these categories
so i used the countess and we saw how to
do this in video number one will see
this in chapter 2 again but I counted so
it's certainly all right two-count
interval data or racial level data for
that matter so you can come over here
and we're counting this is amounts in
the bank so I'm counting how many bank
accounts had between a thousand and two
thousand how many had between zero and
one thousand so both for interval and
ratio we can certainly count and we can
average let's go ahead and see if we can
get the average s80 scorso equals
average
and control enter it's perfectly alright
to average same goes for racial level
average i could certainly average all of
the amounts in the bank
control-enter for this set of customers
the average balance with 1031 and five
pennies over here we could certainly say
the average s80 scores for students 12
13 is 141 5.46 now here's what we can't
do
here's students six score and students
10 score we can't simply divide the two
and say student number 10 is twice as
smart as student number six
there is no zero on this scale
similarly we can make our percentage
calculation hey we're going to take the
second one / the first one and subtract
one to get the percentage change you
can't say hey student 10 is a hundred
percent smarter it's that zero thing
that scale right there doesn't start at
zero and zero doesn't mean the absence
of smartness but overhear were no
problem worked were allowed to take fans
amount and / fills and say yep fam has
twice as much as Phil not only that but
we can calculate the percentage change
hate we take the second one / the first
one and subtract one and boom BAM as a
hundred percent more than Phil scroll
down here and you can see some notes
here ratio is meaningful because 0 means
that nothing exists over here it's
exactly the opposite so scales of
measurement or levels of measurement
ratio interval both quantitative
variables ordinal and nominal both
categorical variables
now I want to go look at a couple last
things let's go over to the sheets c/s
data and I want to differentiate between
cross-sectional data that's data
collected at the same or nearly the same
point in time and this TS time-series
data data collected over time
so here is our example we took the dates
and the adjusted close and we clearly on
a chart see the date here and we're
seeing what's happening over time that's
time series data now if i go back over
to c/s data this is actually went to
yahoo to download the data this is what
was there we were comparing google
facebook yahoo and the industry with a
bunch of metrics Evan operating margin
earnings per share price earnings this
is all at a single point in time I think
it was December twentieth and this is a
cross-sectional set of data a single
point in time are nearly the same time
and we also have time series data now i
want to go over to the data diagram to
summarize what we've talked about well
there's that raw data we're going to be
dealing with categorical that means a
category quantitative that means a
number but with categorical we can have
non-numeric or numeric and then we have
either nominal or ordinal and under
quantitative well it's got to be a
number and it's interval or ratio and 0
is the difference between these 0 means
that we have nothing like in a bank
account when you have 0 money whole what
a bummer
hey one last thing let's go over to
entering data in Excel now this is your
homework problem in chapter one and i
want to show you you're gonna go read
the textbook and just like i did i read
the textbook i typed out the field names
and typed out this data but I gotta show
you something that's going to be awesome
but but you also have to be careful of
it
notice this is the picture from the
textbook for this particular homework
problem i'm going to type windows
ok and then android and then I OS but
watch this this is the fourth record
here that I'm entering i'm entering the
operating system as soon as i type a w
this is auto fill
now it's trying to be really polite it's
looking above the way this is program is
that it's looking above because there's
only one
word that starts with W it immediately
gives you this option now think about
this if we're entering lots of raw data
that is really convenient so W enter w
enter a enter w enter the one caveats is
it if you had a different operating
system which we don't hear as soon as
you
let's say that the name of the system
was way cool as soon as i type of w it's
looking above so you gotta be careful
and just keep typing way cool
now watch this when i type a w nothing
comes up a lot when I typing and I it
knows to go to Windows i'm going to
click escape when i type wa now it knows
to go to way cool so that is an
autocomplete feature that you gotta be
aware of it definitely helps in the long
run when you're entering raw data again
part of the whole point of this class as
we are learning statistics but we're
always dealing with data so a enter a
enter same with over here i enter i
enter i enter key enter i enter and
enter oh there's no end yet so I better
type it out so the first time I bump
into something obviously it's not going
to be there because the way it's working
as it's looking above finding anywhere
that starts with them now when i hit
enter and type in and and by the way if
its capital above and you type a little
one
it's going to capitalize it so don't
even bother to hit that caps lock key
alright so now your job after watching
all these videos is to do the homework
problems that are listed in the syllabus
that means you have to open up a brand
new Excel workbook you know file new or
control and and i'm going to give it a
good name something like and I'm not
going to use file save as
oh because then you have to go save as
and all then you have to go to computer
then you have to go to browse all of
that to open up this dialog box i'm
actually going to use the keyboard for
save as
f12 i love it i skipped over all that
extra steps there now i'm going to name
this something smart something like that
homework your name
chapter one business to tend you should
have a new workbook for your homework
for each chapter and don't forget I know
some of you will and you'll try to hand
it in its not handed in four points it
is just for you to practice so you can
do well on the quizzes and tests now i
want to remind you we can jump to the
front of the work by holding ctrl and
click and here's the topics these are
hyperlinks the and the keyboards i've
updated them so these are all the new
keyboards that we've seen in this field
so each week when you download your
workbook for whatever chapter it is i
will update the keyboard sheet
all right we'll see an X chapter and
next video