Welcome to Highline Excel
2016 class video number 6. If you want to download this
Excel file, Business 218 video 6 start file or the
PDF files, click on the link below the video. Wow. We got all sorts of amazing
topics in this video. Here's the PDFs-- 14
pages of amazing notes. Here's our Excel Workbook. And here are our topics. We're actually going to talk
about conditional calculations with Excel formulas. Now, we did a little bit
of conditional calculations with Excel formulas
back in video 2, and we did a lot of
conditional calculations with pivot tables
in video number 3. But here, we've got to
talk about formulas. We're going to talk
about an AND logical test using AND criteria, the OR
logical test using OR criteria. And there's a bunch
of different ways to do both types
of logical tests. Most the time when we're doing
data analysis or formulas with calculation, we're using
either the and logical tester or logical test. So we're going to see a
bunch of different ways and a bunch of different
methods for making these types of calculations with formulas. Then we're going to talk about
credit analysis for accounts receivable. We'll talk about the
if and is functions, and even compare and contrast
pivot tables and formulas. Now, I want to jump
over to our PDF notes, and in the table
contents, I'm going to click on And Logical Test. Now, the goal of
an and logical test is to run two or
more logical tests and see if all logical
tests evaluate to true. You can think of it like this. If you take out the garbage
and clean the table, you get dessert. It's only if you get two
truths-- took out the garbage and cleaned the table--
that you get dessert. An and logical test
might look like this. Only one of these will deliver
from the logical test a true. It's only when we get two trues. Now, I want to
jump over to Excel, and we want to go
to the sheet And. In our firm example of
an and logical test, we're going to have
1, 2, 3 and criteria. That means we're going
to be asking a question of each record in the data set. Only when we get
1, 2, 3 truths are we allowed to count or include
the number in our calculation. Now, the question is, for
this set of AND criteria, is the region cell and
is the sales rep GG, and is the product DAB. If I'm doing this
manually, that means I have to look through
the Region column. I have to ask, is
this equal to south? False. Is this equal to south? True. I have to continue
for the entire column. Then I switch over to the next
criteria on the next column. Is this equal to GG? True. Is this equal to GG? True. Continue all the way down. Then I jump over
and get my product. Is this equal to DAB? False. Is this equal to DAB? False. All the way down. It's only when I get
1, 2, 3 truths that I get to use that record
in any calculations. Now, first, before we
start doing adding, counting, averages and
other calculations, let's look at the AND function. If I type equals
AND, Tab, notice logical 1, logical 2 up to
255 logical tests for us we have to do. And its only purpose is to
deliver a true or false. We can put as many
logical tests. Only when they all come out
true will AND deliver a true. So we're going to build our
logical test as a new column to indicate that this
record should be included. I'm going to ask
the question, hey is the item for this
record in region? Are you equal to south? And I'm going to lock
that with the F4 key. Now look at this screen tip. I type comma, and now I
get my next logical test. I'm asking of this record
in the Sales Rep column, are you equal to, down
here, GGR criteria and F4. Now we have two
logical tests, comma, and our third one is
hey, is the product for this record equal to DAB. And I'm going to hit the F4 key. Now we have our
three logical tests. Close parentheses. Control Enter better
deliver false, because I have
false, true, false. When I double click
and send it down, sure enough, I get trues
only when 1, 2, 3 conditions have been met. Now, when would you want
to use the AND function? Well, sometimes people
use the AND function in a helper column like this to
help them with complex criteria when they're filtering
or building formulas. Other times, we use AND
and the OR functions inside the IF function. And we'll see a great example
of that for our accounts receivable problem later. Now we want to go talk
about sum ifs, count ifs, and average ifs. These are built-in
functions that add, count, and calculate
the average based on one or more conditions. And guess what? The default for these
functions is and criteria. So if we enter multiple
criteria ranges and criteria, all these functions
run an AND logical test to then either add, count,
or calculate an average. Now, before we make
our calculations, I actually would like to use
defined names in our formulas instead of always having
to come over and highlight these columns. So we're going to use the Create
Names from Selection trick. I've highlighted
the field names. Control Shift down arrow. I want to highlight all
the way down to the bottom. We could go up to
formulas, Define Names, Create From Selection,
but there's a keyboard for this-- Control Shift F3. Now, we have to be careful when
we create names from selection. Top row, I definitely
want to use the word at the top of each
column, the first row as the name for each column,
but I do not want left column. If I use left column, it
would name each row this date, and we don't want that. So I click OK. Now, instantly
from our name box, we can see there is customer. There's region, and so on. Now let's come over, and
our first calculation is total cost of goods sold. Since I'm adding with
multiple conditions, I use the SUM IFS function. Now, I can, in the sum range,
highlight using a keyboard, Control Shift Down
Arrow Control Backspace, and it automatically
puts our define name in. Comma. We have 1, 2, 3
different columns we have to put into criteria range. So the first column is region. I'm going to type it. Region Down Arrow Tab. I instantly see
the range finder. Rainbow color coding shows
me that's the right column. Comma. And the criteria,
well, there it is. It's south. We're not copying this anywhere,
so we don't need to lock it. Comma. Criteria range 2-- so
here's our second column. Sales rep-- so I'm going
to type S Down Arrow, and we can see our gold
dog tags mean define name. f of x means function. I hit Tab, Comma. There's GG for the Criteria 2,
Comma, Criteria Range-- that's our third column. Product-- I see the
Gold Dog Tag tab. Comma, and there is DAB. So there we go. We've entered three
criteria-- one, two, three, and three criteria
ranges-- one, two, three. Any time you add more than one
criteria range and criteria, you are doing an AND logical
test-- in our case, to add. So I hit Enter, and
boom, there it is. There is our total-- $10,314. 114 Now we want to calculate
our average cost of goods sold next, because the
average IFS function is very similar to the SUM IFS. The only difference is
instead of saying sum range like it did up here
for the numbers, our argument says average range. When you see average,
remember, that's the calculation we're
making-- average. When you see average range,
it means please give me all of the numbers. We want our cost of goods sold
column, so I type cost of goods sold or find it from
my dropdown tab. Now we do the same three
criteria ranges and criteria. Now, these arguments do
not have to be entered in any particular order. It does not matter
in which order we put them in, because
all that matters is that there are three truths. I'm actually going to do
the product one first. So Product, Tab,
Comma, and then I got my DAB product as
the criteria, Comma, criteria range 2. That's going to
be our sales rep. Comma, and I'm going to go
up and get my criteria, which is GG, Comma, criteria range 3. That's going to be region. Tab, and now I go up and get
my condition, which is south. So very similar,
except for using AVERAGE IFS instead of SUM IFS. When I Control Enter,
there's our average-- $3,064. Now, COUNT IFS is
one argument easier than AVERAGE IFS and SUM IFS. If I want to count with
these three conditions, there's no average or sum range. It's just criteria range
1, criteria, criteria range 2, and so on. Region, criteria range 1, Comma,
and there is the region, south. Comma criteria range
2 sales rep, Comma, and there's the sales rep GG. Comma, and then Product. That's criteria range 3, Comma,
and there is our product DAB. So simply put three criteria
ranges, three criteria, and you're good to go. And there it is-- the count. There were exactly 36
transactions over in this data set where the region was
south, sales rep was GG, and product was DAB. Now, in all three cases, we
were doing single cell formulas. Sometimes, we have a
cross-tabulated report we need to create with formulas. So cross tab, as we
talked about when we were talking
about pivot tables-- it's as if there's a cross
here, that intersecting cell. Right there, it has the
column header criteria Sales Rep Sheila, and it has
the row header Criteria Region West, and for every single
calculation inside the table, it's the product DAB. Now we're going to
use SUM IFS Tab. And what are we adding
for the sum range? Cost of goods sold,
Comma, Criteria Range 1. It doesn't matter which
range we put in first, so I'm going to put in Region. There's criteria range 1, comma. Criteria-- notice this
is the row header. As we copy the formula
across our columns, we need it locked on J.
But when we copy down, we need to move to J 21 22. So I hit F4 one,
two, three times, lock the column but not the row. Comma, Criteria Range 2--
this would be Sales Rep. Comma, and our criteria
is GG right here. As we copy the K19 down, it
needs to be locked on K19. But as we copy the
whole formula over from the GG column
to Sheila and Mo, we want the k to
move to l and then m. So I hit F4 one,
two times to lock the row but not the column. Comma Criteria Range
3-- that's product. And in this case, Comma Criteria
3, that's going to be DAB, and I need a locked
in all directions. Close parentheses. And so there are SUM IFs
for a cross-tabulated table, cross-tabulated table with one,
two, and three AND criteria. Now one advantage of
some elements and shadow it's an average US
compared to the functions that we'll learn
a little bit later is that these functions
can be copied easily down an oval we're allowed to
have block ranges mixed cell references whatever when
we get to our d functions although they do more
calculations then just adding carrying and averaging
it's difficult to copy those formulas. But not for SUM IF. So I can Control Enter,
double click and send it down, copy it over, go to the
diagonally furthest one away, hit F2, and admire
that handiwork. The cell references
and the defined names are all looking in the
exact right position. So AND criteria for
SUM IFS, COUNT IFS, and AVERAGE IFS, no problem. Now we want to talk about
a different type of AND logical test. We want to talk about
between logical tests. And this is just a form
of an AND logical test. And all it means is we're
going to have a lower limit and an upper limit. And we need to either count
how many of the transactions fell between January
1, 2013 and January 31, and then we need to add all
these sales between this lower and upper date. Now, the trick in
both of these formulas is going to be the comparative
operator and how we join it to the lower and upper limit. Now, we're going to use
COUNT IFS and SUM IFS. But when we have our formula,
we can't just click on this cell as the criteria,
because then it would try to find just this day. And if we did AND
criteria-- if I said please go find a date over
here that's the 1st of January and the last of January,
there is no such date. So really, what we want when we
have an upper and lower limit and we're counting
between is we need to say please find all
the dates greater than or equal to the
lower date and less than or equal to the upper date. Let's try this with COUNT
IFS, equals COUNT IFS. The criteria range-- it's
going to be the Date column. And you can see our gold
dog tag there, Date. Now, I'm going to have to
repeat that date twice, because there's two conditions. But for the first one, I'm
going to put in the lower limit. Now, we need the comparative
operator greater than or equal to. In double quotes you have to
put your compare operator-- in double quotes. Then you have to join it
with Shift 7, the ampersand, to the actual date. Now, what this means is-- notice
the greater than or equal to sign is pointing
towards the column. So it means it's
going to be looking at all of the dates over
there, and any time it finds one that is greater than
or equal to January 1, 2013, it will get a true. Now we have to do
the upper limit, and we have to repeat for
criteria range 2, Date. Now we do Comma. Comparative operators
are in double quote. And this is the upper
limit, so every day, it has to be less than or
equal to, in double quotes, and then join it
to the upper limit. Now, notice this whole
comparative operator and date says I'm going to look at
that column over there, and any time I
find one less than or equal to the end of
January, I will get a true. It's only when we
get one, two truths that the date will be counted. Now I'm going to close
parentheses, Control Enter, double click, and send it down. I'm going to go to the last
cell and hit F2 two to verify. Sure enough, I got the upper
and lower dates correct and over in our date column. I want to look at
this 12/11/2013 notice for this last count we're
doing between the lower and upper limit. This is for December, so there's
December 1 and December 31. So if we look at this date right
here, I have to get to truths-- is it true that
12/11/2013 is greater than or equal to 12/1/2013? The answer is true. Second, is this date less than
or equal to December 31, 2013? True. So because I got to
trues, I'm allowed to count this transaction,
and later, we'll be able to use the numbers
in our calculations. So in this situation, different
than our first couple examples, our two questions are
about the same column. And in particular, they're about
the one date in this record. All right. So there we have counted between
the lower and upper limit. Now let's do adding total sales. I'm adding with AND criteria,
so I'm going to do SUM IFS. The sum range, I'm doing
Sales, Comma, Criteria Range 1 is Date. And now I have to
do the same thing. The lower data always gets--
hey, greater than or equal to in double quotes, and
I'm going to join it to the lower limit, comma,
then I'm going to do my date, Column for criteria range 2,
comma, and four criteria 2, in double quotes, less than
or equal to n, double quotes, and I'm going to join
it to the upper date. We have to find a
date that's greater than or equal to the
lower and less than or equal to the upper. Now we'll look through
those transactions. Close parentheses,
Control Enter, and there is the total
for January, 2013. Double click and send it down. Go to the last cell, F2. You can see how useful
this type of formula is, because it's often the
case in business we are adding sales or expenses or units for
a month or a quarter or a year. Now, in this example here, we
had our lower and upper limit. Sometimes, we do
not have that setup, so we want to look at a
solution to this problem here. Please count all the
transactions for the month, but we're only given
the lower limit, and we're not allowed
to add anything else to our spreadsheet. Well, we can do this. But first, let's consider
a new function that can find the end of the month. And if you guess the
name of this function-- it's called EOMONTH
for end of month. All it needs is a start
date, comma, and then you have to tell it how
many months in the past or in the future you
want to go to calculate the end of the month. Now, I want the current
month, so you put 0. That means from this
January, 2013 date, I do not want to move any
dates backwards or forwards. If I gave it minus 1,
it would give me the end of the month for last month. Now, that is a serial number. I'm going to right click,
point to my mini toolbar, click on Format Painter,
and then click on this date, increase the column width. That is minus 1. It's looking at January, but
it found the end of last month. If I give it a
plus 1, it's going to find the end of next month. Notice it knows that there's
only 28 days in February. F2, but what we want is 0. Control Enter, and that's
the end of the month. We can actually use
this Formula Element right inside our
formula, and we'll join it to less
than or equal to. Enter. Now I'm going to pretend
that this isn't here. I'll leave it here as
evidence that we did that, but we're going to pretend
we're not allowed to use that. So you ready? We're counting. So I'm counting with and
conditions or criteria, COUNT IFS. Date is our range, comma. We have a lower limit,
so we say greater than or equal to in double quotes
and join it to our lower limit. Now we do comma, date. That's our Criteria Range 2. And then we need, in double
quotes, less than or equal to, in double quotes, and
we have to join it to. And now we do end of month. Notice the end of the month
has no problem looking at the beginning of the month. Comma, 0 for the current end of
the month, close parentheses. Now, notice in criteria
range 2, there it is-- less than or
equal to, and then some function determining
automatically as we copy down the end of the month. Now I can close parentheses. Control, Enter, double
click, and send it down. Go to the last cell, F2. That is a useful
construction, because again, oftentimes in many
situations-- not just business-- you're counting
or adding or averaging for a certain time period. Notice if we were
doing quarters and I wanted to jump two months ahead
to get to the end of the month, I could put a 2 there. All right. Let's Escape. We want to talk about
one other situation where we have between criteria. Here's our question. Our question is, count
the sales amounts that were between the
upper and lower limit. So we want to count how many
transactions from our data set were between 0 and $1,000,
between $1,000 and $2,000 all the way to $10,000. Now, I already looked
through the data set, figured out what the minimum and
maximum were, and then created my upper and lower limit
so that when I count, I'm counting every single
sales from that Sales column. So you ready? But before we do this, we
want to notice something here that's going to cause us to
be careful when we create our comparative operators. If you have 0 and 1,000
for the first category and you choose to have 1,000 and
2,000 for the second category, the fact that we have
1,000 in both places means we have to choose. Either the lower limit
or the upper limit is going to get the equal sign. If we had greater than or
equal to the lower limit and less than or equal to
the upper and copied it down, we might double
count, because there would be an equal sign for
both this 1,000 and this one. So you ready? Equals COUNT IFS. Criteria range, I'm
doing Sales, because I want to look through
the Sales column, and count between a lower
and upper limit, comma. Criteria 1 in double
quotes greater than or equal to the lower limit. Now, I chose to put the
equal sign on the lower side. In other situations, you
might have the equals sign on the upper side. So we've joined the lower limit
to our comparative operator. Now I type a comma and then
Sales tab, Criteria Range 3. There we go. Comma, and then for Criteria
2, in double quotes, less than-- I am not putting an
equal sign-- end double quote. And I am joining it
to the upper limit. Now I can close parentheses,
Control Enter, double click and send it down. Go to the last cell in F2. Sure enough, it looks like
it's calculating correctly. Now let's look at this
formula right here. Notice the lower and upper
limit-- 8,000 to 9,000, but 9,000 not included. If I go over to
our data set, here is a single cell in
this Sales column, and we're asking two questions--
is this number greater than or equal to 8,000? The answer is true. Is it less than 9,000? The answer is true. This is an AND logical test. All the tests came out true. It's also a between
logical test. All of the tests for the
lower and upper limit both got true, so
that 8,000 would be counted in this category. Now, one last thing about
this formula right here. Notice we're counting between
a lower and upper limit. If you remember last
video, video number 5, we talked about the
frequency array function. That frequency array
function counts between a lower
and an upper limit, but we're forced into
categories that include the upper but not the lower. Now, that's fine, and that
solution actually is easier to create than this solution. We have to have the
lower and upper, and we have to remember where
the comparative operators go. But if you want control over
your comparative operators-- for example, you
want the equal sign on the lower side-- you can't
do that with the frequency function. But here, we can have any
type of comparative operators for our upper and lower
limit when we use COUNT IFS. All right. So that's three examples
of between criteria. Now we want to talk about
our next topic-- d functions. Now, d functions are going
to be special functions that only act on proper
data sets with field names. And you have to
set up the criteria with field names in the
first row and the criteria below each field name. Now, when to use d functions? You can only use d functions
when you have a proper data set, because the actual
function communicates with the proper data set, the
database, through field names, like Region, Sales Rep, Product. In general, you don't
want to use d functions if you need to copy,
because it's difficult and in some
situations completely impossible to copy a d function
formula to another cell. And the reason why is
because of this requirement that we have field names
and criteria below. Another situation you might
want to use d functions is when you have
complex criteria, like lots of conditions. You can imagine using
SUM IFS and COUNT IFS when you had to enter all
these different ranges. And the other situation
is you might not have a built-in function
like SUM IFS and COUNT IFS. So up here, there's 12
different d functions. Sometimes calculating
standard deviation or d max, that might be the
only way to do it. So let's see how to do this. Equals sign and a d. In our case, we want count. And there's two
database functions-- count, counts number, counts. Not empty cell, so I'm
going to select Count. Now, notice the screen
tip is pretty polite. It says hey, give
me the database, which is a proper data set. I'm going to use Control
Home to jump up to cell A1. I'm going to highlight just the
field names to Sales, Control Shift Down Arrow to
highlight all the way down to the bottom and
Control Backspace to jump back to the active cell. So that's our database, comma. Now, the field, we have to
tell d, count, or whichever d function we use
which field we want to make a calculation upon. Now, I'm going to count. And I chose count,
so I have to choose one of the number columns. I'm going to choose Sales. Now, there's a
couple ways we could communicate to the d
function which field it is. I can put in double
quotes Sales, because that's the
name of the field. I could have a cell reference,
which I do right here, with the name of the field. Or if you know the
relative position-- that means I went over and counted--
one, two, three, four, five, six, seven. Sales is the seventh
field in that database. Any one of those three
methods will work. I'm going to click
on Sales here. Now comma, Criteria. This is where we have to list
our field names in our criteria below. Now, we have the choice
between AND or OR criteria. AND criteria has to
be on the same row. So notice the way we would
read this is that the date has to be greater than or
equal to 10/1/2013, and it has to be less than
or equal to 12/31/2013, and the region has to be west,
and sales rep Gigi and product AIM. Not only that, but you have to
put the comparative operators right in the cell. So here we go. Field names with and
criteria all on the same row, close parentheses, and Enter. So there are seven transactions. Now I want to delete
two of these conditions here-- delete and show
you how we put that in. Now, normally, we'd put a date
like 12/31/2013 and hit Enter. Well, that's a number, right? But we're required to put
the comparative operator in. So I'm going to type it
right before the 12 less than or equal to and Enter. So that's how you can
enter date criteria. Now, watch what happens
if I type equals west. Well, we already know
what's going to happen. We've already run
into that problem multiple times in this class. That were is in a formula,
because equal sign is the first
character in the cell. It's not in double quotes. It's not the name of a function,
and it's not a defined name. So it will give us a name
error if I hit Enter. Here's the trick. When you're entering criteria
for d functions or later, Advanced Filter uses the
same criteria setup here. You put a lead apostrophe. Anytime you put a lead
apostrophe in a cell, it says whatever comes
after is considered text. And so when I enter, that's
how we can put the criteria for find exactly west. Now I want to do total
and average here. I'm going to cheat. Watch this. The actual arguments-- database,
name of the field we're calculating upon, and
the criteria range is going to be exactly the same. So you ready? Control C and Enter. Now we want a total,
so we type ds. And notice there's standard
deviation, standard deviation of a population and sum. I want sum. And since all these
arguments are the same, I Control V and Enter. And there is the total
given our five conditions. Equals d average, Tab, Control
V. There's the database. There's the field we're
going to try and calculate an average from. And there is our
criteria, and Enter. Now, all three of
these calculations could be done with COUNT IFS,
SUM IFS, and AVERAGE IFS. But here's one you cannot do. If you needed to calculate
standard deviation, there's not an equals
STDEV.SIF function. We wish there was one of those
in statistics, but there's not. So instead, we're going to
use equals d for database, s for standard deviation,
and there it is. Now, the one without the p means
sample, and the one with the p means population. Population is just all of them. Sample is a partial set
from the population. So Control V. This isn't
a statistics class, so we're not studying what
standard deviation means. But there you go. That's how we can calculate it
using our database functions. Now, notice this is
pretty complex criteria. This is hey, find any date
that's in the first quarter, greater than or
equal to the lower, less than or equal to the
upper, and then West, GG, and IAM product. Now, there is one
last topic we need to consider for AND
criteria, and it has to do with standard deviation. If we scroll down
here, what if we needed to do a
cross-tabulated table, calculate a standard
deviation with two conditions? Well, d functions, you just
cannot copy through a range like this given
the criteria setup. So in this case, we have to
create an array formula that can filter out the
values we don't need based on AND criteria. We can't just do the
standard deviation function and highlight all of
the sales numbers. We need to pick out
only the sales numbers for each one of the
intersecting cells that match the column header and
the row header criteria. Well, we saw last
video, video number 5, we did average gross
profit for sales rep, and we did an array formula
using the IF function to filter out values. So we can do the same here. Equals STDEV, we're
going to use the s1, because this is sample data. And just like we
did in video 5, we can't dump all of
the numbers in here, so we want to filter them. We can use the IF function. Now, here's the logical test. In the last video, we did one
array operation where we said, hey, particular column,
are you equal to something? But we have two columns
and two conditions here. It's no problem. The IF can do this. We have to put the first
column in, so that's region. And I'm going to ask the
question, are any of you equal to Midwest. Now I need to lock this. When I copy it to the side, I
need it locked on the J column. But when I copy it
down, it needs to move. The row needs to move
to 88 and then 89. So I hit the F4 key
one, two, three times. Now, if I were to
highlight this and hit F9, it's too big to evaluate. But the resultant
array just comes out with trues and falses. Now, remember, we
have two conditions, so watch what happens. I'm going to type a comma. It says please give
me the value of true. I cannot put the numbers in yet,
because I still have one other condition. If you still have
conditions, this is where you put your
second IF function. We are nesting
multiple IF functions, because we have multiple
conditions or criteria. Now, as soon as you
put an IF function inside of an IF function,
this is AND criteria. So the logical test-- we have
to ask the question of the Sales Rep column, are any of you
equal to the column header. And I need to lock this
going down across the row but not across the column. Now we can type comma. And because we have
no conditions left, now we can highlight
the whole column, Sales. Now, the way it's
going to work-- and I'll look at a
small example on one of the other sheets
in just a moment-- is any time it sees a true
here in the same position in the resultant
array as it finds a true over here, only
when there's true and true is the multiple IF
functions allowed to pick out the sales number. So I'm going to close
parentheses, close parentheses. Man, I have a lot
of functions here. I wait till I see the black one. Then I know I'm done. Inside of this number 1
will be a resultant array with only the sales numbers for
each one of these conditions. Now, this is an array. Formula we don't have
sum product function. We don't have array
constants, so we have to use the special key
stroke to enter this Control, Shift and Enter. I immediately look up
to the formula bar. I see my curly brackets. I can double click and
send it down and then copy it to the side. I go to the last cell,
diagonally furthest one away, F2. And sure enough, it got
all of the ranges right. I'm going to hit
Escape, because I don't want to get rid
of that Control, Shift, Enter, so Escape. Now I want to go look at a
much smaller version here, so we can step through what just
happened there in that formula. And I'm going to click
on this sheet, Boolean. Here is a much smaller data set. We have Sales Rep,
Customer, Sales, and we want to calculate
standard deviation based on Gigi and Amazon. We can see there's actually
only two records that have Gigi and Amazon. So here we go. Equals STDEV, and we're
going to do the s. So on an inside number
1, we put our first IF. And I ask the question,
hey, are any of you sales rep equal to Gigi, comma. We still have a
condition left, so we have to put our second IF. And our second question
is of the Customer column. Are any of you equal to Amazon? Now we do comma, value of true. We don't have anymore
AND criteria conditions, so I put the sales column in. Now I can close parentheses,
close parentheses, close parentheses until
I see my black one. Control, Shift, Enter. I look up to the Formula bar. I see my curly brackets. I know I've
calculated correctly. Now, F2, and I want to
do a little tick here to step through
and look at this. I'm going to copy this
formula in Edit mode. Control C, and I'm going
to come down below. F2 and Control V. Now I want to
evaluate certain parts of this. And I did it in the
cell below, because I'm going to leave the evaluations
hardcoded in here so we can see how this is working. Logical test 1, I'm
going to hit F9. Notice it gives me a resultant
array of trues and falses. Now I'm going to go to
the second logical test and the second IF. F9 to evaluate it. And I'm going to
go to value of true and evaluate the
numbers, too, so F9. Now I want to come to the
beginning of the formula and type a space. Now I have this
here as text, and I want to look at each one
of these resultant arrays. So there's 1 and 2. It's only when it finds
a true in the first one-- and I'm going to Control V--
and in the exact same position in the second one. When it finds a true
in the second one, that means true true. Then it's allowed to pick
out in the values if true. So I'm going to bold that. Notice when it gets
a true-- that's the second one-- and
a false, it is not allowed to pick out that number. False, not allowed to
pick out that number. It's not until the fifth true--
and I'm going to Control B-- and if you look in the
fifth position here, there is the fifth true. So the way AND
criteria works when you have multiple
ifs is it's only when it sees in the
exact same position in the resultant array's
true and true is it allowed to pick out, in
this case, the fifth number, Control B. So when you have some
aggregate calculation that doesn't have a
built-in IF function, you're totally allowed to
string IF functions together to do AND criteria. And it's only when it finds
a true in the first resultant array and a true in
the second resultant array in the same position is it
allowed to pick out the number. So those are the only two
numbers that then get dumped into standard
deviation .s function. Now, you could test this. If we just use the standard
deviation s and manually did this using a comma and getting
that number 2, we can verify. Of course, for
large data sets, you would never want
to do it this way. But there we go. We get the same exact thing. There is some number formatting
making it look different. So four AND criteria,
for an AND logical test, sometimes we have to
string together IFS. But of course, other times we
can use built-in d functions. We might be doing between
logical tests, which is a certain type of
AND logical tests. But most of the
time, we're going to be using SUM IFS,
COUNT IFS, or AVERAGE IFS. AND criteria-- now we want
to go over to the sheet Or to talk about OR criteria. Now I want to jump
over to our PDFs, and in the table
of contents, I'm going to click on OR Logical
Test and jump to that section. OR Logical Test using OR
criteria-- the goal of an OR logical test is to run
two or more logical tests and see if at least one
logical test evaluates to true. So if we had two tests, and
one of them comes out true and the other one false,
the OR logical test would say true to
this situation, because there's
at least one true. The OR logical test
would say true here also, because there's
at least one true. Only when we get
both falses will the OR logical test
report a false. Notice true true. That's going to be OK, because
that's at least one true. So in this first
situation, we got one true. Second situation, we got
one true, zero trues, and two trues. Now we want to jump
back over to Excel, and we're on the sheet Or. Now, here's our same data set
we used for our AND criteria calculations. Now we want to look at OR
logical tests and OR criteria. I'm going to do the same thing
I did on the other sheet. I'm going to highlight the
entire table, Control, Shift, Down Arrow, and I'm going to
name these columns with Control Shift F3. So remember, we only
want the top row. So I'm going to uncheck
left column and click OK. Now, something
different happened. Because notice, if this column
is Name, Date and over here, this column is Name Date,
there is potentially a problem. But let's go look
at our Name Manager. Formulas, Define Names, Name
Manager, or the keyboard Control, F3. And what Excel does
is, yeah, there's duplicate cost of goods
sold, duplicate customers. But over here in
the Scope column, it says the first name you
create at cost of goods sold, that is on the AND sheet. That's called the
workbook scope. That name-- if you type cost
of goods sold into any formula, it's available in the entire
workbook on any sheet. But when we create a second
cost of goods sold name, this one has a worksheet scope. It's only going to be
available on the OR sheet. We can see right here OR. Now, there is an exception. If you type the sheet
name, explanation point, then the define name
cost of goods sold, then you can access
this on any sheet. But it's rare that
people want to do that. Most of the time, you define
a universal workbook name and you use it everywhere. If you create a worksheet
name, in general, use it only on the sheet, which
is what we're going to do. I'm going to click Close. Now, we want to talk
about an OR logical test. Here is our customer
group, and we need to do things like add
up all the sales count, calculate the average, and
add a helper column that tells us true when the
customer in the Customer column is one of these customers. Now, the way and OR logical
test will work is like this. We will ask a question of
each customer in the Customer column. That means for
this record, we're asking the question,
hey, customer, are you Amazon, or are you
Microsoft, or are you Yahoo, or are you Google? Notice for any
particular one, we have to ask those
four questions. Now, in this particular
OR criteria situation, since our criteria is
operating on a single column, we're actually never going
to get more than one true. If we asked of Costco,
we'd get four falses. But when we get to
Microsoft, we're only going to get one true. The other ones will be false. Now, let's start
with the OR function. I'm going to click
on the top cell. And just like the AND
function, equals OR. There are logical tests
separated by commas. You can have up to 255. And for our first
logical test, I'm going to say hey,
relative cell reference. Customer for this record,
are you equal to-- and I'm going to
click on Amazon. F4 to lock it. Comma to get to logical 2. Same customer again. Are you equal to the
next customer, Microsoft? And I'm going to hit F4. Comma, logical 3-- hey,
customer for this record, are you equal to Yahoo? F4, comma, logical
4, same customer. Are you equal to Google? And F4. Now I can close
parentheses, Control, Enter, and double click
and send it down. There is our Helper column. All of these ones are false,
because none of those customers right there are in
this list over here. Now, there is another way to
do this kind of calculation that we'll learn
next chapter when we discuss lookup functions. But when we're talking
about OR criteria, that's the perfect
function to illustrate how we're calculating
with OR criteria doing our OR logical test. Now, if we want to
add or count, we learned last video,
video number 5, that we can use SUM
IFS and COUNT IFS with a function argument
array operation. So I'm going to use
the SUM IFS, and I want to add all the sales
for one of these customers, so sum range sales. And notice for my drop down, now
I have Workbook and Worksheet. Now, I'm not going to
select from this drop down list, because
watch what happens. It puts the workbook name,
and I don't want to do that. So I'm going to type out sale. Notice I get the
range finder blue. And that's what I want, so
I'm going to type a comma. Criteria Range, I want customer. Now you could also-- if
you're on this sheet, if I highlight the
Customer column, Control Shift, Down
Arrow, Control, Backspace, it will put that
color coding in. Now, comma, the criteria
1 argument-- this is where we do a function
argument array operation. This is OR criteria. Anytime you put
more than one item into criteria 1 for SUM IFS or
COUNT IFS, we're saying hey, is the customer Amazon or
Microsoft or Yahoo or Google. Now, because I put 4 in there
when I close parentheses and if I highlight
this and hit F9, we know that this delivers
one, two, three, four numbers. That's the total for Amazon,
Microsoft, Yahoo, and Google. That resultant array, we
don't want to leave that, so Control Z. We put
that not into sum, because if we use
the sum function, we'd have to use
Control, Shift, Enter. I want to put it
into sum product. That array 1 argument
there is what we use when we need to add
from an array calculation. So close parentheses,
and I simply hit Enter, and there is our total. If we want to count,
we can use COUNT IFS. Criteria range is
simply Customer, comma. And I'm going to put a
function argument array operation with my OR
criteria, close parentheses. If I highlight this and hit
F9, I gave it four conditions, so it spits out four counts. That's the count of transactions
for Amazon, the count of transactions for Microsoft. Control Z. I need
to add those counts to get the total count, so
I put it in sum product, close parentheses, and Enter. So there are the total sales
and count using OR criteria on a single column. Now, when we get to calculating
average, we can't do that. Because if we do a
function argument array operation for average, it'll
spit out four averages, and we can't average those. When you're doing
an average, you need to add up every one of
the actual numbers for each one of the either Amazon, Microsoft,
Yahoo, or Google and then count and divide by the count. So we can't use a function
argument array operation here. Not only that, but
when we come down here and we're going to see how
to do an OR logical test and ask a question of
the Sales Rep column and the Customer
column-- notice that means we'd be asking a question
one, two separate columns. You cannot do a function
argument array operation here, either. So for both of these
situations, we're just going to use the
regular old AVERAGE function and put the IF inside of
it and an array operation. Now I want to go over
to the sheet Boolean, so I'm going to
click on that sheet. Now, here's a small data set. And I want to ask
this OR question. Hey, customer, are you
Amazon or are you Google? Now, what I need to do is
get one, two, three items, and from the Sales column,
only get these three values and put them into
the AVERAGE function. Now, earlier in this video,
we saw standard deviation with two IF functions. In the last video, we did
aggregate calculations with the IF function. But here, we want
to use the average. And in the number 1, we
want to filter those values. But guess what? We actually do have two
questions using OR criteria, but we can't put an IF
and then a second IF. When you string together
or nest IF functions like we do for our standard
deviation calculation, that's using and criteria. No problem. We can use IF. And then in the logical
test, we can actually put both questions--
both array operations-- right in this logical test. That means I'm going
to put this column two times into the logical
test and compare it to each one of these. First, I have to isolate
each array operation since there's going to
be two with parentheses. So I highlight the
Customer column and I say, hey, are any
of u you equal to Amazon. Now I close parentheses. And the math operator
we use for OR logical test or OR criteria
is the plus symbol. It's addition. And then we put--
notice we're still in the logical test argument. We put open parentheses
in our second column. It's the same column,
but we list it twice. But this time, we're asking
are any of you equal to Google. Now I close parentheses. And in the logical
test, what will happen is we're never
going to get a true and a true in the same position
of the two resultant arrays. So we're only going
to get situations-- and you can see down here-- if
I were to evaluate all of this, you can see I can get true. This is the first
array right here. I get true for Amazon,
and then the fourth true is for the fourth Amazon. But when I add it, the
second resultant array is asking the
question about Google. So you can see over here it's
only in the second position. So in the second array
of trues and falses, there's only a true there. Now when I add, I'm
going to get true plus false, which is 1,
false plus true, which is 1. And then in the third position,
look-- a false and a false, so I get a 0. That's the trick to
doing OR logical tasks inside an aggregate
function inside of IF. Now, here's our screen tip. Let's just go ahead
and click on this and evaluate it with the F9 key. Sure enough, 1s and 0s. Now, the IF function
logical test has no problem. If any non-zero number
is interpreted as true, 0 is the only number that
is interpreted as false. Control Z. Now I
come to the end. Comma and the value
of true-- those are the numbers that
I want to filter. Now I can leave the
last argument off. If I leave it off, then
falses are inserted into that resultant array. Close parentheses. Now in the number
1, there's our IF. And when I hit F9, you've
got to be kidding me. It totally filtered and give
us only the values that match our OR criteria logical test. Now I need to Control Z, come
to the end, close parentheses. I see my black parentheses,
and this definitely is an array formula. There's our array operation. None of the functions
are sum product and I don't see array
constant, so we have to Control, Shift, and Enter. When we look up into the Formula
bar, we can verify sure enough, our curly brackets got put in. Now, we could verify
this on a small data set to prove to ourselves
that this is working average. And I can simply manually click
on the first one, type a comma, click on the second one, type a
comma, click on the third one, close parentheses, and Enter. You would never want to
do this on a big data set. Now, that's OR criteria
on the same column where we needed to do the array
formula because AVERAGE IFS wouldn't calculate a
correct average for us. Hey, here's a second
situation where the same formula will work. I'm asking two questions
of two different columns. I'm saying hey, is there a
sales rep Gigi in the Sales Rep column, or is there a customer
Amazon in the Customer column? Now, we can just do our average. And right hand side
number 1, we'll use the IF function to filter
out the values we don't want. The IF, and we'll do the
same exact construction. In parentheses, I ask the
first column are any of you equal to Gigi. Close parentheses, and I do
the math operator plus symbol for OR criteria,
open parenthesis. I highlight the second
column and ask the question, are any of you equal to Amazon. Close parentheses. Now, the difference
between this situation is that we have two columns. And you can clearly see that if
I'm running to array operations here, in the first position,
I'm going to get a true for Gigi and a true for Amazon. So I get two trues. If I were to evaluate the
logical test argument here-- and if I click on it
and hit F9, sure enough, there's my at least one. I got two trues for the
first item in each array, one because I got a Gigi
but not Amazon, a zero because I did not get
Gigi or Amazon, and so on. And remember, the
logical test argument-- and, for that matter, AND
functions, OR functions-- the logical functions in Excel
interpret any nonzero number as true, and 0 is
the only number that gets interpreted as false. Control Z. Now I
have my logical test comma and the value of true. I simply highlight the values. I leave false out, because
I want false inserted to help filter out the values. I'm going to click on number 1. There it is. Hit the F9 key. And sure enough,
there is our array of numbers matching only the
numbers for Sales Rep Gigi or Customer Amazon. The false filtered out the
two values we did not need. Control Z. Come to the
end, close parentheses. There's no sum product. There's no array constant. So we'll hold Control
Shift and Enter. I look up to the Formula bar. I see my curly brackets. I'm good to go. Now let's go back
over to the sheet Or. So when we're calculating
average sales for the customer group here, four
different customers, I'm just going to use the
AVERAGE function and then the IF. Now, this is going to be wild,
because in this logical test, I have to put four array
calculations separated by a plus symbol. Each time, I'm asking is anyone
in the Customers column-- are you equal to-- and in
this case, it is Amazon. Now I'm going to do
a little trick here. I'm going to close parentheses. And plus, I do not
want to type this out, so I'm going to click on
logical test, Control C, and then very carefully
click right after the plus and control V, V, V.
I have four of them. Now I double click
the K and say hey, that one's got to be Microsoft. Double click the K 9. That one's got to be Yahoo. Double click the K 9. That's got to be Google. Let me get rid of
that plus at the end. Wow. That is wild. I can't evaluate
this, because it's too big for a cell with a formula. But there it is. Comma, value of true. Now I need sales. And I want to leave
that value of false, so I close parentheses. That whole number 1
right there will give me a filtered column with sales
numbers that match our OR conditions or criteria. Close parentheses and
Control Shift and Enter. We immediately look
up the formula bar, and there are our curly brackets
verifying that we entered it as an array formula. So there it is-- 5,109. That's the average sales
for this customer group. Now, whereas this array formula
doing an OR logical test only looked at a single column,
the Customer column, we are allowed to ask an OR
logical test question of two different columns. We're going to ask
the question, hey, how many transactions had sales
rep Gigi and customer Amazon? Now we use a very similar
construction equals average. We don't want the
whole sales column. We want to filter it. So we put the IF function. In the logical test, we need to
use that plus symbol for our OR logical test. Open parentheses,
and the first column is going to be Sales Rep. And I'm going to ask the
question, hey, are any of you equal to Gigi? Close parentheses. There's our plus. Open parentheses. And now I need Customer. Are any of you equal to Amazon? Close parentheses. We have our two
questions, which will evaluate to true and false. The plus will add them,
and the resultant array will be 0 for false-- 1 because
we found only one of them or 2 because we found both of them. Logical test argument
will interpret 1 and 2 as true and 0 as false. Come to the end. Close parentheses
and value of true. That's the entire Sales column. Now, value of false--
we leave that out. When we do in close
parentheses, a false will be put in when the
conditions are not met. And now, average
number 1 argument has its filtered list of
values to match the criteria. Close parentheses. This is an array formula. We have to use Control,
Shift, and Enter. We immediately look
up to the Formula bar. We see our curly brackets. We're good to go. And there it is--
the average sales for sales rep Gigi
or customer Amazon was 4,958 and
90-some odd pennies. All right. Now let's go look
at our last example for OR condition or criteria. d functions-- we saw d functions
for AND logical tests and AND criteria. But here, we want
to see OR criteria. Now, actually, if we
have the field name and the same
customer group here, our formula is going
to be a lot easier. If we go back and look
up here, these formulas are going to be a lot
harder than the d functions. Now, if it's true that you have
a report that says customer group 1 and you need that
there-- not the field name-- then you're going to have
to do these formulas. And if you're taking these
formulas and copying them, then you're probably going to
be stuck with these formulas. But if you have a
single cell calculation you want to make for total
sales, average sales, counting, standard deviation, or
whatever, d functions are the ticket when it
comes to OR criteria. Now, here we want
total sales, so I'm goring to say equals d sum. Now we need our database. Control Home, and I'm going to
highlight the proper data set with field names, Control,
Shift, Down Arrow, Control, Backspace. There's our database, comma. The field-- I need
to tell the d sum function which of
those columns I'm going to make a
calculation upon. I'm going to put the name of the
field in double quotes sales. You could also put the
relative position, 7, comma. And here's where OR
criteria is great. That criteria needs
the field name. And you simply put OR
criteria on different rows. And boom, when I close
parentheses and enter, that is a lot easier than that
sum if sum product formula. Now, F2-- I'm actually going to
cheat for our next calculation, average. I'm going to copy the database,
the field, and the criteria area. Control C, because the only
thing we have to change is equals d average. Tab and Control V. D AVERAGE
will calculate the average for this customer group. That is a lot easier than
our average array formula we had to do. And I hit Enter. Boom. There it is-- 5,109. Now, counting-- I'm going to
count on the Customer column, so I'm going to use DCOUNTA. There's the same database. The field I'm going to
count on customer, comma, and the criteria field
name and OR criteria each on a different row, close
parentheses, and enter. There's our 688. Now, this OR logical
test had criteria looking in a single column. We are allowed to use OR
criteria on multiple columns. But for D functions,
you have to be sure. If you're saying please find
Gigi Sales Rep or Amazon customer, you have to be sure
and put it on different rows. But you have to have
an empty cell that says yes, you're looking
for Gigi sales rep, but anything else is OK. Or you're looking for Amazon,
and the empty cell here means anything in
the Sales Rep column is OK as long as it's
a customer Amazon. So we have the
criteria area set up. Equals DCOUNTA. We have our same database. Comma, it doesn't
matter which field, because they both have text. COUNTA will count text. Comma, and the criteria area. The two different columns
in the OR criteria, including those empty
spaces, on different rows, close parentheses, and
there is our count. So OR criteria for
OR logical tests, we can definitely do
it with D functions. And if it's a single
cell calculation, you're not copying
it, and you have field names in a proper data
set, that is the way to go. We can definitely do some array
calculations for OR averaging and our SUM PRODUCT, SUM IFS,
and SUM PRODUCT COUNT IFS for counting and adding. Now we want to go over to the
sheet And and Or Function. I'm going to click
on this sheet, because we've got to talk about
accounts receivable department when we have to analyze various
customers' credit worthiness. Now, we're going to look
at three different rules, and here they are. Each one has a different
logic, and so it will help us to see different
ways we can use AND and OR functions. Rule number 1-- we
will extend credit if the customer sales
last year are greater than our hurdle of 25,000
and they're asset value was greater than the hurdle of
300,000 and the credit rating 1 is greater than or equal to 3. Rule number 2-- at least one of
the credit ratings is exceeded. So we look at each credit
rating, and if one of them exceeds our limits-- exceed
means bigger than 3, bigger than 7-- then we extend credit. And finally, rule 3
is the most complex. We have to check for
sales greater than 25,000 and asset value
greater than 300,000 and late payments less
than 2 and at least one of the credit
hurdles is exceeded. All right. Let's start with number 1. We're going to start right here. Well, three conditions, all
have to be met-- perfect use for the AND function. AND logical test-- I'm asking
the question for this customer relative cell reference. Are the sales last year
greater than 250,000? And I'm going to lock
that with the F4 key. That's logical 1. Comma, logical 2-- hey,
relative cell reference for the customer's asset
value, is that greater than our hurdle of 300,000? Now we have to lock
that with the F4 key. Finally, we have to look at
credit rating 1 greater than or equal to our hurdle of 3. F4 on that. Now we have our three logical
tests, close parentheses, Control Enter, double
click, and send it down. Only when all three-- yes,
greater than 250,000, yes, greater than 300,000, and yes,
greater than or equal to 3. Now, that's rule number 1. Rule number 2-- this is at
least one a the credit ratings. Well, we're used to
that phrase by now. When we say at
least, that means or. So I'm going to say or. Logical test 1-- I'm looking at
credit rating 1 relative cell reference. And this says exceeds. I have to use greater than,
click on the hurdle, F4. And there's a second test. Comma, relative cell
reference for credit rating 2, are you greater than
our a hurdle of 7? F4, close parentheses,
Control, Enter, double click and send it down. Well, clearly, false means
they both came out false. So this is not bigger than 3
and 7 is not bigger than 7. But this one, we got two truths. Here we got a false
and here we got a true, so our formula is working. In each case, I made
a grave error there. I need to come down to
the bottom and verify, yes indeed, the
relative cell references are all in the correct place
and our lock cell references on the hurdles are
in the correct place. I'm going to come over here, F2. Sure enough, one, two
relative cell references, and the hurdles are locked. Now, rule number 3-- we have
one, two, three and four AND logical tests. Now, this last one, at least
one of the credit ratings is exceeded-- that means that
in the fourth logical test for AND, we're actually going
to have to use this whole thing. Remember, the purpose
of OR and AND functions is to deliver a
single true or false. So this is perfectly
all right to put inside one of the logical
test arguments in AND. I'm actually going to copy
this in Edit mode and Escape. We'll paste it when we
get to that last argument. So you ready? Equals AND, Tab. Logical test-- I need to
check if relative cell reference, the customer's
last year sales, exceed or are greater
than our hurdle of. $250,000 Now let
me hit the F4 key. Comma, logical 2. I have to check asset value
relative cell reference. Are greater than our hurdle? F4 to lock it, comma. Logical test 3, I have
to look at late payments last year for the customer. That's a relative
cell reference. That has to be less
than our hurdle of 2. F4, find the comma. Logical test 4-- I'm simply
going to Control V. Remember, OR is simply delivering
a true or false, and that's what each one of
these logical arguments need. All right. I'm going to come to
the end very carefully. Close parentheses. There are four logical
tests inside that AND. Control Enter, double
click, and send it down. Now we come down to the
last cell and hit F2. And look at that. That is amazing. And we could verify. So here's a true. That one exceeds. That one exceeds. This one is less than the 2,
and at least one of these credit ratings has been passed. So that AND function
got four trues, and then and deliver a true to the cell. Now, here's rule 3, and that's
the rule we're going to use. And we would actually like--
instead of true and false, we don't want to
see Boolean values. We want to see
credit or no credit. Any time we have a column with
a bunch of cells in each cell, I want to put one of
two things, that's the perfect job for
the IF function. Now, with a logical
test, guess what? I already have the
rule right here. Comma, the value if true. In double quotes, I'm going to
type credit, end double quotes. Comma, and the value if
false, in double quotes, no credit, end double quotes,
close parentheses, Control, Enter, double click
and send it down. Go to the last cell, hit F2, and
sure enough, that is working. Now, if this was our goal here
and we didn't need any of this, we can simply notice that
that cell is looking in I 11. So if we go look
at what's in I 11, it's perfectly all right
for us to now copy this-- and this is big little
AND OR construction-- copy it, Control C,
Escape, come over here, F2, double click that
cell reference, and Control V. Notice I
didn't get the equal sign. That whole AND OR
logical construction that defines our rule number
3 for extending credit is now sitting in
the logical test. Control, Enter, double
click and send it down. Now I can highlight all of this. And if I remove it,
this is working fine. Now I'm going to click
Escape, Control Z. In fact, if you ever have to build
a formula like this, you do want to build
it in little pieces and then mash it all
together, and then finally, mash it
all together here so we have one big formula. Enter. So AND and OR
conditional calculations are great for this type of
accounts receivable credit analysis. Now we want to go look at our
next topic on the If Is sheet. I'm going to click on this. And we want to talk
about IS functions. Now, IS functions
deliver a true or false depending on what is
actually in the cell. The IS text, as you can imagine,
delivers a true when it sees text. If it's not text, like if it's
a number, it returns a false. IS NUMBER-- it sees a
number, it delivers true. Anything that's not a
number delivers a false. IS LOGICAL, only when
it sees a logical value. A Boolean true or false--
does it deliver true? Otherwise, it delivers false. IS BLANK-- that function should
have been called is empty. Because what it does is
if it sees an empty cell, it delivers a true. Anything else, it
delivers a false. Now, there's IS ERROR,
ISERR and IS NA. IS ERROR will deliver a
true for any error it sees. ISERR delivers an error
for any value except NA. Sometimes it's important to
find other errors besides NA, because NA means not available. There is NA, eight
which as you can imagine delivers a true
when it sees an NA. Anything else is false. IS NON TEXT, and
even IS A FORMULA. Notice it says true, so we
know there's a formula there. Now let's look at
a bank example. We have three examples that will
combine IF and IS BLANK and IS NUMBER and IS TEXT. Here's the checkbook. We have date, the check
number or transaction number, a description, and what
we're adding and subtracting from our starting balance. Now, what we would
like is I would like a formula that
calculates the balance, and I want to be
able to copy it down. If there's nothing entered for
this particular transaction, I want nothing to
show up in the cell. But as soon as I
enter something, I want the formula to calculate. Now we're going to start
with just the balance part of our formula. I'm going to say equals
relative cell reference. I'm always looking
at the balance above. And I'm going to add
from the Addition column and subtract from the
Subtraction column. Those are relative
cell references that will work all the way down. And notice, normally we're
having either a subtraction or an addition, but
this balance formula will work even if we
had numbers in bold. Control, Enter, and I'm
going to copy it down. Now we can see it works
for the transactions where we've already
entered data. But down here, I don't
want these to show up unless we enter data. So that's where the IF
and IS will come in handy. I'm going to hit F2. We're going to use the IF
function, because I'm either going to have the IF
put the formula in, or it's going to put the
syntax for show nothing, which is double quote, double quote. So we have one of two things,
and that's a perfect job for the IF function. But in order to determine
which of the two things to put in the cell, we
need a logical test. Now, you get to
decide when you're building the template,
how do you want to build that logical test? There's at least five or
six or seven different ways to do this. Well, let's try IS BLANK. Now, remember, IS BLANK
looks for empty cells, so I'm going to click on
relative cell reference the date for this transaction,
close parentheses. That means when I copy
this formula down, when it gets to here, it
gets a true, true, true. Right now, IS BLANK, because
this cell is not empty, will get a false. So comma, the value of true. That's for when we get down
to the remaining rows where there's not a transaction. I want to use the syntax
for show nothing, which is double quote, double quote. Now, double quote, double quote
is really a zero length string. It is considered text, but it's
what we use to show nothing. Comma. Otherwise the value
of false-- we're going to put the
formula into the cell. Now I come to the end. Close parentheses, Control,
Enter, double click and send it down. And sure enough, look at that. Now if I come down here and
put today's date, look at that. Now, notice the IF
function did its job. It actually ran and
calculated the formula result. Right here, it's not
running the formula. It's actually dumping this--
this zero length text string. We could actually come
over here and test this-- equals IS NUMBER. And notice right
here, it'll say true. But right here, it'll say
false, because it's seen something that's not a number. Hey, guess what? We could also use the IS text. I'm going to say is this text. This will come out
false, but watch this. Even though it looks like
there's nothing there, now we have a method
of determining what's actually in the cell. And it is. It's a zero length text string. Now, I'm going to delete this
right here, delete these. And now let's try to
do that same formula, but from a different
point of view. I'm going to say IF. And our logical test, instead
of saying are you empty, I could just as easily
say are you a number. Now, notice we know that dates
are numbers, so that will work. But if this is a number,
what do we want, comma, for the value of true? We actually want the formula. So notice, this is from a
slightly different point of view, but the formula
will give us the same result. I'm going to add
the Addition column, subtract the Subtraction column. Comma, otherwise, double quote,
double quote to show nothing. Close parentheses, Control,
Enter, double click and send it down. Now I want to come over
here and enter a date. There we go. Control Z. We
could still do this from another point of view. Equals if, and I'm
going to say IS TEXT. Now I need to pick a column
that I'm always going to use. I'm going to always put text
and some number in this column, so it is always
going to be text. There we go. Close parentheses, that
will give me true or false. If it's true, it means I've
entered something here, so I want the formula for
adding and subtracting-- add, subtract. That's the value of 2. Otherwise, double quote, double
quote, close parentheses, Control, Enter, double
click, and send it down. Now watch this. When I come and put a date,
notice these two are working, but this one is operating off
the check number transaction column. So it's not until I put
that that one shows up. Now, how do you know
which one of these to use? Well, you've got to
pick a column that's always going to have something. I usually think
that the date is one of the most important things. Yes, there's always
going to be a number, but we don't know which column. But dates for
historical transactions like this-- you've always
got to have a date there. So that's a good one. This is probably
a good one, too, unless you're going
to enter text dates. If it's really the most
important one is this one and you're always
going to enter text, then perhaps you
want to use this one. So that's a little fun
with IF and IS functions for conditional calculations. Our calculation here was putting
either text to show nothing or running a formula. Now we want to go over to the
sheet Pivot Table or Formula. OK, our last topic,
since we've been doing a lot of formulas with
conditional calculations in this video. When do you use formulas? When do you use pivot tables? Now, back in video 3, we
did a bunch of pivot tables with lots of conditions
and criteria. Here are some
general guidelines. Really, for formulas,
if the solution needs to instantly update when
formula inputs are source data changes, then formulas
are usually preferable. So for example, this is a
pretty complicated formula. This pivot table
is easy to create. But if the data changes to 25
Enter, that instantly updates. It's no problem. Pivot tables, you just have
to right click Refresh, and it instantly updates. But sometimes-- Control
Z Z-- but sometimes, you want that instant update, and
formulas give that to you. If you're making aggregate
calculations of many criteria and you don't need the solution
to instantly update, then of course, pivot
tables are preferable. Now, there are
many more functions in an Excel spreadsheet than
there are in a pivot table, so that's another consideration. Pivot tables only do
aggregate calculations. Hey, for big data,
formulas calculate slowly. We saw back in video number
3 the Power Pivot data model with pivot tables could
deal with a lot of big data. Pivot tables are easy. Hey, this monthly report is much
easier than that big formula. But sometimes, it's easier
to create your solution with formulas, and sometimes
it's easier with a pivot table. Clearly, this type
of calculation in some of the pivot tables
we did earlier in the class are much easier than formulas. Let's go look at a situation
here on the sales team. Here is that OR
criteria formula. This is a little bit easier
to create one formula, copy it down and over. Because for a pivot
table, you'd have to do four different
pivot tables and then filter each one. All right. This was an epic video
about making calculations with conditions or criteria. We talked about some
comparison between pivot tables and formulas. We talked about IF
and IS functions. We talked about the
AND or OR functions for accounts receivable
credit analysis. We talked about on the Or sheet
all sorts of different ways to calculate when we
have an OR logical test. And on the And
sheet, we started off talking about AND logical tests. All right. Next video, we
are going to start talking about LOOKUP functions. We'll see you next video.
