- Introducing Functions 4:24
- The IF Function 4:26
- The COUNT Function 1:02
- The EDATE Function 2:18
- Review Formulas and Basic Functions 7 questions
- The SUMIF Function 2:41
- The CHOOSE Function 2:37
- The ROUNDDOWN & ROUNDUP Functions 1:52
- Keyboard Shortcuts 8:06
- Review More Spreadsheet Functions 6 questions
Preview
Video Player
00:00
00:00
Introducing Functions
4:24Functions are built-in calculations supported by most spreadsheet software. They let you calculate averages, sums, round up (or down) numbers, and even calculate values based on IF a certain condition is true. They're powerful spreadsheet features.
[MUSIC]
0:00
In this stage, we'll be covering some
common functions and keyboard shortcuts.
0:05
Many of the videos in the stage are short
and covers specific functions quickly.
0:09
We did that so you can easily come
back and reference them if you like.
0:14
I also want to emphasize that we do
not cover all the different uses for
0:17
these functions.
0:22
As you get more comfortable
with spreadsheets,
0:23
I'm sure you'll find additional
creative uses for these functions.
0:25
The functions you use most regularly will
depend a lot on the job role you're in
0:30
as well as your particular industry.
0:34
For example, if you're in finance or
investing, you probably use financial
0:36
functions like IRR to calculate
the internal rate of return, or
0:40
NPV, to calculate the net
present value of an investment.
0:45
We won't cover specialized
functions like those, but
0:49
just keep in mind,
there are many, many functions.
0:52
Some of which may be just right for
your job.
0:56
Let's get started.
0:58
Earlier on in this course, we used
the sum, average and median functions.
1:00
Let's review the sum function to
highlight the key parts of using
1:04
any spreadsheet function.
1:07
I start by typing in
the equal sign in a cell.
1:09
Think of the equal sign as saying
the value inside the cell is
1:13
equal to something.
1:17
That something in this case
is the result of a function.
1:18
To select a function,
1:21
you just start typing the first
letters of the function's name.
1:22
I want to use the sum function, so
1:25
I enter SUM and parentheticals.
1:30
You can see right below the cell a box
pops up that shows a bunch of other
1:34
functions that are similar to sum.
1:38
It also explains what
the sum function does.
1:41
So if I write IF, the list
1:44
shrinks a lot because a bunch of different
functions that have sum disappeared.
1:48
Going back to SUM though, when I add
parenthesis, you can see the function
1:54
syntax shows up and then descriptions
of what value is supposed to be.
1:59
Sometimes you won't see this appear and
2:05
that's because you've
deselected this functionality.
2:08
Instead you'll just see
a blue question mark there.
2:13
So to turn formula help back on, just
click this question mark, and there it is.
2:17
You can turn it on by clicking
that question mark, and
2:23
you can hide this formula
help by clicking this x or
2:26
using Shift+F1 keyboard shortcuts.
2:31
So I want to calculate the sum
of values in other cells.
2:34
We can do this in many different ways,
but let's start with two common methods,
2:38
selecting individual cells and
selecting a range of cells.
2:42
First, I can individually select each
cell like so, choosing the cell,
2:46
then putting a comma,
then the next cell, and so forth.
2:52
This is a bit tedious since I need to
manually enter every cell I want to sum.
3:00
Or, I can select
the entire range of cells.
3:04
Notice what happens here.
3:11
You see the first cell,
then a colon, then the last cell.
3:13
That can go across multiple columns and
rows.
3:19
So I can type the next row number here,
and
3:22
you can see the function
is now summing both rows.
3:26
Most good spreadsheet
software will use colors and
3:29
highlighting to show you what you have
entered into a function or formula.
3:32
In this case, we see E3:H4 and
3:35
those are orange, and
then what happens if I select these?
3:40
So you can see these are purple and
it's purple in your formula bar as well.
3:45
So if you want to select individual cells,
use a comma between each cell reference,
3:49
but a colon lets you
select within a range.
3:54
All the cells from the first cell
reference to the second cell reference.
3:57
In this case,
I am manually entering that range.
4:01
So E3: and I want it to select
everything all the way out to H3.
4:04
Did you see that where I had no row number
it was selecting everything in the column,
4:13
but if I had the three
there it confines it.
4:19