How to not include blank cells in excel formula
How to Ignore Formula / Not Calculate If Cell is Blank in Excel
Timecodes:
hi it's chester at blue pecan computer
training
and in this video we're going to look at
how to perform a formula
only when the cell is not blank
so here's my scenario i've got
a record of the number of calls a call
center
and then the number of calls that were
dropped
and what i want to do is to find out
what percentage of calls
were dropped by each salesperson
and there's two ways you can do this in
excel you can evaluate whether there's
an empty text string
or you can use the is blank function but
there are important differences
between the two methods and that's what
i want to explore
in this video tutorial so first of all
we'll start by evaluating whether there
is an empty text string
so to do that basically to say equals
the cell address equals an empty text
string and that's two speech marks
nothing
in between so if i press return on that
one obviously that's not
empty but if i copy down
you can see that wherever i've got an
empty cell and there is literally
nothing in these cells
i get a true now i could do the same
with this blank
and it will appear that is blank does
exactly the same thing
so equals is blank and then your cell
address
close the bracket and copy this down
and i'll get exactly the same result so
it appears that these two tests
give the same results
and what i could then do is say if
the cell is empty then return an empty
text string
i don't want the formula to be performed
but
if it is an empty value of force i would
say
take the drop calls amount and divide it
by
the total number of calls that would
then give me
the percentage of calls that were
dropped now you can see that where there
is a blank cell
in the drop calls column i get an empty
text string as my result
this divide by zero error is because i
actually have a blank cell
in the number of calls column as well
we'll deal with that later on
you can see it works fine now is blank i
could do the same thing
put that with an if there's my logical
test
that's true i want to return an empty
text string if not
i want to do the same calculation divide
drop calls by the total number of calls
i'll copy this down and i get
exactly the same result so on the
surface that appears
these two methods the empty text string
test
and the is blank test do exactly the
same job
but they don't let's go into the next
scenario what we're going to do
is divide this column by this column
drop calls adjusted so what i've done
here i've said if the
drop calls are less than three then
don't return a value for drop calls
otherwise return the drop calls so there
is a formula in here that returns
an empty text string if this is less
than three
which it has in this cell here let's do
those tests again
let's say does this contain an empty
text string
and if i copy this down you can see
that i get the two truths there and the
truth there so
wherever this if statement returns an
empty text string i get a true
now let's try this blank
and what i want to do is evaluate
whether that cell is blank
and you see i get a false so is blank
does not consider these cells to be
blank because this if statement is
returning
an empty text string so now hopefully
you can see
the difference between these two test
methods
is blank will only return blank if the
cell
literally has nothing in it this if
statement is returning an empty text
string
whereas the empty text string test
obviously returns true
if it finds an empty text string in the
cell now the other thing i want to clear
up is how to evaluate
whether more than one cell is blank so
we're getting these divided by zero
errors here
because our number of calls cell is
blank and down here
both sales are blank so we'd want to
know
whether either the number of calls sale
is blank
or the drop calls sales are blank
but to do this we could run an or
statement
and the first test would be is
this cell blank or does it contain an
empty text string
and then i could say or is this cell
blank
or does it contain an empty text string
i've got those two tests and the or
statement will return true if
either of the tests are met if i copy
this down
you can now see that even the divide by
zero
column row rather returns a true
so if i was to put this within my
if this is my logical test the or
statement
either of those things are true i want
to return an empty text string
otherwise i want to divide the number of
drop calls
by the total number of calls
copy this down you can see that i only
get results where i have
both the number of calls and the number
of dropped calls
okay so i hope that's cleared up the
distinction between
testing for an empty text string and
testing if the cell is blank
and also how to evaluate whether
multiple sales
are blank or not that's all i wanted to
cover in this particular video
hopefully that's been useful if it has
please subscribe
and i'll see you next video
you
Related queries:
how to include blank cells in excel formula
how to avoid blank cells in excel formula
how to add blank cell in excel formula
how to ignore blank cells in excel sum formula
how to exclude blank cells in excel average formula
how to ignore blank cells in excel if formula
how to blank cell in excel formula
how to exclude blank cells in excel
how to not count blank cells in excel
