All language subtitles for 3Topic 1-2ou1 Formulas and Functions in Excel Week 3 Manipulating

so as we're now thinking about

manipulating and summarizing our data

a great place to start is with a concept that we're

almost certainly familiar with

which is the concept of an average or mean

so an average is basically

an expected value for some data right

and we all calculate averages really frequently

sometimes we calculate averages when we're learning in

in school and we wanna know our grade

or if we're teachers and we're calculating grades

if we wanna calculate an average cost for something

a lot of us know how to do this

we can do this by hand

pen and paper

with a calculator

but the whole point of this class

and the whole point of spreadsheet programs like Excel

is it can do it for us automatically

and save us a bunch of time

we're still working with relatively small data sets

like our weather data set

but as our data sets get bigger and bigger and bigger

we need to rely on more and more

on these time saving tools that we can use Excel

so I'm gonna show you

how to do these kinds of calculations

these kinds of summary

and manipulation calculations in Excel

through a few different steps here

and you'll see we're starting to integrate

and bring in a lot of the other knowledge

that we've worked on so far

now you've probably calculated an average before

and if so you know that the average of a value

is the sum of all the values

divided by the number of observations that we have

so if we're doing daily

low temperature

and average of that value

we know that we need to take the sum first

and we need to add all these values up

now we know

using Excel's equation or formula tools

that we can put an equal sign here

and we can ask Excel to add all these numbers up for us

right we can just use our plus signs

and we can click on every single cell here

and we get these cool cell referencing color codes

right and let's pause here to remember

our cell referencing system is working for us

we're going to add the value

and C to to C3

to C4 and so on

and so on and so on

color coded

we have our equation up here as well

this is going to ensure an error free calculation

but the problem is

you can see that this is already very tedious

and so we're already going to skip ahead

from using these manual equations

to using something in Excel called functions

so we'll start over here

a function is a built in tool in Excel

that will do some mathematical work for us here

I'm gonna use the summer function

if I use the sum function

I need to give this function some information

that information is held in parentheses

what do I want to sum

I want to sum all of these values

just click and drag all of those cells

and you see

we have our cell reference notation for a range here

we wanna sum up the values from cell C2

all the way to C13

I'll close out the parentheses and hit enter

and we get that sum right there

as we've seen before

we can always click on the formula bar

and get that color coded information

to double check and make sure that we're summing up

the full sets of numbers there

so here's our first function

the sum function

and it's already saved us a lot of time

because we don't have to keep

adding all these cells together

even though it's only 12 values

speaking of 12 values

we know if we're going to calculate the average

we need to divide this sum

by that total number of values

we know it's twelve

but we can and should

also use a tool to help us with that

because maybe in the future

we're going to add more values to the spreadsheet

it's not always going to be 12

so we might want to count

use the count function to tally up all those for us

so that we're always updated with how many

observations we have in our data set

then finally

now that we have this information

we know that we can divide the sum

using the slash sign for division

by the counts to come up with our average value

so what we see here is we see that average value

our average low temperature for a month

is 48.7 degrees Fahrenheit in the area where I live

across the entire year

but you'll notice that I still used this mathematical

arithmetic way of writing this equation

but just like we have a sum function

and we have a count function

we also have an average function

and so just by typing in average and then again

putting in the correct information in the parentheses

we can skip all of these steps

and we can get that same exact

exact value

that average value of 48.7 degrees

so each one of these little things I was showing you

each one of these functions

the sum function

the count function

the average function

is saving us additional time and a diff

additional effort

and really importantly

it's saving us the opportunity to be making mistakes

these are called functions

we're gonna be using a lot of them in Excel

and you see that

I was referring to cell referencing again

and so we're starting to see that

useful information that we've acquired

that knowledge that we've acquired

about cell referencing

coming back into play

we're gonna use a lot more of it in a minute

when we explore more

about how to use functions in Excel