Transcripts
1. Intro: Hi everyone. I'm Jeremy, or as
some call me Big dog. Welcome to my
Skillshare course on Microsoft Excel
formulas for beginners. Before we jump into the content, let me take a second
to introduce myself. I've been in the
working world for almost seven years and
all that time has been spent in Excel for my time in college to
my current role today, Excel has been a part
of my daily toolkit. I've developed a passion for analytics and interests
in figuring out the most efficient solutions
and a love for automation. My goal for this video series
is to make you an expert on the basic formulas that
you need to succeed in Excel. This course was designed to
be short so that you can learn formulas quickly
without any additional fluff. At the end of this course, you will have a solid basis
in Excel fundamentals, and you'll be well on
your way to mastering it. Without further ado, let's jump into the course
and get learning.
2. SUM() Function: Welcome to the video
on the SUM function. In this video, we'll
be going through some high-level bullet points on what the sum function is. And then we will transition into some real-world examples
to get you rolling. Let's get into it. Let's start with a
basic definition. The sum function adds values. The values can be
individual values, cell references or ranges. Individual values would be
numbers like 1.25 or 2.50. Cell references
would be cells that contain numbers like C5 or D5. And ranges would be
groups of cells that contain numbers
like C8 through E8. Let's take a look at an example to solidify our understanding. Just like in the summary slide, we have three different
examples for each of the different values that can be entered into the sum function. For the first value
type individual values, we start typing the sum
formula and enter two numbers, 1.25 for number 1.502
for number two, Excel adds the numbers
together and boom, we're done. In the next value
type cell references. We enter the values from the previous section
into individual cells. In C5 will enter 1.25. In D5 will enter 2.50. For the sum function, instead of typing in numbers, we will use the cell reference where the numbers are contained, C5 for 1.25 and D5 for 2.50. Lastly, in the range value type, we will follow our
previous example, C8 for 1.2542.50 and E eight for an
additional number, 1.25. The sum function here, we'll take the first
number in the range, colon, the last
number in the range. The sum function will add all the numbers together
within the range C8 through E8 or C8. And sweet, Let's jump
into the next formula.
3. AVERAGE() Function: Welcome to the video on
the average function. Just like in the video
on the SUM function, Let's take a look at
some high-level bullets and then we can jump
into some examples. On we go, Let's start
with a basic definition. The average function returns the average or arithmetic
mean of the arguments. Just like in the sum function, the average function
accepts individual values, cell references, or ranges. Let's jump right
into the example. As in the summary slide, we have three different
examples for each of the different values that can be entered into the
average function. For the first value
type individual values, we start typing the
average formula and enter two numbers, 1.25 for number 1.502
for number two, Excel averages the numbers
together and boom, we're done. The next value type
cell references, we enter the values from the previous section
into individual cells. In C5 will enter 1.25 and
in D5 will enter 2.50. For the average function. Instead of typing in numbers, we will use the cell reference where the numbers are contained, S5 for 1.25 and d5 for 2.50. Lastly, in the range value type, we will follow our
previous example, C8 for 1.25, D eight for 2.50, and E eight for an
additional number, 1.25. The average function here, we'll take the first
number in the range, colon, the last
number in the range. The average function
will average all numbers within the
range C8 through E8, or C. D and E eight
were on fire. Let's keep rolling
into the next formula.
4. COUNT() Function: Welcome to the video
on the count function. Just like in the video on the
sum and average functions. Let's take a look at
some high-level bullets and then move on
to the examples. Here we go. Let's start with a
basic definition. The count function counts the number of cells that contain numbers and counts numbers
within the list of arguments. Again, just like in the
sum and average functions, the count function accepts
individual values, cell references, or ranges. Onenote, the count formula only count cells that
contain numbers. If you'd like to count
cells that contain other data types like text, you would need to use count. A. Great, Let's move
on to the example, as in the summary slide, we have three different
examples for each of the different values that can be entered into the
count function. For the first value
type individual values, we start typing the count
formula and enter two numbers, 1.25 for number 1.502, for number two, Excel counts the numbers
and boom, we're done. The next value type
cell references, we enter the values from the previous section
into individual cells. In C5 will enter 1.25 and
in D5 will enter 2.50. For the count function, instead of typing in numbers, we will just use
the cell reference where the numbers are contained. C5 for 1.25 and d5 for 2.50. Lastly, in the range value type, we will follow our
previous example, C8 for 1.25, DH for 2.50, and EH, for an
additional number, 1.25. The count function here, we'll take the first
number in the range, colon, the last
number in the range. The count function. We'll count all numbers
within the range C8 through E8 or C8. And Easy-peasy, Let's move
on to the next formula.
5. MIN() & MAX() Functions: Welcome to the video on
the Min and max functions, just like the previous videos, Let's take a look at
some high-level bullets and pop right into the examples. Let's do it. Let's start with some
basic definitions. The Min function returns the smallest number
in a set of values, and the max function returns the largest number
in a set of values, as we're used to. And just like in
previous functions, the Min and max functions
except individual values, cell references or ranges. Let's take a look
at the example. As in the summary slide, we have three different
examples for each of the different values
that can be entered into the Min or max functions. For the first value
type individual values, we start typing the Min or max formulas and enter two numbers, 1.25 for number 1.502. For number two, excel
determines the minimum or maximum of the numbers
and outputs the results. The next value type
cell references, we enter the values from the previous section
into individual cells. In C5 will enter 1.25 and
in D5 will enter 2.50. For the Min and max functions. Instead of typing in numbers, we will use the cell reference where the numbers are contained, C5 for 1.25 and d5 for 2.50. Lastly, in the range value type, we will follow our
previous example, C8 for 1.25, D8 for 2.50, and EH, for an
additional number 1, the Min and max functions
here we'll take the first number in
the range colon, the last number in the range. The min and max functions
will determine the Min or max of the range C8 through E8, or C8 and E8 onto
the next formula.
6. CONCAT(), LEN(), & REPLACE() Functions: Welcome to the last
video in the series. In this video, we're
going to go through some more common formulas
that don't need as much time as the other
formulas we've learned, but are still essential
to understand. We're going to take the
rapid fire approach here. This video will cover
the concat function, the Len function, and
the Replace function. Let's jump into some
definitions and then we'll take a look at the
examples. Lego. Let's start with
some definitions. The concat function
combines the texts from multiple ranges and or strings, but it doesn't provide delimiter or ignore empty arguments. Next, the Len function returns the number of
characters in a text string. Lastly, the Replace function
replaces part of a texturing based on the number
of characters you specify with a
different texturing. Let's hop into the examples
to understand this more. In the first example, we have the CONCAT formula. As we looked at in the
previous examples, we have individual strings, cell references that
contain strings, ranges that contain strings. Let's look at how
each example works. The first example has
the CONCAT formula, taking in strings directly
with a no cell references. The second example uses D1, D2, and D3 as cell references, but returns the same output
as our individual example. The third example uses
brains D1 through D3, and again gives us the same result as the
individual example. Onto the Len function. As with the CONCAT formula, we have three examples,
strings, hello, goodbye, and how the lens
formula is simply gives us the number of characters
in each of the strings. Hello has five characters, goodbye has seven characters. And how did he also
has five characters? Easy enough. Onward. For the last
function replace, we have the same three strings. Hello, goodbye. And how the replace
formula starts by asking us what the old
texts cell or string is. This is basically asking us what our starting cell or string is. Next, it wants us to define the position within the string
that it should replace. It wants both the
starting character and the number of characters pass the starting character
that needs to be replaced. Finally, the formula
needs to know what we will be replacing
the character's width. Let's look at the examples. The first example, hello, starting at position one and
going through position two, we're going to replace the
H and E with a dollar sign. We get dollar sign, LLC. Next for goodbyes, starting at position one and ending
at position three, we are going to replace
GO with a dollar sign. Lastly, for Howdy, starting at position one and ending
at position four, we're going to replace HOW
D with a dollar sign suite. We got exactly what we intended. Since this is the last
video in the course, I want to thank you
all for joining. It's been fun working
through these examples. Feel free to ask questions in the discussion board
if you need help. Peace out.