Tip:
Highlight text to annotate it
X
This demonstration is an example of the Excel skills exam. You will work with your group
and look at the instructions here, then select a state and create a tab, name your tab “Group
A Texas” or “Group B Wyoming” or whatever you decide on, and find the population of
the top ten cities of that state (the largest populations in that state) and list them.
Then we’re going to write some formulas, so let’s, first of all, do a quick sum of
that. If you remember, every formula begins with equals, and then sum, and then you put
in parentheses what you are adding. Let’s add all of those populations, close our parentheses,
and press enter. Then let’s get an average by putting equals then average (our function),
start our parentheses, highlight what you’re averaging, close the parentheses, and press
enter. So you’ll need to work with your group. First of all, be sure one person doesn’t
complete all the work; everyone should do a little bit. These are skills you will need
for your final exam, and on your portfolio, so you will want to be sure you can do this.
You may even want to try on another spreadsheet of your own several times to make sure you
can recreate these formulas. So now let’s look at each city and see what percent they
have of the total population for each state. So we would begin with an equals sign, and
then, just as if you were on a calculator, what you would do is click this amount (so
I’ll click it right there) divided by this total amount. Remember that that’s an absolute
reference, so I’ll want to be sure that I put a dollar sign in front of each piece
so it would be “dollar sign B, dollar sign fifteen” and press enter so that then I
can then use my fill handle and copy down. Otherwise, it would think it was a relative
reference and it wouldn’t let me use my fill handle to get the correct information.
Now let’s assume that they’re projecting that each city will increase in population
by the next census 105%. So we’ve typed one hundred and five up here. So let’s create
that formula. So we type equals and the population we currently have times-we want to multiply
by this F1 but we’re going to need that to be an absolute reference so we can use
our fill handle-s o that would be “dollar sign F, dollar sign one” and press enter.
Now I will be able to click on that reference and, using my fill handle, carry it down.
So then that will show the projected population for each city with one hundred and five percent
increase. Then your group should create a couple of charts. So let’s do one by city
and by total population first. So I’m just going to highlight that. Since those cells
are next to each other, I could just highlight those and say insert, chart, and let’s use
a pie chart for that one and then insert. Then I can just click here (if you remember
from our projects), and edit and we’ll say “Population by City - Oklahoma”. Let’s
move that chart out of the way and create another one. So for this one let’s do the
projected increase, and since those cells are nonadjacent, I need to go insert, chart,
charts, and let’s do a column for this one. I’m going to click back here and go to my
“get range”, and that would be a3 through a14, add another range, move it over so I
can see, and that would be d3 through d14, click “ok”, and I’ll do a column chart
to see how that turns out. I’m going to move it… it
says quick edit unavailable. I’m not sure I did that right so I’m going to close that
one. Let’s delete that chart and try again. Insert, chart, a3 through a14, add another
range, d3 through d14, charts, let’s try column. I think that one’s going to be better.
Let’s move that one over here, and edit, and we’ll call this one “Projected Population
- Oklahoma”. Now I noticed this one didn’t save, so I must not have pressed enter when
I was through, so I’m going to do that one again. Let’s say “Current Population - Oklahoma”
and press enter. Alright! Good Luck.