in this video i'm gonna cover returning true blanks from your formulas if you're an excel user
you're probably used to the fact that if you create a formula and you want it to return
blank you need to return double quotes to give an example let's say i'm trying to create a formula
here and that should take our quantity here and multiply it by price so that should work
just fine for this line no problem and if we drag it down it's also going to work for these
lines but if i want to drag this formula all the way and not have these zeros display here
then i need to do some sort of handling to say that if there is no quantity or there's no price
then let's leave it blank now how do we do that we usually do some sort of if statement so we go
here and do if and there are different ways to go about this one way you can do for example a count
of these two so i can say if that is less than two if the count of this two is less than two we know
one of them hasn't been filled yet quantity or the price in those cases i want to return blanks so
if you're an excel user you would just do double quotes here to return a blank right
comma otherwise we want to do our formula so i would do this times this close parenthesis
hit enter and drag this formula all the way down so you'll see what happens now these are blanks
and then if i go and enter a number here well there's no price yet but if i add a price as well
then this starts working and it does what it's supposed to do so that's our formula so this
formula seems to work for the most part the only problem is these are not really blanks
these are going to be double quotes empty strings so what that means is that if we check
if that is actually blank using is blank and click on this
see we're going to get a false because that's not really blank another way to check if it's
blank or not is to just count so if i just use something like counter to count these
you'll see what i get is 9 because what it does it actually counts all of these but it
also counts all of these blanks because they're not really blanks so how can we resolve this
so what we could do we could go back to our if statement and instead of returning this double
quotes here i can just leave this empty like that to say just return blank in this case
and then continue with my formula like this and drag this formula down
and you'll see that visually my results are the same but now if i check if this is blank using
is blank see i'm going to get true because that's actually blank and if we use something like
counter to actually count how many cells we have that are not empty we're gonna get five so there
you go that's how you return a true blank from something like an if statement let's actually add
this inventory quantity column so i want to match this item to inventory quantity here
and bring that over in the cell so i'm gonna do that using vlookup so i'll do something like
vlookup and i'm gonna use search key to be this comma then i'm gonna select this
lookup array as a table i'm gonna use f4 to make it absolute reference comma and in this table
i would like to get results from inventory quantity columns so i'm going to do 2 comma and
this is going to be what we call exact match or not sorted so we'll do 0 hit enter and you'll see
that this returns 298 which for this item if we'll look through this list that's correct that's how
much it is now to get the rest i can drag this down like that and you'll see that well i get some
of these cases to get me the quantity and when there is no quantity i get any errors so again i
could replace all of these with true blanks and the way you could do that you could basically
just wrap this vlookup in if error function and usually most people are used to using if error
to just do if error go after this do a comma and use something like double quotes to return
something that's empty but again remember the problem with this is that double quotes are not
true blanks so we're gonna get the same problem meaning that if i check if this is blank it's
gonna say false it is not actually blank now the way we can resolve this we can simply just go back
now in google sheets this value if error argument is not really required so we can just skip
this completely and just use if error around vlookup like this hit enter and drag it down
and you'll see visually it's going to look the same but if i check if this is blank
it's going to say true because now that's a true blank cell now obviously if i drag it
up these are not blanks and these are not blanks but these would be blanks
again so there you go that's our effair function for any effair function case in
google sheets you could just not have any return for error and that's going to return a true blank
and you could obviously use all of this with array formulas and things like that
so if you didn't want to have all of these formulas you just want to do an array formula
you could have just done your like array here instead of a single cell do your command shift
enter or ctrl shift enter to get your array formula wrap around this hit enter and it's
gonna work exactly the same way if i check if this is blank it will actually return true
and it works just fine so there it is thanks for watching and i'll see in the next video