Afrikaans
Akan
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
so for this video
you had some homework
right remember
I asked you to copy and paste the summary equations
the summary functions that we used
for daily average low temperature
copy and paste those over to the right
into the daily high temperature column column d
so I'm gonna do that right now
I'm gonna do this one at a time at first
and then I'll do a whole bunch of them all at once
so control C to copy
moving over to the right
control V to paste
and something different clearly happens here
right so in fact
let's just pause right here
and as I asked you to do
I suggested that you click up here
so that we can get that color coded information
on what the arguments are for the sum function
what information is in that sum function
and we see that instead
of maintaining the reference to cells C2 through C13
as soon as we move that information over to the right
the cell referencing information
moved over to the right by the same amount
and what it did is it kept the same
number of rows
the same vertical sequence essentially right
and so it took that rectangle
and when we shifted the function over to the right
Excel shifted everything over to the right
and if we do that with these other functions as well
and we can do the all three at once
just using copy and paste
we see the same thing happened every single time
every single time is
updated to that new range of data so
this is one of those things that Excel does to help you
but whenever
remember I've said this before
whenever Excel does a shortcut to help you
it's making an Assumption
Excel makes an Assumption that if you move a function
if you move an equation over to the right
or if you move it anywhere
that you want to change what cells
that equation or function is referring to
what this example shows is
it shows the principle of relative cell references
and this is the default
cell referencing system that Excel uses
what we mean by relative cell references is in this
some function
for example
it looks like we're asking Excel to some cells
C2 through C13
and in fact
that's what we've told Excel to do
but what Excel is actually thinking is
it's going to sum 12 cells that are four cells above
the cell where the function is 1
2 3 4 and then it's going to sum all 12 of those cells
over here that works
one two three
four and then it's going to sum all of those cells
relative cell referencing is what this is called
and again it's a fantastic shortcut
we can do it again
what if we want to know the average of um
the average precipitation that we get in my area
every months
right across a year
average precipitation per month
really quickly
we just copy and paste that one cell
it brings over the equation with it
it brings the function with it
and it updates to that new location
it has the same relative location
this is thinking sort of spatially
it has the same spatial distance
to where we entered in that equation
this is fantastic
it saves a lot of time
it doesn't always work
so for example
what if we actually want to move the value 89.96
repeating if I copy and paste that
I'll just go over one more cell there
now we're going to get an error
because what's happening is
now Excel is looking at an empty column
to try to do a calculation
when really we don't want it to do that calculation
really all we want is to try to move that number over
and that's where things
can get a little bit complicated
is if you want to do what Excel thinks you want to do
then it's going to work beautifully
this relative cell referencing system
but for other types of operations
other things we might wanna do
we're gonna need to make some changes
and so what we're going to explore next
now that we understand this concept of relative
cell referencing
you're going to explore how we can move values around
without that equation updating
how we can just move that value by itself
the number the answer by itself
or how we can move that equation and function
and have it still refer back to where it
was referring original
so we can break that relative cell referencing system
with something called absolute cell referencing
so that's what we're going to tackle next
and this is really going to help you
master spreadsheets
because both of these tools
relative cell referencing and absolute cell referencing
are going to come in very handy
especially when you're using equations and functions
so we just learns that cell references in Excel
are always relative cell references right
they're always spatial references
and so just like I have highlighted here as a reminder
we're looking at the cells in this range above
where our equation is
every time we move that
that range of cells is gonna move with it
how do we change things to be absolute cell references
and what I mean by absolute cell references is
for example
this average equation
where we're averaging the
daily low temperatures for every month across the year
how can we make sure that we're always
absolutely always referring to that set of cells
and it's not gonna change
to make our cell references absolute cell references
we add dollar signs in between each column and row
in the arguments of the equation
and so right up here
I can do it manually
I'll show you another way to do this
we can just
add in dollar signs before each column and row
and now magically
this has become an absolute cell reference
because of those dollar signs
I can now move this equation
move this function around wherever I want to
and it's always going to point back
to these original cells
so we'll test this right now
you see over here on the right
I've created a new location for these summaries
so this will be a good way to test this
so if I copy and paste
remember when I copy the cell
it brings the underlying equation
the underlying formula with it
if I copy that over here
oh look at that
click up here
and it's referring to those cells
those original cells
and we get the same answer
we get 48.7
just like we had before
remember what it would be doing if it was still using
relative cell references dude
actually be looking way up off the top of the sheet
right would be looking up for cells
and then for a range of 12 cells up above that
but we fix that now
let's do this
also with mean daily high temperature for months
so we'll come over here
I'll show you a shortcut for doing this
we can just highlight that equation in the formula bar
instead of typing in the dollar signs
we'll just hit F4
and F4 automatically
converts that relative cell reference
to absolute cell reference
now this is not gonna change
we can do the same things
we can copy
we can paste that to the daily high
and it's going to update just like that
so this is a very important thing to understand
is you start working in more advanced tasks in Excel
I'm working with other people's spreadsheets
where they're probably referring to different cells
and you need to be able to know
if you're dealing with an absolute or a relative
cell reference very
very important in more advanced applications in Excel
I'm gonna show you a couple other ways
that we can solve the same problem though
let's now look at this
average precipitation per month
88 points 9 6 ish millimeters per month
around here
and so what we wanna do now
is we wanna look at different ways
that we can move this value over
so one way we can do is
we can take advantage of something that's called
paste special
so I just copied
and I'm going to right click
and you may have noticed
that we have these paste options
these paste special options
and one of these options is
we can just paste the values
so let's do that
click on that
and what happens here
is now only the value has been moved over
so if we click
we see that there's no longer an equation
there's no formula underlying that
there's only the value
so if we only wanna move the value
the result of an equation
we can do that
paste special
paste values
there's a drawback to that
so let's say we
let's say we delete June
from our data set
you can see temperature updates
but over here
on the right
precipitation does not update
it changed here
because we lost the value for June
it does not update over here
because we only pasted that number
so this isn't necessarily the best option
I'm going to undo those
I'm hitting control Z to undo those deletions I did
pasting those values by themselves
is not always the best option
because now that value will never update
maybe that's how we wanna do it sometimes
but we need to be careful
and we need to make sure that's really what we wanna do
so I'm gonna show you one other option here
we can do another copy
and we're gonna do another paste special
what we can do is we can paste a link
and what we've just done is
we now are linking to this cell
we don't have the equation in there anymore
we're saying it is equal to cell E20
so cell M4 is always going to equal E20
and we know it's always going to equal E20
because we're using an absolute cell reference there
because we seal those dollar size
as I mentioned
understanding this distinction between relative
and absolute cell references is very
very important
as you start working in more advanced spreadsheets
and especially when
you start working with other people's spreadsheets
and you need to figure out how those cells
are referring to each other
and what people are doing in their equations
functions and formulas
we'll have some applications of this very soon
but now you know the difference between
relative and absolute cell references in Excel
most recently
we've been working with functions
and getting a more advanced
understanding of cell references in Excel
and right now
we're gonna take a little bit of a break
from working on formulas and functions
and we're gonna start looking at some different ways
to manipulate and reorganize
and pull meaning from our spreadsheets
don't worry
we're going to be coming back to functions
and learning more advanced functions
that we can use in formulas but these
these sets of tools that I'm gonna be talking about now
sorting and filtering
are some of the most powerful tools that we have
and they're just a few things that
you need to make sure you think about and master
before you start engaging in sorting and filtering
to make sure you're doing what you think you are
and to maintain the integrity
of the data you have in your spreadsheet
for sorting and filtering
we're gonna start with our weather data
and then we're gonna move on to some larger
more advanced data sets
again it's useful to have a small data set
where we can see everything that's going on
and a data set where we're familiar with it
so we know when something is going wrong
now the sort function in Excel
as I've said
is one of the most useful things in Excel
sometimes we want to be able to identify the largest
smallest value
the earliest or latest value
organize things alphabetically
or any other way
and we can do all of that through sorting
there are few key things to keep in mind
and that's the point of this video
on some of the exercises you're going to be doing
now we're on the home menu
we're on the home tab
home tab and we see our ribbon
of all our different commands here
if we look way on the right here
we have a sort and filter button
I think it's more useful to go into the data tab
where we have more details
more complete sort and filter functions here
so that's what we're going to be working with
our first job
is that we're going to sort our weather data
by the daily high temperature
and basically
we're going to put this in order
where we're looking at
which months have
the lowest daily high temperature on average
and which months have the highest daily
temperature on average
so we wanna sort our data set
so that temperature is ascending
so it's climbing
we're gonna do that by sorting on
by clicking on the sort button here now
this is one of the most important things to think about
if we select our column of interest
as I have our daily high temperature
and then we choose to sort it
Excel immediately gives us a warning here
it's suggesting we expand
the selection from this one column
because it's suggesting that we probably wanna sort
all of our days together
and not just this one column
and this is absolutely correct
this is a place where we do wanna
listen to Excel's advice
think about what will happen if we simply sort
temperature
we only sort temperature
then those temperatures
are no longer going to be associated with
the correct month
they're not going to be associated with
the correct precipitation
and basically
we've messed up our data set
and so this is a really critical thing to think about
even when we wanna sort
buy one column
we want to sort our entire data set usually
so we're gonna take Excel's advice
and we're going to expand the selection
once we do that
now Excel is going to ask us what we want to sort by
we're going to sort by
daily high temperature
we're gonna use the cell values
the numbers in the cells
as our sort criteria
and we're gonna go from smallest to largest
we're going to increase
temperature as we go down in the spreadsheet
it's gonna be an ascending sort
the data has headers
Excel recognize this and so yes
we are not sorting
we're not including that first row of data
that's fantastic
because that would be confusing also
so let's go ahead and click okay
and see what we have here
we know sort
did something
because we see our index values are now out of order
this is where having an index column
can be really useful
it tells us when our data are in some order
other than the original order that we had them in
we could tell that by looking at once as well
but it's not quite as intuitive as looking
at those numbers
and we do indeed
see that our
month with the lowest daily high temperature
around here is January
about 51 degrees Fahrenheit
and our highest is August
I'm sorry it's July
with about 89 degrees Fahrenheit
so this worked very effectively
and every other data point
sorted along with that
so our data
still has integrity
we can say that this first row
is all associated with January
last row is all associated with July
and that's really critical
when we're thinking about sorting
now often when we sort
this is a temporary way of manipulating our data
we wanna make sure we can
get it back to the original format
we want everything to get back into order
there are several ways we can do this
if sorting was the last thing we did
then we can just
use our undo
keyboard shortcut
control Z to really quickly
put everything back into the proper order
so that's one option
is using Control Z
just to undo that sort
but that won't work
if you sorted your data
a while ago
and did a whole bunch of things in between
so I've just re sorted it
so we can look at some other ways
what are some ways
you can think of
where we can
resort these data back into the original order
I can think of at least two ways
now we're seeing the value of that index column right
if we want us
put this data set back into the original order
we can just resort
but we're going to do it by index this time
we will reorganize those from smallest to largest
and boom fantastic
we now have our data back in the original order
this is part of the reason I like to have an index
or an ID column in almost any data set I'm working with
with it can really help you solve issues
like the one we just solved here
we'll do one more thing
and this is a slightly more advanced
option within the sort menu
so we know that
our data were originally in the order of months
and so let's see if we can sort order based on month
now the first option we're given here is to sort a to Z
that's not really gonna give us what we want
we don't want our months sorted alphabetically
maybe we would at some point
but that's not what we want
we want them back in calendar year order
so can we do that
the answer is yes
if we choose the drop down on the order menu
we can go here to custom list
and this is a little bit hard to find
you wouldn't necessarily know this is in here
and if we look at this custom list
we can create our own list and put any order we want
or as you see
there are some standardized lists
days of the week
months so let's go with this list of full months
that's the order we want
things in the order will be January
February March and so on
and we click okay
and voila our order
our data goes back into the original order
so it's another way of unsorting
and going back to that original data format
again with Excel
it's always useful when you're manipulating
when you're changing your data
making sure you have some way to
get it back to the way it originally was
that's a really important data management practice
and even when we're sorting
we need to be thinking about that
gonna give you some exercises
so you can practice this yourself now
so at this point
a great thing for us to be doing
is actually combining some of our skills together
so that we can really integrate what we're learning
that's gonna lead to much more
advanced use of Excel in the future
is thinking about how we can integrate these tools
and use them all together
so this is why I asked you to create this temp diff
column where we're calculating
how variable the temperatures are in each month
I asked you to write a formula to do this
and I started you off here by reminding you
that we would always start with an equal sign
there are number of ways we can do this
but I think maybe the easiest way
is to just click on our individual cells
and say that we wanna take the difference
between the high temperature and the low temperature
and that's all it is
it's just a simple arithmetic equation
that we can write using the minus sign
so we don't always have to use functions
we can use math equations
we can use plus
minus divide
and multiply
and a lot of the time
that's going to give us something that we want
we see that that temperature
that variability in the month of January
is 21 degrees Fahrenheit
that's not a whole lot
and remember
what we can do is there are multiple
different ways that we can copy this value down
but if we do that
Excel is going to assume
because these are relative cell references
Excel is going to assume that we're always want
going to want to be looking immediately to the left
and then subtracting the value
that's two cells to the left
and so if we just paste that in right here
then we can see that we get the same value
and every time we click on a cell
we're always looking in column D
minus column C
but as we go down
the row number is updating automatically perfect
and this is interesting right
because we actually see that
where I live
that temperature variability is
about the same every single month
it's right around the low twenties
as low temperatures go up
high temperatures also go up
where did we see this before
we saw this when we made a scatter plot
so we're seeing how a lot of these different ways
of looking at our data
are starting to come together
so let's go ahead
and we'll sort our data set now
based on this new value that we just created
and I'm going to say okay
we're gonna use temptive cell values and no
we're not gonna do it based on month order
we're gonna go smallest to largest
our least variable month is the month of August
our most variable month
in terms of temperature
is the month of April
and again if we wanted to undo this
we could use undo
we could sort custom on the months column
or we can just sort numerically
based on our index column
all of these would get us what we wanted
so I hope you were successful in
calculating that difference
in doing these sorting functions and soon
we'll be moving on with a close companion of sorting
which is filtering
so we just did some really great work with sorting
and now we're gonna move on to a skill
that I consider a close companion of sorting
sorting and that's filtering
so let's quickly compare and contrast
what's going on with these two things
with sorting
we're changing the order of our data
but we're keeping it all there
we're just looking at it in a different order
and maybe that's because we want it alphabetical
or an increasing order
or month order
something like that
with filtering
we're actually only looking at some of our data
removing some of our data from view
that we're not interested in
so that we can focus on
on data that we are interested in
so these are slightly related
but slightly different ways of organizing
and drawing different kinds of meaning from our data
I say that these are closely related
and it looks like Excel agrees with me
because we see that filter is right next to sort
in the data menu here
and so it's going to be easy to find
let's do some practice filtering to see how it works
and to see what we can get out of filtering
now notice them already clicked within our data
that's important
because now that my cursor
I have a highlighted cell within our data
I can just click on this filter button
and what that's going to do is
it's going to add in
pull down menus to each of our columns
this is really interesting
we didn't get a pop up window
we got something else
we got these pull downs
so let's see what we can get here
let's assume that we want a filter based on month
and we only wanna look at the winter months around here
where I am and so
winter months around here
I'm just gonna say
we consider December
January and February to be winter
and the problem with the data right now is
those months are separated
we have January and February together
December is different
it's down at the bottom
because it's in order of months
and so maybe
we wanna make sure we can look at all those together
so we'll filter out everything
other than that
we'll just click on this little pull down menu
and you immediately see that
we also have our sort options here
and so this is one way that you could do sorting
we won't do it now
because we've just been practicing with it
but if you wanna apply the filter
to your entire data set
and then use these pull down menus to sort
you could do that
so this is really great
it's a comprehensive data reorganization window
that we have here
what you see
is that everything that's shown in our data set
has a checkbox next to it
so as you recall
we only now wanna look at winter months
we only wanna look at December
January and February
think the easiest way to do this is to unselect
everything now
we're not gonna see anything in our data
but we'll go ahead and reselect December
February and January those three months
now I'm gonna click okay and we'll see what happens
this is a little scary
the first time it happens
it looks like you just lost all your data
but what it's doing is
it's hiding data that you said you're not interested in
and only showing you the data
that you said you are interested in
how do we know our other data is still there
well we could undo this and get it back
and that's really easy
but we can also see
when we look at our row numbers
it's telling us we're missing a bunch of rows
it's only showing us here
highlighted in blue
row 2 row 3
row thirteen
it's telling us we have other rows of data in there
they just happen to be him so
that should make us feel a little bit better about it
we now have our information
just for our winter months
how can we get all of our information back
you see that
there's a little filter icon here in this pull down tab
that's telling us that we're filtering on month
so we would go back into month
to clear that out and restore data set
you can see
we also have this option of clearing out that filter
up here on the data menu
and we can do that in either place
and as soon as we clear that out
we get all of our data back
like nothing happened
and so hopefully
that's reassuring
that we're not actually changing or losing data
we're just changing what Excel is showing us
temporarily if we want
what are the kinds of filtering
can we do in a data set like this
well we've got a lot of numerical data
and we just did a filter based on text and says
let's take a look at how we can filter based on numbers
we're dealing with temperature
and let's suppose that we want to have an outdoor event
maybe a party or a wedding
and we wanna make sure the weather is neither too hot
nor too cold to have this outdoor event
which months might be good for this
I'm gonna arbitrarily define not too hot
not too cold is somewhere between 60 degrees Fahrenheit
and 80 degrees Fahrenheit during the day
that sounds like really good temperatures to me
so how can we filter our data
so we can only see high temperatures between 60 and 80
let's go into our pull down menu here
and figure this out
now if we approach this
the way we approached our text filtering
we'll see that all we can do is
we can just select or deselect individual
specific temperatures
and so this is the same thing as filtering by months
really we're just gonna eliminate those specific rows
and so there's no point
in doing this manually
that's not gonna help us
especially if we have a really big data set
and remember
most of these tools are gonna become most useful
when we have large data sets
so what else can we do
we can go into this number filters menu
a sub menu and now we have all these different features
equals does not equal
greater than less than
and here is one that's really gonna be helpful between
as I just said
we want to find days that are warmer
or 60 degrees
and
cooler or 80 degrees
so we want values between 60 and 80
including those numbers we'll just say
and now if we click that
fantastic our data set gets smaller
that's exactly what we wanted to see
we wanna see that sub set of months
that meet the criteria that I set
for the kind of party that I wanna have outside
and so now we know
we can schedule these events pretty confidently
in March April
May October
or November
so that's nice to know we've got these options
and as usual
we can just really quickly clear this out
and get all of our data back
so filtering as you see
is a really
really great way to draw new meaning from our data
to simplify our data
and to answer specific questions
this is very similar to using some more
advanced functions in Excel
and after you do some practice with filtering
we're gonna move on to those more advanced functions
where we can answer similar questions
before we move on from filtering
let's look at another application of filtering
and you may remember
that when I was showing you how to plot
and first introducing the idea of charts and graphs
and looking at a bar or column chart
I was actually
in real time
adjusting what I was showing you
this is one potential application of filtering
that can be really
really useful
now what I was actually doing
when I was doing that demonstration is
I was manually changing which values I was showing you
just by dragging the selected data
and the chart updates in real time based on that
and so that's a neat tool
that anytime you want
you're not sure which data
you should be showing in your chart
you can just change your selection
and the chart will update in real time
and that's really helpful
but another thing we can do
is we can apply a filter
we can apply a filter to the data
that are used in our chart
and if we do that
then the chart will update in a similar fashion
so if I go back to what we just did in filtering
and I say okay
actually we only wanna look at those winter months
here we have those winter months filtered down
to just those three
everything else has been removed
and our chart has updated
just as we expected it would
now we would wanna change some things about this
it's really squat and wide
and so we would make some modifications
but this is a great way to really easily assess
the effects of including different data sets
and data points in your chart
and looking at how you can change it
to show different things
so again this is an important integration
of a few of the different skills we had
you can use filtering
when you're making a chart
to show different things
and learn different things about your data
we're just working with filtering
filtering is one way of reducing our data
and only looking at our data
if it meets certain conditions
there's another way we can do this
we can have conditional functions
and conditional functions will do the same thing
except conditional functions
can sometimes be more powerful
because they can apply
some kind of mathematical function to our data
that only meet certain conditions
just as we're using filtering to find winter months
or find months with temperatures within a certain range
we can do the same thing
using these conditional functions
I'm gonna show you these
because they're a very powerful tool
for understanding what's going on with our data
as I mentioned early on
with functions
there are hundreds of functions in Excel
and there are even several conditional functions
we're going to use count
if we're going to count
how many data points match the criteria
we're setting
we could also do some if
we could add data points to each other
if they meet conditions
average if we could take averages
but only for some of our data
this is a very similar idea to filtering
it just adds in this additional step
of adding some math to it
if we'd like to do these
additional mathematical functions
in this case as I said
we're going to be focusing on count if
and I'm going to show you
how these conditional functions work
as you know
when you're putting a function into its cell
we immediately start getting clues
on what kind of information it needs for its arguments
for count if
it wants to know what range it's looking in
and because we're saying right now
in this case
we're going to be looking for good weather months
which I'm defining as months with a low temperature
greater than 40 degrees
so we'll select
the range of data associated with low temperature
now how am I going to define this good weather month
I have it right there next to it
we want low temperatures to be greater than 40 degrees
we put the condition
we put the criteria in quotes
and now as soon as I hit enter
Excel's going to do this count for us
and it's going to say okay
there are seven months that meet your condition
of having low temperatures greater than 40 degrees
remember we could be doing this ourselves
this is a small data set
and that's the point
you can check this visually
if you want to confirm that Excel is correct
but these functions become very powerful
when you don't wanna be counting at yourself
when you're looking at rose
with hundreds of data points
that's what we're going to be doing soon
now another condition that I'm going to say
is having good weather months
I don't like really
really hot weather
I like it warm
so I'm also going to say
we only want once
where the high temperature
and so now we're selecting
this high temperature range
is going to be
less than 80 degrees Fahrenheit
and there are eight of those months
so this is one quick example
leading you into understanding
how these conditional functions work
we just counted up months
that meet our condition of having lows greater than 40
we counted up months
leading our condition of having highs less than 80
but what we haven't done yet is
we haven't figured out if any months meet both criteria
that is what's going to come next
after you have a little opportunity
to practice using some of these conditional functions
so we've been doing a great job
working with these conditional functions so far
have a good understanding of count if
presumably you've gotten to feel
for the fact that you could use some if
and average if also
and what we looked at is
we looked at how we're setting conditions
for this count function right
but we still have more work we wanna do
we actually wanna look at
two conditions at the same time
we know in my area
that we have seven months
or low temperatures are greater than 40
and we have eight months
or high temperatures are less than eighty
but how many months fit both of these criteria
both of these conditions
we can't do this with count if
this means we need to work
with another set of conditional functions
these are the IFS functions
there's an s on these
and so I'll just type this in here
I will type it in properly
and as we see here
count IFS will count
cells specified by a given set of conditions
so not just one
so we can use this to tackle our problem here
and this is a really neat set of functions
I actually use these quite a bit
when I'm summarizing data
as an alternative to filtering
which is another way of doing this
as usual we wanna set our range for our first criterion
and again that's low temperatures
so I select that column
and as we know
we want those temperatures
to be greater than 40 degrees
but now we're looking at our second condition
that we're setting
still within the same function
but it's a separate condition
so we need to be looking in the separate column
because now we're talking about high temperatures
we want those high temperatures to be less than eighty
count ifs 4
check both of these boxes at the same time
January is the low
greater than 40
is the highest
than 80 yes or no
and it'll go down the line
and so let's see what we get here
three months total
across this data set
that meet both of those criteria
so now we can be confident
that we have three months across the year
where lows are greater than 40
and less than 80
we could have done this manually
but even just checking
two different things at the same time
can be a real challenge
so these count IPS functions
can be very
very useful
they're great alternative to filtering
or something
that you can use in combination with filtering
and remember
something I've been encouraging you to do more and more
now that we're getting more advanced material
is be thinking of ways
that you can integrate
these different skills
and use them together
and that's when you're going to become
a very powerful Excel user
we're almost finished with Week 3
soon I'm gonna wrap up
and recap what we've done this week
and then you'll move on to an assignment
where you'll have the opportunity
to integrate
many of these skills
you've been working on
great job so far
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.