Welcome to Excel 2013-- Statistics Video 1. Hey, if you want to download
this workbook and follow along, click on the link
below the video. Hey, this is the first video in
my Excel 2013 statistics class at Highline Community College. And this first video is
an introduction to Excel. So we're actually going to
study Excel before we even jump into chapter 1 in the textbook. Now, we're going to
be using Excel 2013. The textbook and all the
videos are using 2013. And really, there's some
dramatically different things in 2013, especially
the charting-- that's totally different
than earlier versions. Now, here's the
topics for this video. And we're going to
start off by clicking on the sheet, Excel Layout. Now I'm going to start with
the basics of Excel first. And the most basic
thing is, look, we have letters at
the top of each column and numbers at the
head of each row. So when I click in
cell D16, that cell is named D16 because there's
a D representing the column and a 16 representing the row. Now, that seems
silly and simple, but it's so fundamental. Everything we do--
formulas, functions, charts, pivot tables, will be
based on cell references. Now that's D16. If I click over here, that's F5. I can even look up
in the Name Box. And if you hover your
cursor you can see, it tells you, oh yeah,
that's the Name Box. You can even click in the
Name Box, type A3 and Enter, and your cursor jumps to A3. So the cell is the
most basic unit. Now all the cells together
make up the worksheet. Down here, we can see
it says Excel Layout. That's the sheet tab. Later when you do your
homework from the textbook, you actually will have
to create a new workbook and name your sheets. Naming sheets is easy. You simply double
click, double click. Now I double clicked this. I'm going to type, what
is Excel and Enter. That's how easy it is
to name your sheet. Now, here we're on Excel Layout. So we got column, row,
then we have cell, then we have all the cells. Then we have a sheet
name, which gives us the name of the worksheet. All of the worksheets
together-- and you can see on clicking,
clicking, clicking-- all of the worksheets
together make up the file or the workbook file. Up here in the title bar,
we can see the actual name. This is a .xlsx. We will also have .xlsm. These are workbook files. So cell, all the cells,
worksheet, sheet tab-- all of the sheet tabs
and all the worksheets together make up the workbook. Now, let's talk a little
bit about navigating. there's a horizontal scroll
bar you can click or arrow over with your little scroll
arrow, drag horizontally back. There's also a vertical
scroll bar, click and drag. If you have the cell D16
selected or whatever cell, if you use the wheel on your
mouse, you can scroll down. I'm using the wheel
to scroll up and down. Now, in the workbook
files for each chapter, there's going to be many,
sometimes 20, 30, 40, 50 sheets. So we need to talk
about navigating through these sheets. Now one way is to use the arrow. And if you're
familiar with Excel, there used to be an
arrow here with a line that says jump all
the way to the end. That's gone. So we need to talk about this. Now watch this-- the sheet
Excel layout is selected. But if I use the arrow,
it just moves the sheet. It actually doesn't
move the active sheet. Now I can click the
other direction. You could see the sheet
is still selected. So I want to look at
something different. There is a keyboard
Control-Page-Down or Page-Up. So I'm going to hold Control
and the Page-Down, Page-Up are by the Home and End
above the arrow keys. All of those keys are going to
be very important in this class because we're going to be
dealing with a lot of data. Now I'm going to hold
Control and Page-Down. And now look what happens-- I not only move the sheets,
but the active sheet moves. And watch this-- see Enter Data. Now when I
Control-Page-Down, oh, now it shows me more sheets
exposed underneath. Now, I can go the
other direction. Control-Page-Up. So that's one way to
conveniently move. Now how do we get all
the way to the end since that little line
and the triangle is gone? You actually hover and it
reminds you you have to Control and left click. So I'm going to
Control and left click. Wow, it shows me the last
one-- it doesn't jump there. Now I'm going to
Control and click to show the very first sheet. Now here's the
coolest trick in 2013. I'm going to come to these
arrows and right click. And no way, a whole
list of all the sheets. So that probably is going
to become very important. Here we have maybe
25 or 30 sheets. Later, we'll have a lot more. Now check this out-- if I want
to see the homework problem, I click on Homework
3 and click OK. And instantly, not only
do the sheets move, but the active sheet moves. So that's pretty
convenient there. Now I'm going to right click. Oh, it pops right up. Come to the very top, click
on Topics, and click OK. Now I'm going to take my mouse. So that's a little
bit of navigating. Now we want to talk
about the ribbons. Now if I click, the
ribbon shows up. If I click it again, it hides. I'm going to right click. And there's the Collapse Ribbon. So if I uncheck this, now the
ribbons will show all the time. We will be getting
lots of features like fill color and font
size, number formatting. We'll go over to
the Insert, we'll have to use Insert PivotTable
a lot, or Insert Chart. Page Layout, we're not going
to really do much with that, but we will do so a little bit. Formulas, we'll definitely
do some stuff with that. Data, we'll do some
stuff with that. I'm going to click back on Home. Right click, check to collapse. That way you have a
little bit more room. Another thing is, this is my
Quick Access Toolbar, which I have customized. Yours shows up at the top
maybe with an a Save, an Undo, and a Redo. If you want to modify
the Quick Access Toolbar, if you want to modify
yours, you can right click and Show Access Toolbar
Above the Ribbon. That's where your shows up. If you want to add
your own buttons, like I want to add Underline. You come to any button in
the ribbon and right click, and it says Add to
Quick Access Toolbar. You gotta be kidding me. So I can go get my
favorite things. There's formula, evaluate. I'll right click, Add
to Quick Access Toolbar. How cool is that? Right click, Show Quick Access
Toolbar Below the Ribbon. Escape. The ribbon was actually hiding
the QAT there for a second, so I clicked Escape. So we have the Ribbon, we
have the Quick Access Toolbar, or QAT. That's where we go and
click to get our features. We saw the basic of how a
spreadsheet is laid out. Now we want to go to the next
sheet tab, What is Excel. And actually, I'm
going to double click that and type, what Excel does. Notice, I typed it out
and now I hit Enter. And now that sheet has that
name because this sheet is about what Excel does. It does four basic things. The first thing is
it stores raw data. Now this data set
is world famous. All raw data that is analyzed
is stored in this form. Field names at the top,
or column header names. They just say, hey, the date
has to go on this column, sales has to go in this column,
sales rep in this column. And then we have
individual transactions or records or
observations in rows. We'll talk more about
this proper data set in a little bit. But we store raw data in Excel. And what do we do once
we have the raw data? Well, the two main
things that Excel does is make calculations and
perform data analysis. Now calculations-- and we'll
see how to do formulas later in this video-- we can do something like a
formula with the SUM function to add a column. We can use the AVERAGE
to calculate the average or, as we'll learn in
this class, the mean. We can even use multiple
functions together to calculate MAX and MIN
to calculate the range. Now those are just
built-in functions. Just in a couple of
minutes in this video, I'll show you all
sorts of other formulas that make calculations. So calculations is one of the
main things we do in Excel. The other thing
is data analysis. Now data analysis, statistical
analysis, financial analysis, they're all doing
one basic thing-- converting raw data
into useful information for decision-making. Here's the raw data. One thing we can use is we can
use the pivot table feature to quickly go from a column
filled with sales rep, and then get an individual
listing of each unique name, the sales rep, and the total. From raw data into
useful information. How is this useful information? Because now we can compare. If the hurdle was 45,000
for getting a bonus, we can clearly see that
only Joe got the bonus. Another way we can
go from raw data to useful information
for decision-making is using a formula. Here, we had to type
these unique data. But we look through this column,
then summarized the sales based on the date. Here, we used the
SUMIFS function. We also can do things
like data visualization, not only with
conditional formatting, making a cell chart. Obviously, the
longest yellow bar indicates the biggest number. Or we can make charts. Now, here sales rep, we can
clearly see one, two, three. We're visually articulating
quantitative data-- that means the numbers here. And we can quickly see that
the green one is the biggest. All of these things
here-- pivot table, charts, formulas,
going from raw data, creating useful information. Now we'll see how
to do all the basics of these things in this video. But next, we need to talk about
data types, because get it-- we're going to start by
talking about raw data, then we're going to
look at formulas, pivot tables, and charts. But if we don't know all the
characteristics of our raw data before we start, then
we get into trouble. So let's go over to
the Sheet Data Types. Here are the data
types that we're going to see in Excel-- text,
numbers, logical values, trues and falses, errors. And empty cell isn't
really a data type, but it is going to be
important to understand empty cells because they will
have an effect on formulas, pivot tables, charts. Now let's just do an example. I'm going to type a word,
right, because the first data type is text. I'm going to type Excel. Now, I'm going to use the Enter
key to put this in and push my cursor down. But watch which direction
text is aligned. It is to the left-- always to the left. Numbers, I'm going to type 43. And watch this, I'm
going to hit Enter. 43. Whoa, it's aligned to the right. That's the default behavior. By the way I have my
Speak Cells on Enter. I should turn them off. Immediately when you
put something in Excel and it is a aligned the
default to the left, you know it's text. Immediately when it's aligned
by default to the right, you know that it is a number. Hey, what about Boolean values? That's a fancy word for
trues or falses or logicals? Notice, I didn't capitalize,
but when I hit Enter, always logical values,
TRUEs and FALSEs, will be capitalized and center. Here's another type of data. It's not really a
type of data, but it is something in a
cell that will affect formulas and pivot tables. If we do a formula
like 1 divided by 0, hey, we're going to get
an error because we're not allowed to divide by
0, or it's undefined. That's an error. Empty cell-- it's just
there's nothing in it. Now, stats are fun. It's definitely
aligned to the left. 450 aligned to the right. False-- notice I didn't
capitalize, always centered and in the middle. Equals a number plus a text. You can't do that. How do you add the
letters S-A-T-- that should be stats-- how
do you add these letters to this number here? It's not possible in math,
so you get a value error. This should say stats
because stats are fun. Now, I immediately
want to point out why it's so important
the default alignment. And the main two ones we will
see are text and numbers. Let's just look at
these numbers here. We're going to come down here
and do our very first formula. Equals S-U-M, tab. Then I'm going to take
my cursor and click. You see the dancing
ants are dancing around, but I'm clicking. And don't be scared
if you accidentally click the wrong cell. It's in Edit mode
until you hit Enter. So if I click and now
highlight by dragging, whoa, I went too far. No, no, no, just don't let go. When you got the
right ones, then you can Control-Enter to put
that formula in the cell and keep the cell selected. I can see up here in the formula
bar, that is our first formula. But notice, it gives
me an answer down here. Now let's try this over here. Equals SUM, and I'm going
to highlight the range. So far, it's looking good. Control-Enter because I
want to put it in the cell and keep the cell selected. Whoa, look at that 0. I could see the
formula up there. Why? Because these are text values. And it's so important
in our data era, when you're given data lots
of times from databases data will be exported. It looks like a number,
but it's exported as text. Guess what? Our formula won't be able to
add that with the SUM function. In this class, we will see
data that is improperly stored not as a number but as text. And then it's useless to us. So we need to
immediately recognize-- when it's aligned to the
left, it's our visual cue that there's something
wrong with the data. Over here our visual
cue was to the right, so we knew it was going to work. Now we've got to be careful. Watch this-- this
is why you never want to use alignment in Excel. And most spreadsheets in the
world, I have to tell you, do something silly like
this with their number. You center-- as soon as
you force alignment away from the default alignment,
you lose your visual cue. Control-Z. Now what's Control-Z? Control-Z is Undo. Now normally there's a
button, right, Escape. I don't even have that button
here because in this class we're going to use keyboards. We're going to have to
use undo so many times. What a hassle it is
to go up and click the little blue back arrow. Control-Z is the keyboard. All right, so numbers to
the right, text to the left. Now, here's what you
don't want to do. And again, really, if you go
out and look at spreadsheets in the working world, some
huge proportion or percentage of them do this-- they
take all their numbers and they center them. The problem is, it may look
good but immediately lose your visual cues
for whether Excel thinks it's a number or text. And there is a problem here. I'm going to Control-Z
to undo that. Notice this column,
dates are numbers, right? Immediately, this
is a typing error. Someone accidentally
put 37 days. As soon as you type this
and it aligns to the left, it's your visual cue
that you made a mistake. As soon as I delete the 7 and
hit Enter, ah, my visual cue, alignment to the
right, that's a number, it tells me that it's correct. As soon as I mistype this and
hit Enter and aligns this way, you're like, oh,
I made a mistake. I mean, that's by design. That's to the very beginning
of spreadsheets-- visual cue. Enter. We can already see
there's trouble here. If I was to use the SUM
function to add these up, I would get 92. I immediately have a visual cue. That is text. So again, when we
highlight, and many of you probably have seen
this in your job and go to Alignment, Center,
you lose your visual cues. Control-Z. All right,
so types of data-- text, numbers, logical,
errors, and even empty cells. Now let's go over to
the sheet, and I'm going to use my
little arrow here to scroll over a little
bit, and click on Data Sets. All right, so if our
goal with data analysis is to go from raw data
to useful information for decision-making, we
have to keep our raw data in a certain form. Now, raw data, or
simply data, we've all heard these
terms thrown around. Raw data or data just means
the individual little piece. This is the date,
individual little piece. This is the sales amount,
individual sales rep, that's the sales rep name. So all the little
teeny individual pieces make no sense until we
organize them and create useful information. But all of the tools we use in
Excel and throughout the world depend on the raw data being
kept in a proper data set. A proper data set-- here
are five characteristics of a proper data set. You have to have in the first
row of the data set field names or column headers or variables. Those are all synonyms. And I have some notes over here. Those are all
synonyms, and you'll hear it all over the place. Database people call
them field names. Scientists like to
call them variables. The reason that we have to
have field names or variables in the first row is you have
to say what kind of data goes in this column. We're only allowed to
put date in this column. Only allowed to put
sales in this column. Only allowed to put sales reps. Rule number 1, field
names in the first row. Rule number 2 is that
the first column has to have a unique list of items. It's called in
databasing a primary key. You also use the term element. We're collecting information
about this transaction. It happened on this
date for this amount from this sales rep. The first column contains
a unique identifier. If you as a student think
about your student ID, that's the unique identifier
in the student table at your university or college. Your student ID would be here,
and then all the information about you and grades would
be in subsequent columns. The idea is, there's
no duplicates in this first column. So when we collect data
for transaction 12570, we don't mix it up with
any other transaction. Imagine at your school
if you had your student ID in the first column. Imagine if you had your ID
down here for someone else. That means they'd be mixing up
your grades with someone else's grades. So unique identifier, no
duplicates in the first column. Called a primary key. And the primary key
contains elements. All right, so field
names in the first row. Primary key is the first column
containing elements we're collecting raw data about. The third thing is each row
is a record or a transaction or an observation. Those are all synonyms. This is the collection
or observation for transaction 12572. The fourth thing, and this
is important in Excel, is you have to have empty cells
all the way around the data set or row headers. These are row header 17, 18, 19. Or if this was touching,
up here that you could have column headers. The reason why-- as soon as
you put a piece of data here, which sometimes people do,
they put like a note about this transaction-- boom-- that becomes part
of the data set. When you sought it,
when you do a pivot table, and other data
analysis features, it will mess things up. And the final thing
is that you don't want to have to delete
any empty field names. You also want to try to not have
any empty cells, which means a bit of raw data is missing. Sometimes in the working
world the way data is stored, sometimes that does happen. That's what we're talking
about understanding-- an empty cell sometimes
will mess things up. Control-Z. You also obviously don't want
to have an entire empty row or an entire empty column,
because then it disconnects the data set, right? This bit of raw
data and field names up here is disconnected from
down here and over here. I'm going to Control-Z, Z. All right, so 1, 2, 3, 4, 5. Every data set is going
to have to be like that. Now there is one exception
to that, and it is-- and we'll go down here-- many data sets do not
have a primary key or list of unique elements. And it's no problem. All the other rules
still have to apply-- field name in the first row,
empty cells all the way around, records in rows, and then no
empty cells or columns or rows. The reason why this is
perfectly all right. And even in our
textbook, which defines a proper data set as
always having a primary key or a list of unique elements. Even though our textbook
defines it that way, we will see data sets in our
textbook that come like this. And it's no problem. Because once we summarize it--
and we'll see just in a few minutes in this video how to
do this with a pivot table or formulas-- once we do that and get
a unique list from here-- boom-- of our
products and then add them all up-- boom-- to
get our useful information, this becomes a proper data set. So that second rule about
primary key and unique list of elements is not always true. But the rest of
them have to be true in order to perform pivot
tables, sorting, and other data analysis features. All right, now here's
our second example. Does it meet all of the rules
for our proper data set? You bet. Field names in the first
row, looks like there's a unique list of elements. Records in rows, no empty cells. Boom-- here we have stock names. There's the date,
there's the number of employees, the net incomes,
and earnings per shares on that particular date. A third example. Oh, here is our student ID. Here's the class, the quarter. Here's the testing information. So this would look
like my grade book. If I'm not going to
mix up your grades, I better have a
unique identifier, either your name or student ID. So proper data set, proper
data set, proper data set. And even our example
right here, this is a proper data set for using
our data analysis features. But it doesn't have a primary
key or unique identifier. Now we want to go
over to the next slide and talk about entering data. So I'm going to click
on entering data. Now I have two simple
problems like you might see in the textbook or on the test. And we have to
figure out what is going to be required when you
enter the data in the worksheet and complete the problem, doing
your calculations or whatever. Let's read this first one. Problem 12, if the
grades for the class were-- bloop-- these, what
was the class average or mean? So the very first
thing is I'm going to label everything properly. Problem number 12. I like to do formatting. I'm actually going to right
click and uncollapse this. So a week ago, I have
built my QAT here for all of my favorite buttons. But just in case you
don't have that there, I'm going to use the
features up here. I'm going to add some yellow and
I'm going to add an All Border. Now this looks like a data set,
so I'm going to say grades. And I'm going to format this. I'm going to use some dark blue. And I'll show you two methods
or techniques for formatting. I like to add color
and stuff like that. Guess what? That's the field name or
variable or column header. And now I enter the raw data. 2.9, Enter. 3.2, Enter. 1.9, Enter. 3.8, Enter. Now I don't even remember. 2.5, 2.5, Enter. Now this row is too
tall, so I'm going to point between 17 and
18 and double click. Now I'm going to add
some borders here. And guess what? That's a proper data set. Now here we're not going to
use pivot tables or anything like that that are
dependent on that. But guess what? Now when you come
and look at this you know exactly what these are. Now our goal was to
calculate the average. So you have to put a label. And I'm going to,
in parentheses, say mean because when
we get to chapter 3, we'll learn that what we
learned in grammar school as an average in statistics
is called a mean. And what is it? We add them all up and
divide by the count. Well, luckily we
don't have to do it longhand-- we can do a formula. Equals sign starts any formula. And then I'm going
to type A-V-E-R. And when I see AVERAGE
selected in blue, I hit Tab. I'm simply going to with my
mouse click on the first one, and there's my dancing
ants, and drag until I get the right number of cells. And then Control-Enter. Now I want to definitely
add some formatting. I got to show you a cool trick. I already did fault wide, fill,
blue, and border around this. So I'm going to right click. Oh, the mini toolbar has Fill
and Font Color and Border. That's pretty cool, but it
also has the Paint Brush, which is copy the formatting only. So-- Boop-- I click it. Notice, I'm not clicking. But look at my cursor,
it's a thick white cursor with a black shadow with
a little paintbrush. Now be careful-- wherever
you click-- boom-- that applies the formatting. Now I'm going to very carefully
in every spreadsheet I ever make and show you in the video,
and cells I expect on a test to have a formula. I'm always going to
add some formatting. And I'm going to go
down to More Colors. And in the standard color,
there's Custom and Standard. I'm going to select
the old counting spreadsheet before there were
spreadsheets, paper green, and click OK. So anytime you see
a cell like that, it means that that's a formula. Now even out in the
working world sometimes this is handy because,
notice, this is raw data. I didn't add any
formatting there. I put my field names or my
labels in this formatting here. And I clearly delineate
because when I'm over here, if there wasn't
any green there, I don't know if that was
typed in or a formula. So I'm using formatting to
help me to understand, yes, that's a formula. Yes, this is raw data. Now when I highlight this
and I want to copy it, I'm going to use keyboards,
Control-C. You can right click or come up here, if you want. But Control-C to copy. Now when you copy, that's
copying the content. There's some text there,
there are some numbers here. There's a formula, there's
fill, there's font color. So when I come down
here and Control-V is the keyboard for paste, whoa,
it actually pasted everything. Now I'm going to show
you another cool trick. Home, over here to the
editing, there's an eraser. And you can erase
just the format. I'm going to click on this. All would remove everything,
the content and form. And I'm just going to
remove the formatting. Because I've got to
show you two schools of thought on formatting. Now, Control-B or Bold, either
Control-B or Bold up here. And I'm going to make this the
minimal school of formatting. I am going to add a color there. This is the minimal
school of formatting. And if you're out there
in the working world, you'll see that
people do formatting like this a little
bit more elaborate. But the minimal
school says, hey, why do you think they
invented those gray lines. I don't need to add a border. Why do you think we have bold? I don't need to
add all this color. So this would be like
the minimal formatting. That's the kind of
formatting I'm going to do. Now we'll talk about
Excel's golden rule here, because here it is. If a formula input can
change, put it into a cell and refer to it in a formula
with a cell reference. Now what does that mean? That's what this is right here. Notice I'm referring
to the raw data. These are called formula
inputs with a cell reference. Now this is a range of cells. This is A21 to A25. The reason I do that, and the
original idea for spreadsheets from Bricklin and
Frankston back in 1979, VisiCalc, was if you mistyped
your data, this wasn't 2.9, it was actually 2.7. Now watch this, before I
hit Enter, watch over here-- boom-- instantly
everything updates. Why? Because we did a formula
with cell references that pointed to the raw data. Now I'm going to
Control-Z. All right, so that's the first part
of Excel's golden rule. The second part, and
we haven't seen this, is if the data will not change,
like 24 hours in a day or 12 months in a year, you can
type that into a formula. And we'll see that
later in this class. And part of the
golden rule-- always label your formula inputs. And that's what we did. We either used some
elaborate formatting or the minimal formatting. We're also following on
the rule of formatting the cells with our formula. Now problem number 13, if the
total car sold for the month was 75 and 15 Fords
were sold, what proportion of the
cars sold were Fords? Well, the first thing is I
want to put my problem number, but I've got to show
you a great trick. Control-C, remember, it
copies content and formatting. Control-V. And I'm
going to hit the F2 key to put it in Edit mode. F2 always puts it in Edit
mode and keeps the cursor at the end. And watch this-- backspace, 3. So even if I put it in Edit
mode and changed a little bit in the text, that'd
be probably faster than typing it out and
adding all of the formatting. Now here's what we
never want to do. I'm actually going
to put a label, which is part 3 of the golden rule. I'm going to say, I
didn't put a good label because it doesn't say what
kind of proportion it is. And here is violating the
rules to the n-th degree. I'm going to say equals
15 divided by 75. I am violating the rule. I'm hard coding formula
inputs into the formula. Control-Enter. Well, yeah, it does
give me the answer. I can see this up here,
but there's a huge problem. Well, we already know if
I needed to change these, and if I'm over here
and I'm looking at this, I'm like what is that. But even if I had
to change this, it's easier if
there was a label. When I look up here,
I don't know what that 15 is, I don't
know what that 75 is. But there's a bigger
problem, and I'm going to scroll down here. Research throughout
spreadsheet history has shown that embedding
variable formula inputs in formulas,
hardcoding is one of the main contributors
to faulty spreadsheets. And there's a couple
studies done in history, but here's a great one right
after the financial crisis, if you want to click
on this and go. It talks all about this being
one of the main problems. Now this is just one cell, but
imagine an entire spreadsheet filled with formulas
like this and no labels. So that's not going to work. We're going to come up here
and type this out correctly. So I've entered my
labels and my numbers. Now I've got to
show a great trick. We're going to use right
click, mini toolbar, and that paintbrush again. But before we did
it on one cell, watch this-- as long as
when I click I don't let go, but I can click and I'm
holding and I'm dragging. And when I let go, now all
three cells get that formatting. Now I'm going to go ahead
and highlight all these, right click, and check
that out-- mini toolbar, All Borders. Now right click. And it looks like the bucket
is already that color, so I can simply click and-- boom. Now let's do our formula. An equal sign
starts all formula. Now I can go ahead and
click with my mouse. But if the cells are
close, use your arrow keys on the navigation board. So I'm going to use my
Up Arrow to get the 15-- that's the Ford
sold for the month. Then I'm going to use
division-- that's forward slash. And then Up Arrow, Up
Arrow, to get the 75. So there we have our
proportion or division-- 15 divided by 75. And I'm going to
use Control-Enter to put the formula in the cell
and keep the cell selected. I can see my answer right there. Up here, I can see
the actual formula. Now if I change the inputs,
it wasn't 75, it was 80. Instantly, the formula updates. Now I'm going to
Control-Z to undo that. So that's how we're
going to enter data. When we read a
problem, we're going to put formula
inputs in the cell, label everything, do
some sort of formatting, make our formulas pointing
to the formula inputs. We never want to see
equals 15 divided by 75. If you do this on
a test, you get 0. Why? Because we don't want to
be one of those statistics about faulty spreadsheets. All right. Now we're going to go
over to our next sheet. Even though we've done
a few formulas here, we want to now go over
and talk a little bit more about formulas. I'm going to click on Formulas. Now, we have a bunch
of examples here, but I also have some notes. So formulas, well,
we can do things like add a column of sales,
calculating an average, add with one condition. We'll see examples of all
of those in this sheet here. Here's a list of
the different things that can go into a formula. And we'll see an example of all
of these on this spreadsheet here. But of course we start
with equal signs. We can have cell references
in formulas, math operators, numbers if the
number won't change like 12 months, built-in
functions like SUM, AVERAGE, SUMIFS, comparative operators
like greater than or less than, even the join symbol. And sometimes we have text. All right, that's just a list. So you can come back
and refer to this later. Here's the order of operations
or our math operations. You should already know
the math order operation from the math prereq
for this class. Parentheses,
exponents are carets. Multiplication, division,
plus, and subtraction. Here's our
comparative operators, and we will see comparative
operators throughout our class. There's the join symbol. Here's the full list of how
Excel calculates formulas. The full list is here
for you as a reference. As we run through
our examples, we will see examples
of all of this. Now let's go scroll to
the top of the sheet. I'm actually in the E column,
so I'll use my horizontal bar. And we want to use this as our
first transaction data set. And we're going to see about
15 examples of formulas. Now, the first one
here is we want to add this column of sales. Now we typed it out
before, equals S-U-M. But I've got to
show you two things. One is, adding a column is
the most common calculation in Excel. So what do you think they have? Right up here is a button. Now we're never going
to use this button. And actually that's sigma-- that comes from statistics. That means to add them all up. But I want you to
notice something. When you hover your cursor
over this Sigma AutoSum button, notice that in the screen
tip it says Alt-Equals. Alt-Equals is the
keyboard for the AutoSum. Of the 450 functions in Excel,
only one has a keyboard-- the SUM function. So let's try it. Alt-Equals, and sure
enough it puts it right in. Number 1, number 2. This thing right here
is called a screen tip. And as we use more
and more functions throughout this
class, especially the statistical
ones, the screen tips will help us
complete our format. Now SUM and AVERAGE
and MAX and MIN, those are known as
aggregate functions because they calculate a single
number from a bunch of numbers. Now number 1, number
2, that's very common. That means you could
put as many arguments. You could actually highlight
this column, and then another column over here,
and it would add them. But for us, we're simply
going to take our mouse, click in the first one, drag. When we see our dancing ants
going around the correct range, then we hit Enter. And sure enough, that will work. Now the SUM function,
it's awesome. Notice here I have
another formula, and I'm going to hit F2
to put this in Edit mode. You never want to do
a formula like this. If you do a formula like this
on the test, it's not correct. And the reason why? Well, there's two reasons. One is, obviously that took a
long time to create plus, plus, plus, plus. But the second thing
is this, many times in spreadsheets
people will come, point to the row 5, right click,
Insert, and add a new record. So I'm going to put like 50,000
here or whatever that is. Watch these two formulas. When I hit Enter, only
one of them updates. F2, only the one with the range. G2, colon, G11. It actually isn't
G2 to G11, it's whatever the first one you
highlighted to the last one. So that way, when it
was looking at G10, as soon as we inserted a new
row, it knew to push it down. Whereas this formula, F2, never
going to pick up that extra inserted row. Now I'm going to
Escape and Control-Z to get rid of the number,
and Control-Z to get rid of that inserted row. Now let's look at the average. Now average, as we learned,
is called the mean. Add them all up,
divide by the count. Now there's no keyboard,
so we're going to type. Equals A-V-- and check this
out, as soon as you type in E, those are all the
alphabetical listings of the built-in functions. ABS is for absolute value. I'm going to type a V. Oh,
we get a bunch of AVERAGE. And we will actually see some
of these different averages in this class. But now I'm going
to type E-R. And now when the function I want
is highlighted in blue, I can simply hit Tab. And now number 1, number 2,
it's an aggregate function. It means it's going to look at
a bunch of numbers-- bloop-- and then make a single
answer calculation. Enter. Now I'm going to come
up here and right click, Collapse the Ribbon. Now if I want to count, it
depends on the data type. Remember, this is a
number, this is text. If I'm only given text,
which some of the data sets in our class
will only be text. You can't use the
COUNT function. And in fact, let's click
in cell F11 and type equals C-O-U. And
check this out, there's all sorts of
counting functions. I'm going to type an
N, and now we get just to the COUNT functions. And we want to learn
something really important about this dropdown list. When you select either with
your mouse or your arrow key, it will select the
function in blue and it will give you a
screen tip that gives you a hint at what this does. Counts the number of cells in
a range that contain numbers. Down-Arrow, COUNTA,
counts the number of cells in a range
that are not empty. Now that's important. It really is not empty. COUNTA will count
numbers or text, but this is the function we
use when we're counting text. Another function,
COUNTBLANK, counts the number of empty cells
in a specific range. When would you ever want that? Well, remember we
talked about raw data? You might want to count how
many missing bits of information there are. Down-Arrow, COUNTIF
and COUNTIFS. We'll see lots of uses
for COUNTIF and COUNTIFS. This counts the number of
cells specified by a given set of conditions or criteria. I'm going to Up-Arrow
and I'm going to use COUNTA here
because I'm going to count the nonempty cells. I want to count how many
names there are in this. And Control-Enter. That is 9. Now let's try down
here, equals COUNT. And I'm going to try the COUNT. Notice it says counts numbers. Tab. What if I were to
highlight text? It's not going to
work, it gives you 0. Now I want to use this
opportunity to show you how to edit a formula. Don't hit delete and start over. I mean, you could do that. But watch this-- F2. As soon as you put an F2,
the cursor is at the end. If you're cursor is inside,
you can click on the argument and it instantly
highlights the whole range. Now just like highlighting
a word in Word, when you highlight it and
start typing, it replaces it. It's the same here in Excel. Notice it's highlighted. So as soon as I click and
drag, it totally replaces it. That will work. Let me show you a different way. Control-Z, I'm back
to the wrong formula. Another way to edit
your formula is if you point to the edge
of the highlighted range. Notice the dancing
ants aren't moving, so you can't just click
in and highlight it. If I were to click
and highlight, it doesn't do anything. It's only when
those dancing ants are going that you get
to re-edit and continue to select until it's right. But watch this-- I'm going
to point to the edge. That cursor right
there, notice, we've been using this cursor--
that's the selection cursor. But watch this--
I'm going to point to the very edge of the
line, that blue line. When I see that arrow, that
four-way pointing arrow, that's the move cursor. You can click and move. As long as you don't let go,
you just keep dragging it until you get it where
you want, and now let go. And hit Enter. These are all examples
of aggregate functions that look through a
bunch of different items and calculate a
single number answer. Now in all of these examples,
we did single cell formulas. But now we got to talk
about copying formulas. And we have a few
examples for this. Now here's our data
set for this example. We have employee and gross pay. And here's our tax rate. And we need to calculate a
column of all the deductions. Now calculating a single
cell formula is quite easy. I'm going to click
in G27 and I'm going use the equals sign-- that
equal sign starts all formulas. And since my cell
references are close, I'm going to use Left-Arrow
times Right-Arrow, Right-Arrow. I have the gross pay
times the tax rate. When I hit Enter, I
calculate the correct answer. Now the next employee, I
have to redo the formula. Equals, I'm going to get
the gross pay, times I27. Enter. Ah, this is getting tiring. Equals, one cell to my left. Notice each time
in this column I'm always looking one cell
to my left, times I27. Now the funny thing about
the pattern of this formula, equals sign, one cell to the
left, times locked on I27. Every single formula
in this column needs to be locked on that I27. But every new formula needs
to adjust and look exactly one cell to my left, times I27. So instead of creating
all the formulas by hand, we're going to have to
learn the difference between relative and
absolute cell references. Now I'm going to
highlight all these and use the keyboard shortcut
for delete, the Delete key. Do not right click
Delete in Excel. There's very few situations
where you want to do that. Because if you do that-- and
I'm going to do that and make a horrible mistake here-- it's going to ask you
which direction you want to shift the cells. If I were to just
blindly click OK, oh, it literally deleted everything
and shifted everything over. Luckily, there's Control-Z.
So use the keyboard Delete. Now I'm going to try
this formula again. Equals, one cell to my
left, times locked I27, Control-Enter. We use Control-Enter
when we want to put something in the
cell and immediately do something to the cell. And what I want to
do to it is copy it. So I'm going to
use a trick here. That little green box in
the lower right hand corner is called the fill handle. This is my selection cursor. But watch what happens
to my selection cursor when I move it right to the end. You don't want to
move the cursor. You want the crosshair. That's the crosshair, or I like
to call it an angry rabbit. You can click and drag, and
that copies your formula. What? What happened there? I'm going to go to the last
cell, and this will be a trick. Every time we copy
a formula, you've got to go check the
last one by hitting F2. Well, look at that. Well, the blue one is a
relative cell reference. That means every
single time we copy, it's looking relative to me, the
formula, one cell to my left. But that one, I didn't
want it to always look one, two cells to my left. I wanted it locked on I27. So I'm going to Escape and
highlight all of these, Delete. Let's try this again. Equals, one cell
to my left, times-- but now when I highlight I27
either with my arrow keys or my mouse, you hit the F4 key. And the F4 key puts
the secret symbol in that tells Excel that's
no longer a relative cell reference-- it's an
absolute cell reference. It is forever locked on I27. In this class, these
are the only two types of cell references we're going
to use-- relative and absolute. There are other types
of cell references. But for this class, these
are the two cell references we're going to use. And you're not going
to believe this. When I Control-Enter, now
I'm going to take my cursor and point to the fill handle. When I see that angry rabbit,
I'm going to click and drag. Click in the last
cell and hit F2. That is amazing. What did it do? Relative cell reference--
every single cell is always looking one cell to
my left, times absolutely I27. F2, one cell to my
left, absolutely I27. Now the advantage
of this is time. You save time. If you have to do every single
one by hand, it takes forever. In this class, we will have
huge columns of formulas where if we didn't
know the difference between relative
and absolute, we'd be spending way too much
time creating our formulas. When instead we create a
single formula, copy it down. Now we want to talk about
a few more formulas. And we're going to talk
about a parallel example to the one we just did. We're going to do two
single cell calculations, and then we're going to do
two column calculations. So here's your score. You got 8.6 on a 10-point quiz. And here's all the rest of
the scores for the class. And you want to figure out
how well you did, right? Well, you wanted to get
10, but you got 8.6. But what if we took the average? And I'm going to
click on cell F38-- equals A-V-E-R, Tab, and
highlight all of the scores. And I'm going to hit Tab to
put the formula in the cell and jump to the right,
because eventually I'm going to come here and
make my calculation. Well, if the average was
6.97 and you had 8.6, did you do above average
or below average? Well, you did above average. Now we could take
this one step further. I'm going to make
a second formula. Equals, and we're going
to take your score and subtract the average. Now later we'll
learn that this is part of the standard
deviation calculation. But you take the particular
x value-- that's you-- and subtract the mean. That means the average
of all of the numbers. If this number comes out
positive, you're above average. If it comes out negative,
then you're below average. This particular calculation
is called a deviation. From the average or the mean,
how far are you above or below? Well, you got 1.6. You're way above, right? Now in that calculation
for standard deviation, we have to get a
little tricky here. And I want to illustrate
not the concept because we'll talk
about standard deviation later, but just making formulas
and the different things that can go into formulas. So if our goal is to
square-- remember, we have to use Shift-6,
that's a caret, and do a 2. Now that 2 is a number
hardcoded into a formula. But remember when
you do a square, the 2 is never going to change. So that's an example
when you can hardcode a number into the formula. Now there's another
problem here. When I Control-Enter,
that's not right at all. F2 to put it back
into Edit mode. Hey, we have to
force subtraction to happen before exponents. We know our order
of operations-- parentheses, exponents,
multiplying and dividing left to right, and adding and
subtracting left to right. So if this is at the
bottom of the math operations,
subtraction, and we want it to happen before exponents,
hey, we just use parentheses. Now it'll calculate fine. Now this calculation later
we'll see is deviation square. One, two single cell formulas,
let's make these calculations through the whole column. I want to do the deviation
for the entire class and the deviation square. All right, so you ready? Equals, one cell to my
left, times the average. If I Control-Enter, that
puts the thing in the cell and keeps the cell selected, and
double click and send it down, well, I'm going to have
all sorts of trouble. Don't worry about trouble. Go to the last cell, hit F2. You're like, oh,
the range finder, that's what this rainbow
color-coded cell referencing system is called-- range finder. You can clearly see that
the orange one is not pointing towards the average. So you click Escape, you
come back up here, F2. Now we have to lock it. Now how do you lock? We didn't just
create this formula, I don't see the dancing ants. The way you can lock is as
long as your cursor is touching somewhere-- if I
hit it right now, it would lock the wrong one. But somewhere after, before,
or anywhere in the middle, as long as the cursor is
touching, hit the F4 key-- boom-- you've locked it. Control-Enter. Now remember, we double
clicked and sent it down, it looks to left but
it also looks below. So when I double click-- zoom-- it replaces all those. Go to the last cell in F2. Now that's the completely
wrong calculation. Multiplying-- no problem. F2 on the top one,
it's subtraction. Control-Enter. We're going to use
the same concept. Double click and send it down. This will happen a lot. We're making calculations,
we make errors. No problem. Excel is easy. F2, Edit, change
it, copy it down. So that's looking pretty good. I'm going go the last cell, F2. That's looking fine. Deviation square,
equals, and this is a relative cell reference,
one to my left, caret 2. We're hardcoding that in because
it's never going to change. Control-Enter. Double click and send it down. Now we're still
talking about formulas. So let's go down here. And we want to make a
bunch of calculations. We're actually going to do all
the calculations for this thing called standard deviation. But we're not worrying
about the concepts, we're just worrying about the
different types of formula elements that we will see. Now, hey, one of the
things we have to do is add up this whole column. Well, we already know that
there's a keyboard for this one function-- Alt-Equals. Now when you Alt-Equals,
it always tries to guess. Our previous example
when we did Alt-Equal, there weren't any
numbers around it. So the dancing
ants didn't go off. But here it got it wrong-- I don't want that empty cell. The thing about the
dancing ants is as long as they're still dancing, it
doesn't matter how many times you try and correct the range. Just when you finally get it
right, then you can hit Enter. But be careful when
you Alt-Equals, be sure and look
at the dancing ants and make sure they're
in the right place. Now one of the
things we're going to have to do often in this
class is take the square root. Luckily, there's a function. And so I'm going to
type equals SQRT. And sure enough, returns
the square root of a number. So it's S-Q-R-T, Tab. It says a number there. I'm going to click right on the
cell with a number, and Enter. Hey, it calculated
the square root. Now if we didn't have
the SQRT function, we could use an exponent. And I'm sure you remember
from your algebra that we're allowed
to take a number and raise it to a fraction. Now for this particular
example we use the square root, but later in chapter 3
we'll do geometric mean. And we're going to use
exponents that are fractions, taking the second root, third
root, fifth root, sixth root, et cetera. So in this case,
we're going to have to build a formula like this. If I hit Control-Enter,
that's not right. And why? F2. Because the order
of operations is going to do exponents before the
division, left to right, right? So I'm going to come over
here and put parentheses to force that fraction. Now why are we typing
the fraction in? Because those numbers
are not going to change. The square root is
always fraction 1/2. And there we go, we
get the same answer. Next we need to count. And I'm counting
number, so I'm going to use that COUNT function. Any one of these
columns will do. I'm going to highlight
this column, and Enter. So we get a count of 13. And now the big finale
is the final calculation is we actually have to take
total deviation squared and divide it by the count. And we're going to have
to do the square of this. So watch this-- SQRT. And just a second ago, we
did square root of a number and we just did
a number, but I'm going to do a calculation here. Hey, the total deviation
squared divided by 13. So here we're
seeing we're allowed to do a calculation right in
the argument of a function. And Enter. That tells us the standard
deviation of the population. The population because we
have all of the quiz scores. Later we'll talk
about the difference between population and sample. Now all of that was just for
us to learn about formulas and formula elements. But that was a lot of steps to
calculate standard deviation of the population. And the beauty of Excel-- many different things
make Excel so amazing-- but one of the
things is that they have a lot of
built-in functions, especially for statistics. So watch this-- S-T-D-E, and
there's a P for population and an S for sample. We have a population,
so I hit Tab. And you've got to be kidding me. I simply highlight
quiz scores and Enter, and it does all of those steps. F2. That function has an algorithm
that does all of these steps. That is amazing. We have a couple more things we
want to look at with formulas. I'm going to scroll down here. Now in this last set of
data we have CPA scores. And we're going to calculate
total, average, and count. So we use SUM,
AVERAGE, and COUNT. Well, we know how to do that. But here we want to pay
particular attention to building formulas quickly. Now let's just see how
tall this data set is. I'm going to use the wheel on my
mouse, and I'm scrolling down. Ah man, this is huge. Imagine if you had to click and
select, and down, down, down, down. That just takes way too long. So guess what? We're going to learn the
keyboards for selecting. Now let's just click
in the top cell. And the first keyboard
we want to learn is how to jump to the
bottom of the data set. So you're ready? Control-Down-Arrow. Now how did it
know to stop there? It knew because it kept going. And the first empty cell
it saw it knew to stop. Watch this-- if I have
an empty cell, remember, we talked about how
empty cells can sometimes mess up your data set. I'm going to Control-Up-Arrow
to go to the top. If our goal is to go to the
very bottom of this data set, and there's an empty cell,
Control-Down-Arrow, well, it looks like we got
to the bottom but we didn't quite because
Control-Arrow or later Control-Shift-Arrow only goes
until it sees an empty cell. Now I'm going to Control-Z
to put that back up there. Control-Up-Arrow. Now that's going to
help us for navigating. But what we really want is to
quickly select for formatting and for getting
ranges and formulas. So before we see the keyboard,
let me click right here. And before I click on the number
41, I'm going to hold Shift-- boom. That's called the bookend. You start with one. Before you click on the
other side of the bookends, you hold Shift. And it works anywhere. Click. Before I click over
here, Shift-- boom, it highlights everything. Click right here. Before I click
over here, Shift-- boom. So that is awesome for
highlighting a big range. You don't have to
click and drag. Now we want to combine. It's Control-Down-Arrow to jump. But if we hold Shift,
Control-Shift-Down-Arrow, I love that. That highlights
all the way down. That would be perfect
for formatting. Control-Up-Arrow. But now I want to try
it inside a formula. Now this next trick
we're going to combine. Alt-Equals and
Control-Shift-Down-Arrow. Now I promise you at your work
if you do this and your boss sees, they'll be like,
how did you do that. So you ready? Alt-Equals is the
keyboard for the AutoSum. And you've got to be careful. The dancing ants, they're
trying to be polite. They're trying to
look for the numbers, but it got it totally wrong. Now notice, they
programmed Excel, it's dancing ants, which
means it's Edit mode, and it's highlighted. So you don't even
have to hit Delete. I just click over here
and now the magic-- Control-Shift-Down-Arrow. Now I'm going to Control-Enter
to put the formula in the cell and keep the cell selected. Oh, that didn't work at all. I'm going to use my scroll
bar to scroll all the way up. Let's try that again. Alt-Equals, click in the top
cell, Control-Shift-Down-Arrow. If I hit Enter,
it doesn't really work either because I
can't really see the top. So I want to show you yet
another way to enter a formula. We've seen Enter puts the thing
in the cell and goes down. We've seen Tab puts
the formula in the cell and moves to the right. We've also seen
Control-Enter, which puts the thing in the cell
and keeps the cell selected. But now, ready? Now I'm not even
going to delete that, I'm just going to Alt-Equals,
click in the top cell, Control-Shift-Down-Arrow. And now many you Shift-Enter. Shift-Enter puts the thing
in the cell and jumps up. Now you don't have to do that. Just remember Alt-Equals,
Control-Shift-Down-Arrow. And even if you
use Enter, at least remember to use your
wheel and scroll up. That is profound. Now let's try it
a few more times. AVERAGE, tab, click
in the top cell. Control-Shift-Down-Arrow,
Shift-Enter. Equals COUNT. Counting numbers, Tab. Click in the top cell. Control-Shift-Down-Arrow,
Shift-Enter. That is amazing. That's an example of keyboards
and how to make your job easy. Now we want to talk about
a couple more formulas. And they have to do with
counting with conditions. Now I want to look
through the CPA scores and count how many of these
scores are greater than 90. Then we're going to do how
many are between 75 to 90. So I'm going to click in
cell H74, equals COUNTIF. And there's two functions-- COUNTIF, that only counts
with one condition. COUNTIFS, that counts with
one or more condition. So I'm pretty much
going to use COUNTIFS. Now COUNTIFS started
in Excel 2007 and has existed ever since. And the screen tip is polite--
it says criteria range. That means all the numbers. Then it says criteria-- that means the actual condition. So I'm going to click in the top
cell, Control-Shift-Down-Arrow. And now I'm going to use
yet another keyboard, Control-Backspace. Control-Backspace jumps
back to the active cell. Now I need to get to
the next argument. So I'm going to type a
comma, and watch the bold. Oh, criteria 1. It's saying, hey, give
me the criteria 1. So now I'm going to click. Notice I typed in the
comparative operator and the number. Notice the alignment
is to the left, so Excel thinks it's text. But it's fine because
the comparative operator and a number together are text. That's how you put the
criteria into COUNTIFS. And it will tell
you, only 79 people. And if we did our division,
79 divided by 2,000 whatever, we'd
get our proportion or percentage of people
that scored above 90 points. Now the beauty of this
is now I can simply say greater than 95-- 32. Greater than 99--
wow, eight people. Now if I type exactly 100,
that's just equals 100. Oh, so there's eight people,
yes, because greater than 99 there's only one number
for our range of scores greater than 99-- that's 100. I'm going to go back
to greater than 90. It's kind of [INAUDIBLE]. You could also do less than 90-- most people, right. Or greater than or equal to 90. That means it'll
pick up the 90 also. So 89, I didn't notice. If I get rid of the
equal sign, yeah, wow, there's 10 people
that got exactly 90. Now that's a single condition. Now let's do two. And in this class,
statistics, we will have many examples
throughout the chapters where we're counting between
an upper and a lower bound. Oftentimes you've
got to be careful. If you have a lower and
upper bound, in just a moment you see we have a
lower and upper bound. If we weren't careful about
where we put the equal sign, you notice the upper is
10 here but the lower is 10 for the next category. That means we can't put an
equal sign in both places. And we'll see this. Throughout the class
sometimes we'll have the equal
sign on the bottom. Sometime it's on the top. There's other times
where you're never going to have a problem
because these two numbers are different. But when they're
the same, you better be careful with
your equal signs. It's two conditions, so
I'm going to use COUNTIFS. Well, I'm going to be using
COUNTIFS all the time anyway. Arrow, Arrow, Tab, COUNTIFS. We're actually going to have
to put the range in twice. There's criteria
range 1, criteria 1. Now I always remember
it says range. That means all of the cell. So I'm going to click in the top
cell, Control-Shift-Down-Arrow, Control-Backspace. Control-Backspace jumps
back to the active cell. Now my screen tip is
trying to be polite. It's not until I type a comma
that the next argument is bold. Now I click on greater
than or equal to 75, comma, criteria range 2. I have to repeat it. Hey, I'm asking two questions. I need true and true. The number has to both
be greater than or equal to 75 and less than 90. So I'm going to have to enter
that criteria range twice. Click in the top cell,
Control-Shift-Down-Arrow, Control-Backspace, comma. And now I'm going to
use my arrow key to get criteria range 2 less than 90. Close parentheses,
Control-Enter. 412 people were
between 75 and 90. Now our last formula
for this sheet here is going to be a
formula, just like this one, except for two things. We're going to have
to be able to copy it down an entire column. And check this out-- the
lower and upper limit for each one of
our rows does not have the comparative
operator in the cell. Up here, we put the comparative
operator and the number in the cell. That's no problem. We can take the lower limit
and join a comparative operator inside our formula, and
then the upper limit and join the
comparative operator right inside our formula. The other thing that's going to
be different about this formula is that these formulas
we didn't have to copy. But here we're going to
have to copy the formula all the way down the column. Now I want to point something
out about this label. I created this label here just
so when we create a chart, there is a label
that is unambiguous. But this label here follows a
slightly different convention for comparative
operators than we're going to use in our formulas. All this is is this
is from algebra. That score, that's the
variable, it's in the middle. Remember, this is between. We're going to count
between an upper and lower. So the score has to be both
greater than or equal to 0 and less than 0. But here, the score
is in the middle. So that's why the
greater than symbol is pointing towards the score
here, and the less than symbol is pointing towards
the score here. When we do our formula,
our comparative operators are always pointing
towards the column. Right here, this is
a less than, it's pointing towards the column. So when we get greater
than or equal to 75, it has to look through here
and find numbers that are greater than or equal to 75. For this criterion, it'll have
to look through the column and find all the numbers
that are less than. And again, this is
between criteria or/and criteria--
both conditions are going to have to be true. So let's build this
awesome formula. Hey, we're going to use
COUNTIFS, criteria range 1. We're going to have to
use the whole column, so I click in the top cell,
Control-Shift-Down-Arrow. But here we need to lock
the cell references. So what do we do? F4 key. And check that out-- F4 key not only jumps
back to the active cell, but it puts the dollar signs
in to lock the cell reference. We have to lock that because
we're copying this down. If we didn't, then that blue
box would move as we copy down. We type a comma to get
to the next argument. Now we're going to need
greater than or equal to 0, so I type greater
than or equal to 0. But that won't work. You have to put the comparative
operator in double quotes. Now this is text
inside of a formula. This is the first
time we've seen this. And that's not a problem
because we saw up here how comparative operator and a
number, it is considered text. And that's exactly
what COUNTIFS needs. Now, somehow we have to
join that with the lower. So we use Shift-7. That's the join symbol,
also known as ampersand. Once we do that, we're allowed
to click on the lower number. That's a relative
cell reference. So as we copy it down. It'll always move
to the new lower. Now check this out-- that join symbol
means it's going to put both of those things
together and create one thing. Watch this-- I'm going to
click on the screen tip. And I'm going to hit the F9
key to evaluate this and see if that's actually true. Now watch-- I'm going to hit F9. Oh, look at that. In double quotes, comparative
operator and number, it's exactly what COUNTIFS
needs for this criteria. Control-Z, do not leave
that hardcoded in. Because if you copy
this down right now, every single lower
limit would be 0. Now the beauty of
this construction is now we have the comparative
operator in the formula, but that is free to
change as we copy down. Now we need to get to
our criteria range 2. We're going to
repeat the scores. Click on the top cell,
Control-Shift-Down-Arrow, and F4. Not only does it jump back, but
it locks the cell reference, comma. And now in double
quotes, less than, in double quotes, and
join with the upper. That is an amazing formula. Very convenient
because now we can have all of our different
upper and lower numbers, close parentheses, Control-Enter. And watch this-- I'm going
to point to that little fill handle. And when I see my crosshair, I
double click and send it down. Wow, look at that. I go to the last
cell and hit F2. Look at range finder. It got the orange
one, the lavender one. That's the lower
and upper limit. Comparative operators
are still perfect. And the blue ranges
over here are locked. Control-Enter. We'll see how to create
this chart in chapter 2 in our textbook. Wow, that was a lot about
formulas and formula elements. Now our next topic we want to
go over to Data Analysis Sort. Let's come over here
and click our arrows to expose some more sheets. We want to talk
about data analysis. Now we have a few sheets
for data analysis. I'm going to click on the Sort. Now data analysis, the
essence is, oh yeah, going from raw data and
creating useful information for decision-making. Now we're going to look at a few
things and a few different tabs for data analysis. I want to click on
the Data tab and I'm going to right
click on Collapse. And here in the Sort
and Filter group, we will use sorting
often in this class. We will occasionally use
Filter, we'll occasionally use Text to Columns. Now Data, right,
that makes sense-- tools that deal with data. The other ribbon
that we're going to use a lot for data
analysis is over on Insert. We're going to see in the next
couple sheet tabs, pivot tables and charting. But we're going to stay over
here on the data and first look at sorting. Now let's scroll down here. Now sorting is the simplest
of all data analysis features. You think, we want to organize
these smallest to biggest or biggest to smallest. It doesn't seem
like data analysis, but it is organizing the
raw data in a certain way. If you want to see
the biggest one on top and then the next biggest one-- boom, that is a simple way
of doing data analysis. Now when you're doing sorting
and filtering and pivot tables and all
those things, you've got to have a proper data set. Remember, empty cells
all the way around. Field name at the top. Now we click in a single
cell when we do data analysis and use our features
up in the ribbon. It doesn't matter if we're
going to sort or filter or pivot tables. So I have a single cell, and
it doesn't matter which one. Now I'm going to
go up to the Sort. And I want to see the smallest
one on top, so I hit A to Z. Instantly, wow, lots of 0's. That's pretty bad. And as you scroll down, you
can see the scores are sorted. If we do Z to A, that
brings the biggest on top. Now we have text, we
might want to go A to Z. That's of course going
to bring the first letter C, and then the next letter
F. If I do Z to A, notice a single cell,
and instantly it sorts. Now sometimes you
have multiple columns. Here, we want to see all
of the Chevys together. But then I also want to
see within all the Chevys and then Fords, I want
to see the number sorted. Now this means we have
two columns to sort. The trick is is if you
want your major source or your final sort,
you have to do it last if you're using these buttons. So I simply click in a single
cell and check this out. Just to see that when
I sort this column, it actually sorts
the entire record. Let's go ahead and highlight
one of these records and add some yellow, either
here or over on the Home tab or right click. I love this mini toolbar. That means if we sort on the
amount column and the record is to remain intact, that
yellow record will be intact after we sort it. So you ready? I'm going to go over to Data. I want the biggest on
top, so I'm doing Z to A. Well, the biggest one is on
top, but let's just scroll down. Sure enough, that's
pretty amazing. Now did you see what we did? We clicked in a single
cell and instantly, because we were sure to have
empty cells all the way around, field names at the top, it
knew how to sort this data set. Now I'm going to come
over to this column and sort it a second time. Once the first column is
sorted, it remains sorted. And when we sort this,
you won't believe this. Let's go to A to Z. Instantly,
all the Chevys are together. But check this out-- we have a second sort
from biggest to smallest. So I did Z to A on the
first column, and then A to Z on the second column. So sorting, pretty convenient. It is in essence the most
basic type of data analysis. Another basic type
of data analysis, let's go over to DA Filter. Raw data into
useful information, sometimes we have
a big data set. And you're given the task of
just looking at some of them. All we want is Lisa
E.'s records only when the sale was greater than 500. Now I could use sort
here, but then I'd have to go to the
middle of the data set and try and find Lisa E.
and then all the ones that are greater than 500. So check this out, they
have a great feature. Wait a second, this is a data
analysis feature, got to check. Do we have field names
in the first row? Yes, we do. Do we have empty cells
all the way around? Well, I don't know,
I better go look. Watch this, I'm
going to do a trick, I'm going to Control-Down-Arrow. Wow, down to the millionth row. Because we got to
the bottom, that means there wasn't anything
there in that column. Now watch this-- I need to get back
up so I'm going to click one cell over
because I know there's a column with stuff up there. Control-Up-Arrow. That's pretty amazing. So wow, over 7,000 records. Control-Up-Arrow. So we have a proper data set. Remember, a single cell for
all of our data analysis features-- sorting,
filtering, pivot tables. So we want to filter,
so I click in any cell. Remember, sorting it
matters because we wanted to tell the column. But here for filter,
just go up and-- boop-- instantly, we can
see our filters. Now look at this, we
go to the dropdown. And actually, the filters
since 2007 Excel are amazing. Now we're just going to
do a basic filter here. We're going to uncheck
Select All, scroll down, and say, oh, I only
want to see Lisa E. This is one criteria
or one condition for filtering the data set. Instantly, I see all
of the Lisa E.'s. Now how do I know it's filter? A few ways. Notice there's a
filter icon there whereas here is a dropdown. But the more obvious
way to tell is look-- we have sky blue is the
color for our row headers. Not only that, but 14, 37, 65-- there's lots of missing rows. They're not deleted,
they're just hidden. Now we come over. We're going to apply a
second condition or criteria. I'm going to come up here and
for each one of the data types, and here we can clearly
see there's number. Over here, if we were to click
this, we can see there's text. If we were to come over to the
date column, oh, look there's a date filter. Oh, you can't see that--
it's off to the side. Now I'm going to have
to right click, Insert. Just for a second, you could
see, oh, there's date filters. I'm going to Control-Z.
Let's come over here. The filter senses
that this is number, so we're given some
special options. Number Filter, and you've
got to be kidding me, Equals, Does Not Equal,
Greater Than-- that's what we're looking for. If we wanted to
find exactly 500, we use Greater Than Or Equal To. I'm using Greater Than. I'm simply going to type in 500. And by the way, this is one
of the few dialog box where we do not see our Collapse button. So we can't attach this number
to some criteria or conditions in the cell. I'm going to click OK. And instantly, there's
all of Lisa E.'s records greater than 500. And so for data analysis,
we've done something simple-- eliminated all the records
we're not interested in. So sorting and filtering,
those are two basic types of data analysis. Now let's go over to
Sheet DA PivotTable. Now I'm going to click
my arrow, raw data into useful information. Well, guess what? The pivot table is the most
powerful feature in Excel. And here are the steps
we're going to go through. I'm going to come down here. I want to create from this
raw data in a proper data set one, two, three reports. Now, no problem. We could do these with formulas. And formulas have their place. And we'll do both formulas and
pivot tables in this class. But you're not going
to believe how easy it is to go through this raw data
and create one, two, three reports with our pivot table. The reason it's the
most powerful feature is because it does
amazing things quickly. Now let's look at the
form of these reports. Because one of the
tricks to understanding how a pivot table works is
looking at what your end report needs to look like. Well, it looks like we need
a total for all the dates. If I click on this top cell
and Control-Down-Arrow, you see there's only 44
records in the small data set. But what do we do? We had to go through and find
all of the 10/20's and get all of the numbers
and then add them up. So really we're not
adding up all the numbers from this column. We're adding up all
the numbers given one condition or criteria. So for here in this
cell, I'm adding just the dates for 10/20. In this cell, just
the dates for 10/21. So we're adding
with one condition. Not only that, the second
thing you want to realize is that this is a row, this is
the second row in the report. This is the third row, this
is a fourth row, fifth row. And what sits at the
front of each row? The actual criteria. So that criteria or condition
says which numbers to add. Let's look at this next report-- same idea. We don't want all the
numbers from this column, we want just the
numbers for Northwest. So manually I'd have to
go Northwest, Northwest, Northwest, get all of
these numbers somehow, and then add them. Doing that manually,
you'd never do. Doing it with a formula,
you can do that, and we'll do that in this class. But doing it with a pivot
table is just automatic. So that's a calculation
with one condition. Here, add up all the Southwest. Here, add up all the West. But remember, this
is sitting in a row. The condition or criteria
as at the head of the row. Finally, we want to do the
same thing for a sales rep, get a unique list of sales
rep from this column, and add up given one condition. You ready? You're not going
to believe this. What's our rule? Proper data set? You got it. Field names at the top, empty
cells all the way around, records in rows. Now click in any single cell. The pivot tables should
be on the Data tab because it's the most powerful
data analysis feature. But Microsoft put it on
the Insert, PivotTable. So I'm going to click-- boop. Now I want you to tell me
why did it guess right. It guessed right
because, yep, I heard it, it's a proper data set. And there's empty cells
all the way around. So it got that step right. The only thing to this dialog
box we need to worry about is where do you want to put it. On a new sheet? I'm going to put it
for this first example here on an existing sheet. Click in Location, there's
that Collapse button. That means you can attach
this text box to the cell. So I'm going to click
on the title bar. Now I want to click in cell I22. And since our report
is going to be small, it's OK to place it right
here next to some other stuff. I'm going to click OK. Now instantly what shows up? Pivot table fields. So one of the reasons to learn
the synonyms, column header, field name, variables is
because if we're in Excel we need to know that the fields,
that's all of these things. In science and the
textbook they say variable. Sometimes people
say column headers. But here we need to know
all three of those words. So what is this right here? This is a field list. We have all of our field. Now remember we talked about
the shape of the report. It looks like the date is
going to be in the row area. And it looks like the sales-- that came from this column-- the sales are going to be
down in the values area. And they have that
little symbol, sigma. Now pivot tables
do not only add. That's the most
common calculation, but they do other
calculations too. So watch this. You simply take
whichever field you want, and I'm going to take
date to start off. And just by clicking
and dragging, now notice it says no, no, no,
with that icon, no, no, no. But right, oh, I could
drop it in the filter, I could drop it in the column,
I could drop it in the values. But no, I want my
criteria condition at the head of each row. So I drop it there. You see that? What did it do? The pivot table is
just flat out amazing. It went through
this whole column and gives me a unique
list, also called a distinct list,
one of each date. We didn't have to
manually do that. The pivot table did it for us. Now we drag sales
down to values. Just like that, three clicks. So I open up the PivotTable,
drag, drag-- boom. Now that's not
the end because we need to always do a few things. If we're creating
useful information, is that a useful name at
the top of the date column? No, it's not. Unfortunately, that's
the default behavior. So every time we
create a pivot table, we're going to have to come
up to our pivot table context sensitive ribbons, go to Design,
go over to Report Layout. And you have to show in
either outline or tabular. Now we'll do so many pivot
tables in this class, you will have the hang of it
after the first or second week. You can pick either one. I'm going to click Outline. Oh, that's much better. Now it says date and
we see our dates. The next thing is, we want
to number format this. Now I'm going to show
you the method that allows us to change the
function, the name at the top, and the number formatting. And the trick is we right
click in the values area. Right click, and the
one-stop shopping is Value Field Setting. Value because it's the value
area, field because it's a field, settings
because this is where you go to change whatever you want. Click, and here's
our dialog box. This is an amazing dialog box. We can type anything we want
up there, change the function, and change the number format. Now I'm going to start
with the number format and I'm going to go boop. And I'm going to choose
Currency or Accounting because this is dollars. We can show how many
decimals, 0, 1, or 2. I'm going to show 0 since
there aren't any decimals. And click OK. So it hasn't taken effect
yet because we just did our first step. SUM, we want that, but
we could COUNT, AVERAGE, MAX, MIN, and other functions. Now I'm going to highlight
up here and type total sales, and Enter. And look at that,
there's our first report. Now let's try this again to
create our second report. You ready? Click in a single cell, it
doesn't matter which one. Don't do this, don't do this. Don't accidentally
select just two cells because then it will think
you want just those two cells. One cell, Insert, PivotTable. I'm going to say
Existing, Location. Now stacking pivot tables on
top of each other like this can be dangerous
unless you're never going to move the pivot
tables or pivot them. We're simply going to have
three pivot tables right on top of each other, so it's
OK for this example. Click OK. Now what do we do? We drag and drop. Filter, which we'll see. Column, Row,
Values-- we're going to have our region in the rows. Instantly we get a unique list. We drag sales down to Values. Instantly we get
our SUM calculation with one condition or criteria. We don't like row labels, we
go up to Design, Report Layout. Tabular or outline, I'm going
to do tabular this time. You see it keeps the lines. Up here, it doesn't
keep the lines. Finally, right click,
Value Field Settings. This is the one-stop
shopping for value area. I want to keep the SUM function. I want to change the number
formatting, currency, two decimals only. Click OK. I want to come up here,
total sales, and Enter. Notice the OK button is
blue, it's highlighted. Actually any dialog box when
the button is highlighted, that means Enter will enact it. So when I hit Enter, it
takes all of those settings. Now let's do this again,
let's do this in fast speed because pivot
tables are amazing. We don't even have to
look through the column and get a unique list. We click in a single cell,
Insert, PivotTable, Existing Location, Location. Click in the cell we
want, OK or Enter. Drag sales rep down to Rows. Instantly, your unique
list, sales down to Values. Instantly, the sum with
one condition or criteria. We don't like row labels. Design, Report Layout,
Show in Outline. Right click the values area. Notice if you
right click here, I don't see Value Field
Setting because I'm in the wrong part
of the pivot table. Right click, Value
Field Settings. Number formatting, Currency, no
decimals, click OK, click OK. I left it Sum of Sales. But watch this, I can
click right there and just type total sales. You can't type sales
because sales is the name of one of the field. But I can type total
sales and Enter. That is amazing. So the pivot table really
is a very powerful feature. Now we want to go look at
a few more pivot tables and see even more
amazing things. DA PivotTable 2. Now here, here's the thing. We're looking at the end
result of the report we want. This calculation
right here is not done with a single condition
or criteria, it's one, two. Now what's at the
head of the row? Date. What's at the head
of the column? Region. That means when we get to
our pivot table field list, we're going to drag the region
field to the column area, the date field to the row area,
and the sales to the values area. Now get this-- this is
two conditions, right? That means we are not adding
up all the numbers-- we're only adding up for that cell when we
find West and the date 10/25. So here as we go through,
here's in the Region column a West, that means true. This record gets a true for
that one condition or criteria. For 10/25, we found
it right there. That means we got true, true. We're now allowed to use
that number for adding. You can see there's
only one of them. For this calculation up
here, 10/20 in the West. Remember we had to go through
here, we found a West. And we also had to
find a 10/20-- boom. Two trues, so we're
allowed to use that number. We keep running
through West, 21, no. West on the 20, those
two conditions are met. So we're allowed
to use that number. But there's more, and we
could go all the way through. Here's another West on the
20th, another West on the 20th. So you get all of those numbers
by looking through the day, finding 10/20, the
region, finding West. And then going through
and finding the sales and adding them. So we're not adding all of them. In this cell, we're adding
with two conditions. The key though is
we don't necessarily have to think of it in
that explicit of terms. But we do have to realize
that date goes in the row, region goes in the column,
sales goes in the value. So you ready? A single cell. Insert, PivotTable, Existing
Worksheet, Location. I'm going to select K14. Click OK. Watch this, you drag
the Date down to Rows. Instantly a unique list. Region down to Columns. You're kidding me, look at that. Instantly a unique list
across rows and columns. We drag sales down to values. That is amazing. We're not done. Row label, column labels. Not useful information, so we
go up to Design, Report Layout, Show in Outline or Tabular. I'm going to Show in Tabular. Right click inside of the value
area, Value Field Settings. number formatting,
Currency, zero decimals. Click OK. We're going to keep
the SUM calculation. Later, it's totally simple
to change it to AVERAGE or whatever you want. And watch this, I'm going
to show you a trick here. Sometimes you do
want to see sales. But if you type sales, that's
the name of the field name so you're not allowed to do it. And here's the trick-- type a space. A space counts as a character. It means that this is
different than the field name. That's just a cool
pivot table trick. Click OK, and
instantly-- look at that. Notice the pivot
table field list when I click outside the
pivot table goes away. Sometimes if you
accidentally click the close, like, hey, how do I get it back. Well, you can look through
the context-sensitive ribbons to find it. It's up in Analyze. But I always like
to right click. Right clicking usually
gives you what you want. And sure enough, down at
the bottom, Show Field List. Now this field list is
for that pivot table. And I click outside here, it'll
come back if I click in there. But now I can come over. I need to create my
second pivot table. Sales rep, region, sales. Ready? Insert, PivotTable, Existing
Worksheet, Location. I'm going to select
K23, click OK. Region down to Column. Instantly we get a unique list. Sales rep down to Rows. Instantly we get what's called
a cross-tabulated table. Sales down to Values. Wow. Design, Report Layout. Show in Outline, either
one, tabular or outline. Right click, Value
Field Settings. Number, Currency, zero decimals. OK. I could've hit Enter,
Enter there too. That is simply amazing. I'm going to click outside. Notice when I click here,
it shows me the field names for that pivot table. When I click over here, it shows
me the field names for that. When I outside, it goes away. That is just amazing. So many tables that
we do in statistics are cross tabulated,
and that is amazing. Now I do want to show you
something about a pivot table and why it got the
name pivot table. So you make this
beautiful report, you've done your data
analysis, the boss is happy. But then the boss goes,
hey, wait a second. I didn't want region
across the columns, I wanted it on the rows. Watch this. I'm going to click and drag. And this is why
it's called a pivot. I'm in a drag Region
below SalesRep, and instantly it turns
into a vertical table. That's why it's
called a pivot table. That's also why it's
dangerous to put pivot tables next to each other. If we pivoted this table and
drag region down to row area, it would ask us, it
would give us a message. Are you sure you want to
destroy everything below? But check that out,
that is amazing. That's why it's called pivot. Now if I want to bring
SalesRep back up to Columns, I just click and drag
and instantly I've pivoted the report. I'm going to drag it back. SalesRep and Region. That is amazing. Hey, let's go over to our
next sheet, PivotTable 3. Now here we're
given-- and this will happen a lot in
this class-- we have survey data for statistics. We ask everyone their
preferred Cola or yes or no to a particular
question or whatever the survey results might be. We have this huge table
here, Control-Down-Arrow. So I have well
over 500 responses. Control-Up-Arrow. And all I want to do is count. We could use formulas
and functions. One of the problems is, I have
to look through this whole list and go and get a unique
list of each item. But forget it, the pivot
table does that for us. Click in a single cell. Now we're going to do
so many pivot tables. We're going to
learn the keyboard. But we got to learn. If I go to Insert
and hover my cursor, remember for the
AutoSum there was in our screen tip a keyboard. There is no keyboard
for the pivot table. So we're going to have to learn
what are called Alt keyboards. That's A-L-T. Watch this-- my cursor is
sitting right there. And when I hit Alt, what
happens to my ribbons? That is so cool. It shows me all of
these screen tips. So what's the letter for Insert? It's N. Now Alt keys are succession. I hit Alt and I let go. Now I'm going to tap
N and then let go. Now I haven't done it yet. Watch this-- I'm going
to tap N. Instantly a bunch of new screen tips
inside the Insert menu come up. So now it's V.
When I hit V, wow. I clicked Escape there. The reason that they
designed it that way, Alt, is because Alt keyboards are
for you to teach yourself. If you're using
Excel all the time on the page layout
or formulas, you teach yourself the keyboards. So Alt, N, V is what
we're going to use. So from now on, that's
what we're going to use. Alt, N, V. Now I want to put it
on an existing sheet, so I'm going to click
Existing Worksheet, Location. I'm going to put it in G14. Click OK. Now wait a second. We have only one field. How do we do a pivot table? Watch this, I'm going
to drag it down to Rows. Instantly a unique list. Drag it down here. Now, remember data types. What kind of data
is this over here? It is text. Notice I didn't let go. I grabbed it but
I didn't let go. When I drag it down here,
it will automatically count the text items because
it knows that it's text. So when I drop it here,
instantly I get a count. See, it says count. When the data type is a number,
it defaults to SUM function. Now it's easy to change
that-- we just right click, Value Field Settings. But let's fix this up, Design. I'm actually going to close
the pivot table field list. Go up to Design, Report
Layout, Show in Outline. I actually don't
want to do this, so I'm going to click Escape. The reason why is because if I
drop the field name right there it'll be really wide. And I don't want
that long field name. So I'm going to do the same
trick we learned earlier. I'm just going to
type here, Cola. And then over here I'm going
to type frequency, count, and Enter. Now I can come up between
H and I and point. And when I see that cursor,
I can click and drag to change the column width. Pivot table is pretty amazing. We had a single column
of text and-- boom-- we get a unique
list and a count. Now we have one last pivot
table we want to look at. I'm going to scroll over
and go to PivotTable 4. Now here we have a data set--
company name, sales rep, customer, zip code, and amount. Control-Down-Arrow, so we
have about 200 records. Control-Up-Arrow. And all I want to do is I want
to count, get the max and min or amount for each zip code. So we're going to drag
this to the row area. And then we're going
to have one, two, three calculations on the amount. Now notice we don't
have a preview here. After using pivot
tables a while, you get the idea--
you can visualize the report in your head
and then create it. A single cell, Alt, N, V.
Click Existing, Location, F15. Click OK. Now the trick here
is we're going to drag zip code down to rows. Instantly we get a unique list. But now since we have three
calculations on amount, we go drag one time,
drag two times, drag-- and it might not
have enough room so you can point to the
bottom of your field list and click and drag, looks like
we have plenty of room there-- and drag a third time. Now I'm going to close this. Now just as we did
in the last one, I don't want to have
customer zip code. I'm just going to
type zip, and Enter. We would want customer zip
code if the people reading the report didn't know it was a
customer, because we're always trying to create
useful information. But this is just
for us internally. We know it's zip,
so I just put zip. Now I have to come in
one, two, three times, go to our Value Field Settings. Right click, Value
Field Settings. First, we're going to
change it to Count. Now actually in the
pivot table field list, if you'll look right there
and then scroll down, there's a Count Numbers. So actually Count
Numbers and Count are different than
the Excel functions. This would be COUNT in the Excel
spreadsheet using functions. This would be COUNTA. I know, it seems strange. Now either one of
them will work. But since I have
numbers, I'm definitely going to use Count Numbers. I do not want any number
formatting because counting gives us whole numbers. Click OK. Count, I don't like that. So I'm going to click in the
top cell and just type count, and Enter. Now come over here, right
click, Value Field Settings. We want max. I'm going to change
the function to Max. I'm definitely going to come
down and do number formatting. These are dollar amounts. And then I can
backspace through here. I don't need to see maximum
amount to, and Enter. Right click, Value
Field Settings. This is so amazing. Min, number formatting. We want to indicate that
these are in dollars. We can show the unit. Get rid of all that. Just show Min, and Enter. That is pretty amazing. So it looks like if
we're analyzing this, we can clearly see that
98174, we had the most sales. 15. If we we're looking at
an advertising campaign, we'd see that the zip code. If we were mailing stuff out,
that advertising campaign didn't work. Or maybe it's some other cause. But that's a pivot table
to count min and max. All three calculations
are calculations based on a single condition. So we didn't look through
and find the max, the min, and the count of all of them. Just some of them based on
this criteria or condition in the row area. So pivot tables. We'll use them so many
times in this class. Now I'm going to
scroll over here. We have two more topics. We want to talk about chart. Now I'm going to click
on the Sheet Charts. Now the first thing
we have to know, and the most important
thing for this class, is charting is 100% new in 2013. If we go over and start
clicking on these charts, the dialog boxes, ribbon
tabs and task panes that we use to create
our charts are just totally different
than earlier versions. So a different version
of Excel is not going to work for this class. Now what is a chart? If we look down here,
we have two charts. We have a column chart
and we have a bar chart. I'm going to move it
up here to the side. And what is it that
they do so well? Well, look at down here,
here's all the data. Oh, yeah, raw data. It's just hard to make
sense of raw data. But charts take numbers and
visually articulates them so you can get a quick
impression or see the trends or what's happening. We can clearly see that the
yellow bar is the tallest. The yellow piece is the biggest. The drop right here for the 22nd
of October, that's the lowest. The trend for these scatter
plots tends to go up. That's why we do charts-- quick visual impression. Now I want to briefly go
through each chart type. But before we do that,
we've got to talk about the number 1 rule for
charting-- it's no chart junk. Everything that's
in the chart has to help deliver the message. No unnecessary repetition. If you look out there in
charts, sometimes even in magazines,
professional magazines, you'll see charts
that are badly done. We're not going to do that. We're always going
to try and articulate our quantitative or number
data visually in the most effective and efficient way. Now columns, columns are
vertical, bars are horizontal. Now there's a big problem-- the textbook, they always
call this a bar chart. Now that's fine. But in Excel, we have
to know whether to pick the column because it
says column or the bar. That's called bar. So columns are vertical. Columns hold up
Greek architecture. This is a bar, it's horizontal. Now what do columns and bar do? Well, they do the same thing. They show some amounts
over a set of categories. Now, the difference
between these two is that sometimes the bar
chart, the horizontal, will show the differences
more forcefully. So if you're trying to emphasize
the difference, there it is. You could see that the
difference is not as much here. But with the
horizontal, it tends to show it more forcefully. The other thing is sometimes
if you have really long labels, showing a bar shows
the long labels. See here, we don't have
much room to show our label. So showing amounts
across categories. Well, look, the pie
chart is an alternative. And I want to bring this up
here and show you something. The pie chart shows
us the same data here. And actually in recent
years, the column chart tends to be the preferred
chart over bar and pies. The thing about the
pie chart is sometimes it's harder to distinguish
the differences in sizes. Now here we happen to
have a big pie slice. But with columns and bars,
it's almost always easier to see the differences. So in some ways, pie charts
have fallen out of favor when it comes to visually
articulating quantitative data. Now they still look great. And in some cases when
you have percentages, it's the perfect chart. Another chart is the line chart. And I want to show the
line and the xy scatter next to each other. Because here's a big mistake-- sometimes people will
try and use line charts to articulate xy data. Here's the rule--
a line chart shows one number on the vertical axis,
and xy chart shows two numbers. You go out or along the
x-axis a certain distance, and then up or down a certain
distance on the y-axis. There are two numbers that
determine that point right there or that one right there. You can see that little
point right there, that's five hours spent studying. 61 score on the final. Whereas here that
point right there, it's just however high that is. These are categories
equal distance on the horizontal axis. There's only one number here. Hey, the line chart,
the xy scatter. What does the line chart do? It shows trends or patterns
over time or categories. Many times we see time
here because it really shows way down on the
22nd, way up on the 24th. So we much more quickly
can see boom, boom pattern, given this line chart. xy scatter, we will see
many times in many chapters, or we have xy scattered
data and it shows the dots. We look to see if
there is a trend. Is the trend tend to be
up or tend to be down? Or is there really
not a pattern? Now in this particular example
we have our study, independent variable. Score on your final,
dependent variable. It depends how many
hours you study on what your final
score is going to be. So here it tends to look
like the more you study, the higher your
score is going to be. Well, we have lots of chapters
in the textbook about this. But here we're just pointing
out what that chart does. Now I want to create
a couple charts here. Now the first chart we want to
create is that column chart. But look at this,
this is raw data. We actually need to create
a pivot table first. And then from the pivot
table, make our chart. But you're not going
to believe this. There's so many amazing
new things in Excel 2013. Let's go look, Insert. In the Chart group,
Recommended Charts. If you click this, you
do have to be careful. There's some charts
you don't want. But look at this one
when I click through and I find sum of
sales by sales rep, you're not going
to believe this. That recommended
chart only in 2013 and later, it builds
the pivot table for you, and then will build this
chart from the pivot table. Now when you use
this, it's going to automatically create it on a
new sheet, which will be fine. You can see that little icon
for a pivot table there. Now I'm going to click OK,
and it'll go to a new sheet. I immediately want
to name this sheet. I'm going to double click
and call this chart practice, and Enter. Now I don't need
this field list, so I'm going to close that. There's the pivot table,
there's the chart. Now we do want to
fix this chart up so it articulates our message. All we want is the
height of the columns with the numbers on top. Now the first thing is these
gray areas, these are fields and they're great-- you
can use them to filter just like in your pivot table. But I'm going to right click,
Hide All Field Buttons. The next thing is there's
different elements in the chart. Chart title, the
axis with numbers, the axis, the horizontal
with our categories, the column heights, grid lines. Now I'd like to start
with the columns, and I'd like to right click. And notice it says
Format Data Series. Now I want to show you the
trend for chart elements. If I right click the title,
oh, it says Format Chart Title. Right click the
axis, Format Axis. So each one of the
elements if you right click will allow you to do formatting. But let's select the columns. And when we select
the columns, you want to make sure
they're all selected. If you select just one
and then select it again, you see it's like a
slow double click-- only the one column is selected. That means you can
format just that. That's not what I want. I want to click back out here. Now select, and
they're all selected. Now we could right
click, but every one of the elements all you
have to do to format them is use the keyboard Control-1,
and immediately our task pane opens up. This is brand new in 2013. They used to show
up in a dialog box, but now it's in a task pane. Now what they did
is they make icons. This is the Fill button,
this is the effects. This has options like
gap width for the column. So you're expected to
know what the icons mean. Now for me, I'm
still learning them, so I oftentimes have to click
through to find what I want. But these icons are
going to be the trick. Fill, there is a great option-- Vary colors by point. And instantly the columns
have different colors. Now we want to do
some more formatting. One thing I want to do is I
don't want these grid lines, so I select-- and you want to make sure
not to select the outside when it's solid. You want the inside lines with
the little boop, boop, boop, the little bubbles. And then Delete
key to delete them. Sum of Sales by SalesRep,
that is a fine chart title. I'm going to come
over to the axis. I want to actually format this. Whoa, wait a second. Remember, if I come over to
the pivot table and right click, Value Field Settings,
I can change the Value Field Settings number formatting. Currency, something
like boop, boop. Click OK, click OK. And that will actually
flow through to the chart. If we were to select the
axis and then scroll down, sometimes these task panes
really you have to go way down. But down here under Number,
oh, I click the arrow and it didn't show me, but
this gap appeared here. And now I'm going
to drag this down. You could see it says
Linked to source. You can totally customize your
number formatting here also. Now task panes are new in 2013. But also, we have some options
here right next to the chart. Now notice if I click over
here, printing it out, or anything like that,
it doesn't show up. As soon as you
click on the chart, they expect that you're going
to do something like edit it or change it. Now let's look at here because
this is chart elements, and this is actually a
good feature in 2013. If I click it, we can add
things like data labels or later when we do error
bars in linear regression. So we can add things. And what they did-- the
idea in 2013 is they added. These things used to
be up in the ribbons, but now it's closer
right to the chart so you don't have to go as far. Now I'm going to
click on Data Labels-- boom. Now I'm going to click
over in the chart. This thing doesn't
go away sometimes, so now I'm going
to click Escape. Now my question, is there
any chart junk in this chart. Well, yes, there's some
unnecessary repetition. If I have the numbers here, I
probably don't need them here. So I'm going to click on the
axis and use the Delete key. So that chart is
looking pretty good. Now I want to change
the size, so I'm going to point
right to the corner. When I see my diagonal arrow,
I'm going to click and drag in. Now I want to do three things. We talked about
column, bar, and pie. So I want to cheat. Actually, this is a great trick. I want to copy this. Now you want to make sure that
you're not selecting something inside the chart, but you want
to make sure it's the outside and the outside
edge is selected. And then we're going
to copy, Control-C. And now I'm on a paste
down below, Control-V. Right click, Collapse Ribbon. And now I want to
change this to a bar. And this is a great trick. You might have the same
data, but multiple charts. Now we could go up to Design
and change chart type. Or we can simply
right click inside of our chart and say,
Change Chart Type. And just like
that, this pops up. I'm going to come down to Bar. None of this three-dimensional
chart junk, just the flat one. And click OK. There is that emphasizing
the differences. Oh, that's very nice. Now I'd like to make the
gap width between these bars smaller. So now I'm going to select. Now we're going to have to
find where the gap width this. And again, you might have
to click, click, click. But this has to do with
the actual bar, so I'm going to click here and
sure enough, gap width. Now one thing you do not want to
do with this categorical data-- this is text data,
word data-- is you don't want to
touch the columns. We need some distance
between them. And in chapter 2, we'll
see the difference between discrete
and continuous data. So if we had categories
here like 0 to 10, 10 to 20, then the columns could touch. So when we have words
or text as our labels, we don't want the
columns to touch. But I like it a little
bit less gap width. Now one more thing-- I want to copy this chart. Again, I'm going to make
sure the outside is selected, Control-C. And over here
I'm going to Control-V. And now right click,
Change Chart Type. I'm going to change
it to a pie, flat one. The most horrendous violation
of 3D is pie charts. Because when you tilt
a pie on its side, you distort the proportions. So really, you don't want
to ever use a 3D pie. Our flat pie is just fine. I'm going to click OK. Now one nice thing
about the pie, unlike some of the
other charts, is when we click in our pie and
scroll over a little bit, this plus-- we can come to Data Labels,
and there's an arrow. We click on that arrow, and
it will open up more options. Now the more options
will appear over here. Golly, that didn't
work, so I'm going to have to start
clicking up here. I'm going to click on
the actual columns, even though they are pie pieces. Go down to Label Options. And we can click Percentage,
and it will calculate the percentage for us. So that's actually kind of cool. Pie, bar, column. Let's go back over
to our charts, and we want to look at the
line and the scatter plot. Now if we have this
data set and we were to go up to Insert,
Recommended Charts, the line chart is not
going to be there. So this is an
example where we're going to have to create a
pivot table from scratch. But no problem, we
know how easy it is. Existing Worksheet, Location. I'm going to put
it right in F53. Click OK. Now I'm going to drag date down
to rows, sales down to values. Close the pivot table list. Design, Report Layout,
Show in Tabular. Now I can click in a single
cell, go up to Insert. And we want line. We have one number
that we're going to show on the vertical axis. So I'm going to click
boop with markers. Chart junk, so I delete. Click on the total chart
title-- that's chart junk. I click up in the formula
bar, I type in equal sign. I click on the cell A50. I can see my formula. Check that out, it's polite. It says, hey, I got the A50
from the sheet called Charts. There's this sheet
name right there. Now I hit Enter to put that
formula right into the chart title. Right click, Hide
All Field Buttons. Maybe we'll select the
grid lines and delete. Click on the axis. Come over to the column. You might have to click
through for this one. I'm going to click Number,
dropdown, Currency. And I'm definitely going
to select zero decimals. Tab. So that's a basic line chart. And it shows us some
trend over time. Very quickly looking at this, we
know the 22nd is the smallest. Now I'm going to point to the
corner and click and drag. And moving charts is tricky. Again, just like
when copying, you don't want to click
on anything inside. You always want to click
to the outside edge. That cursor right there
is the move cursor. I'm going to move it right. Now I'm going to
scroll down a bit and we're going to
do our last chart-- x, y. These are from a sample
from class, self-reported how many hours were studied
and the final score. So we want to plot this xy,
a bunch of little markers or dots on our chart,
and see if there is an apparent relationship. Now the important thing about
xy is that the x comes first, it's always to the
left, and then the y. Later we'll see you can
even have multiple y's. I click in a single
cell, Insert. And there's the scatter chart. Don't click line. Boop. For sample data or collected
raw data, we use our markers. If we have a model like a
fixed cost accounting model, we'd use this. But to use a line on
collected sample data is not meaningful, so I'm
going to select this first one. And there it is,
the basic chart. Now there's a big
problem with this. If you were to look
at these numbers, I have no idea what they are. So this is a case where
you have to label. This came with not enough stuff. We're not so much
worried about chart junk. Hey, here's that plus. This is really cool. Now in 2013, we just
check and there it is. Now we're going to do the same
trick because it's not linked. So I'm going with the solid
line come up to the formula bar, equals sign. This is y, so I click
on that, and Enter. There's the formula, Enter. Click on the axis title. In the formula bar, equals sign. Click on the x, the
horizontal is always x. And now we hit Enter. Click on the title up in the
formula bar, equals sign, and A76. I already created a nice
label for our chart. Now I like the grid lines,
it helps us pinpoint things. One last thing-- right
click one of the dots. Add Trendline. And later in chapter 12 and 13,
we'll see how amazing this is. Linear, it actually did a bunch
of heavy-lifting statistics to estimate that line. We come down, scroll. And I'm going to say display
the equation in R-squared. We'll talk all about
that later in our class. That is profound. Now we wouldn't want to use
these numbers and calculations. Later we'll see how
to use functions to look at our
actual data inputs and calculate all these numbers. But for visually portraying
our quantitative data, that is simply amazing. So charts-- really
important for our class. Now we have one last topic. I'm going to use my
arrow to scroll over, and we're going to go to
the Sheet Number Formatting. And what we want to talk
about is number formatting. The first thing is,
if we have some sales that we need to
enter, watch this. I'm going to enter dollar
sign, 13.25, Enter. Dollar sign, 10.00, Enter. Dollar sign, 34.10, Enter. Dollar sign, 54.00, Enter. Now what we did there is
we entered the dollar sign and the decimals by hand. We do not need to do that. Let's see how to do this
a much more efficient way and learn one of the awesome
features of number formatting. In this cell, I'm going to
start in the top, 1,013.25. Notice I have to put the 0.25. But when I hit Enter, I
only have to enter a 10 here, because I'm going to let
number formatting do the rest. Now down here I
have to type 34.1, but I'll get that extra
zero and the dollar sign from the number formatting. And down here, look at this, I'm
going to type two characters. And now what do I do? I highlight. And up in the Home Ribbon,
Home Ribbon, Number group, I click the dropdown
and I can either choose Currency or Accounting. I'm going to choose Currency. Currency actually
has the dollar sign floating close to the number. But notice, it put
those dollar signs in. And I didn't have
to type the 0.00. So here when I type 10 and 54,
I avoided four extra clicks. Now I want to show you
just briefly the difference between accounting and currency. Accounting puts the dollar
sign fixed on the outside. Currency has a
floating dollar sign. There's some other
differences, but for this class we don't need to worry so much. Now I want to click in cell C10. And look up in the formula bar-- 10, that's all
that's stored there. Right here, well,
there's a 13.25 because we need
all those digits. But here, 54. Here, there's just a 34.1. All the rest is
number formatting. That dollar sign is
not in the cell-- it's on the surface of the cell. It's a facade. Not only that, but
how many characters do we have to type here? One, two, three,
four, five, six. What got put in the cell? Even though we typed six
things, only two things got put in the cell. So don't ever do it. It takes too long. We want to do things quickly. Now if we were to add
these up, watch this, this is a cool trick-- Alt-Equals, and it does
both of them instantly. So the first thing is we love
number formatting because it helps us save time typing. But here's another problem-- I'm going to highlight
both of these cells-- Alt-Equals is the
keyboard for AutoSum. Now wait a second. 13 plus 36 plus 97 equals
145 here, but 146 over here. What's going on? Well, number
formatting is a facade. So what happened? It looks like if we were
to highlight all these and go up to Home and
increase the decimals, it looks like someone
decreased the decimals. I'm going to
Control-Z in a second and reverse that and see. But notice, we were totally
fooled by number formatting. Really what's in this
cell right here is 12.15. But what happens when we
decrease the decimals? It just looks like
there's a 13 there. You can flat out look
up to the formula bar now and see that it's 12.5. Now I'm going to Control-Z,
Z, Z, Z. Here, I see a 13, but up here is a 12.5. Here, I see a 13, and
there is a 13 in the cell. You've got to realize that
number formatting is a facade. Sometimes what you see on the
surface of the spreadsheet is not actually
what's in the cell. And it can cause problems
with our formulas. That formula does not see
the number formatting. If it did, it would get
13 plus 36 plus 97 is 146. What that formula sees, if I
increase the decimals here, that SUM function sees
all of these numbers, including the
decimals, adds them up, and then when we
disguise the number by decreasing the decimals,
it just looks like it's 145. That's just number formatting on
the surface of the spreadsheet hiding the decimals. Now one way to tell for sure,
and when I get into trouble with number formatting,
I highlight them all and I go up to the Number
group on the Home ribbon and apply the General. The general number
formatting is the eraser. It wipes away all the
number formatting. And there you can clearly see-- boom. Control-Z. Now we want to
look at a few more examples of number formatting. Here's a well-known problem. You want to make a formula
equals the 100 times the tax rate of 10%. Control-Enter. As soon as you see 9.75,
you know there's a problem. I'm immediately going to
suspect number formatting. I could increase the decimal. And sure enough, someone
decreased the decimal so it looked like 10%. I'm going to Control-Z,
Z. If I click in the cell, I can see up in the
formula bar 9.75. So really the solution to this
is to increase the decimals. Don't make the
spreadsheet misleading. Here's another problem. I'm going to type 0.1. And notice, I'm not
going to type the lead 0. It's actually got general
number formatting. And if I needed to see the
decimal, I would increase it. But watch this-- here's
what you should do. If you enter the
decimal, then you're allowed to come up and add
percentage number formatting. Now if you use the dropdown
here, it shows two decimals. If you use that one right
there, it shows zero. And notice the screen
tip doesn't prompt you. It doesn't say I'm going
to show zero decimals. So I like to use the
dropdown here and say, percentage, two decimals. Now we can make some calculation
like equals $100 times 10%, and Control-Enter. Here's a big mistake
that happens often. Don't enter the whole number 10
and then try to go up to Home-- and I should right
click, uncollapse this-- and apply the
formatting percentage. Because what does it do? You can already see
the preview there-- it obeys. When you apply a
number formatting, it actually slides the decimal
and adds the percent symbol. That's facade. Underneath is the number 10. So don't add a 10 and then
add a number formatting like percentage. Really, and I'm going to go and
apply General, and then Delete. You either want to enter the
decimal and then the number formatting for percentage. Or you want to
format as you type. Now for one
percentage like this, it's OK to type in
some extra decimals. With our currency
example above when we're entering lots of data,
you don't want to do that. But when I enter 10.00%, the
actual number 0.1 will go into the cell because
I'm formatting as I type. Now I could prove
this to myself. If I look up into the
formula bar, see, I see 10%. I don't see the actual decimal. But the actual decimal is
what's in the cell, not that percentage. So watch as I'm going to go
up and prove it to myself. You saw me-- I typed all
of those decimals, 10.00%. But if you go back,
because what we did was add percentage number
formatting as we type. So if we go back
and apply General, oh, it wipes it away,
Control-Z. And then our formula equals $100
times 10% tax rate will work. Couple more examples. This is straight from
chapter 2 in our textbook. And it has to do with
number formatting. We will see how to count from
a data set, count the number, and then calculate
relative frequency. That's just going to be
8 divided by 40 is 0.2. 6 divided by 40 is 0.15. But the textbook is going to
show you this crazy thing. They want you to calculate
percent frequency. If I hit F2, they
multiply that times 100. Now there may be some arcane
examples where you should still do it this way. But the problem with this
is it leads to error. And I'll show you the
error in just a second. What you really
need to do is simply point your formula
over there and then add percentage number formatting. Now we'll look at this
example again in chapter 2, but I just want to show you why
this is not a very good idea. We see the percentage. And as someone who has
helped solve spreadsheet errors for decades, I know this
is a mistake waiting to happen. Because people will come
down and they will think, oh, OK, I have a sample of
500 students and the Cola that I want to estimate from
these percentages here-- I'm actually going to scroll
out so we can see this-- is I want to estimate how
many people from the sample will drink Bloxy Cola. So I'm going to say
equals 500 times the percent for Bloxy Cola. And instantly you
get the wrong answer. The correct way to do this is
to say 500 times our percent frequency. Based on our past
data, we estimated that 27.5% of the people
will drink Bloxy Cola. So when we take a sample,
Control-Enter, we're going to estimate that
137.5 people will drink. Now we could prove this-- equals the amount we just
estimated, divided by the 500, and add our number formatting. And that's the
correct percentage. So we'll see this example
again in chapter 2. Let's go down and look at one
last number formatting example. Now here we have some
amounts for each month that I want to calculate
the percentage change. I'm going to
calculate, hey, give me the end amount divided by the
begin amount, and subtract 1. Control-Enter. And what do we get? We get eight pennies. That's just number formatting. Don't forget-- the formula
looks at the underlying numbers and calculates correctly. What we see on the
surface of the spreadsheet can be incorrect sometimes. So our job in this case is to
simply come up and wipe it away with the general. Or if you want to show
it as a percentage, you could add the correct
number formatting. Now we can copy this down. Notice, there was lots of
extraneous decimals there. This number formatting doesn't
hack those decimals off. It just displays
only two decimals. Now that formula
right there, we're going to end this
video by coming. I'm going to click on the Sheet
Change Increase Decrease Sheet Tab. Now here we want to talk
about two different formulas for calculating the
percentage change. Here we have sales amount
for each year, 12, 13, 14, and we need to figure
out the percentage change from 12 to 13. The long formula is this--
equals, open parentheses, and you take the end amount, and
you subtract that begin amount. Close parentheses, that
becomes the numerator. That's the change, the
difference between the two. And you divide it
by the begin amount. Now when we Control-Enter,
we get a decimal. We could format that if we want. But I'm just going to double
click and send that down. Go to the last cell, hit F2. Clearly, we can see
our formula is working. But that formula involves
typing seven things. And we're going to look at a
little bit shorter formula. And here's the
formula-- equals, where we're going to
take the end amount and divide it directly
by the begin amount, and then subtract 1. I had to type five characters. Control-Enter, double
click, and send it down. You'll get exactly
the same formula. Here's the reason that
that formula will work. If we take end and subtract
beginning and divide by begin, well, this is adding or
subtracting in the numerator. So we can actually break it
apart and slap the denominator into both fractions. So this minus this
is the same as that. Oh, but what's anything
divided by itself? It's 1. That's why end divided begin
minus 1 is perfectly all right. Now if you wanted to
add some formatting, you certainly could. That's the way we can do it. Later when we do relative
frequency and then a column for
percentage frequency, we'll just have the formula. Look over there-- I already
preformatted this as a percent. Now let's just use that
same formula we just did. It took us seven keys
for that longer formula and five keys for
the shorter formula. So if this is the end
amount, I want to figure out the percentage decrease. So I'm going to take, hey,
the end divided by the begin minus 1. Control-Enter, 0.3. If I added a percentage
number format, it'd be like 30% faster. So I like that
formula right there. That was a percentage
change formula and it'll work for a
percentage change up or down. Now increase or decrease-- there's two ways
we could do this. Now here's the starting
amount, our investment at the beginning of the year. And here's the
percentage change. One way to do this is to say,
hey, equals the full amount. And then we're going to
add the change, which is the full original amount
we started with times whatever the change is. Now this is one, two, three,
four, five different things we have to do, put
into our formula, Control-Enter, double
click, and send it down. But there's actually a formula
that is more common, especially in finance. And we'll see it in this class. Now let's just look at how
we could derive this formula. We want to get the
end amount, so we took begin plus begin times
the percentage change. Now we can notice that begin
plus begin times something, well, we can rewrite
this-- begin times 1, well, that's the same as
begin plus begin times percentage change. Soon as we get to
things added together and there's a common factor
here, you can factor it out. And so begin is on the outside. And then in parentheses
you're left with the 1 plus the percentage change. This formula is a
world famous formula for calculating an
end investment amount. So I'm going to say,
equals the begin amount, times in parentheses
1 plus the percentage change, close parentheses. Now I have to do a little
bit more typing there. But this formula is so common
that when we see this formula for getting the
end amount, we just need to recognize that
it is true and valid. Control-Enter, and double
click, and send it down. Wow, that was an epic
first video for this class. All about an
introduction to Excel. Now this is the
only chapter where there's not going to be any
homework in the textbook. So if you click
over on Homework 1, it describes what you
should do at the top. And then here's the problem. And you do your
problem on the sheet. Now because these are homework
problems I'm providing you, I also give you this solution. But don't go look until you
finish the problem, just to show you there's a
potential solution there. But here's problem number
3, here's problem number 3. I'm going to use
Control-Page-Down. You can see that there's a
bunch of homework problems all the way to problem number 9. All right, that was an amazing
first video for this class. We'll see you next video.
