Tip:
Highlight text to annotate it
X
Ok, in this second video, I want to talk about some simple functions in Excel.
Excel comes in preloaded with a large number of formulas or functions that you
can use to make all kinds of calculation.
you can see them
listed as dropdown menus on the Formulas menu.
You can see all of these different types of formulas, for financial purposes, math
texts,
date and time and so on
Some of the most common functions you might be interested in
are listed
here on this slide.
There's a function to take the square root of a number,
to raise a number to a certain power
and then several statistical functions that we will discuss in a second.
For this exercise, we want to calculate three different statistical quantities.
These are the mean, which is also called the arithmetic average
the median,
and then the standard deviation
In lecture, I would talk about all three quantities
and also in your lab procedure
these are defined.
The dataset you'll be working with
is the third one which is the
exam score dataset.
So in here
you can see the scores for exam one in Chem 11 some years ago
for a series of students who were taking the class at the time.
so what I want to do is first just calculate an average of all these scores
and I'm going to do it on this
cell right here.
How do i do that?
Well to do this, you need to know
what that name of the function that would allow you to calculate an average.
In Excel this function is called
AVERAGE.
So you would type "="
and then
the word "AVERAGE"
and you can see that it's highlighting here several different averages that you
can calculate, that Excel allows you to do
We want to just do the simple average
put parentheses,
and inside the parentheses I want to calculate
the average of
all of these numbers
Instead of typing them all in, you can just
go inside the parentheses and then
highlight all these numbers
you can see what Excel shows here is A3:A20
and what that means is it's going to calculate the average for the numbers
between
cell A3 and cell A20
and all the numbers in between.
If you press "Enter"
you get an average value
68.5.
I want you to do the same thing for the median and the standard deviation.
These are again formulas that you can access from the slide that I showed your
earlier.
Or you can also search in Excel to find what the formula is
for standard deviation and for median.
Ok, so the last part of this video will discuss how to add data to find
intersecting data points.
For this exercise you want to use the last dataset that you have from that
downloaded file on the website,
and this is called the Unemployment dataset.
Now unemployment, like some other data you collect physical sciences,
it tends to go in a cycle.
Some years, we have bad years, we call Recession Years, where unemployment keeps
going up,
and some years we call them Growth Years or good years where employment keeps
dropping.
So what we want to know in this particular dataset is
at which point during the period from 1989
to the year 2000,
is unemployment the highest? At which point during those years,
at which specific
intervening years?
You can determine this
by plotting both of these datasets and finding
the intersection point
between the two datasets. Let me show you what I mean
Ok, so first off you have to enter the data in these form. So you have one dataset for
the recession years and you have another data set for the growth years and you're entering
them as year which is one and then percent unemployment
for the Y-value,
and then the same thing for the growth years
What I want you to do now is generate a linear fit
for the first dataset.
This is something you learn
in the beginning part of this video so I hope you'll be able to do it at this
point and I also want you to generate a line equation and an R-squared value for that
fit.
If you did that correctly
this graph at the bottom should be what you see.
What I want is to add
the second dataset now,
and the second dataset is this one right here.
I want to add that to the plot.
How do I do that?
Okay
what you have to do now is the following
you're going to click inside this plot
doesn't matter where it is,
and then right-click
and in one of the options you should see "Select Data". Choose that option.
And now you should see this window showing up.
You want to "Add"
and now what you have to do is add what you want to be the second dataset's X-values
Y-values. How do you do this?
You go back to your data set
and for X-values, you want to highlight the years from
1993 to 2000.
That's your X-calue for the second dataset.
And then for the Y values, you want to do the same thing but since there's some
things entered here already, I'm going to clear that out, Delete,
and then instead I'm going to highlight the values for
percent unemployment for those years.
Then click Ok.
Now if you do this
and you click Ok for the second time,
If I bring that plot back again
you should now see this plot right here, the red one,
which corresponds to that second dataset that you've added in.
So what you want to do is then generate the same fit
for the second dataset
so you go "Trendline"
and then you can display equation and R-squared,
and now what you see is you see the second
line having the best fit
equation as well as R-squared value.
and what you can see is that the unemployment is highest during these two
years, which is 1992 to 1993, so there's a high
point here
and if I were to cross these lines together
I'm going to find a number that corresponds to the highest percent
employment
Ok?
And you can find that number
by using the two equations that you have here
because you can simultaneously solve for
for that intersection point.
Ok, so this ends the Excel graphing video.
All the skills you learn in these videos and the first video which gives you
the introduction to graphing
should be very useful in completing your Excel lab assignment