avoid

Excel Magic Trick 486: Avoid #DIV/0! Error in Formula 4 Examples (Divide By Zero Error)



Sharing buttons:

welcome to Excel magic trick 486 hey if

you want to download this workbook and

follow along click on my youtube channel

and click on my college website link and

you can download the work like a

soulmate took 44 to 489 and this trick

right here we have some formula inputs

and then a formula F to put it in edit

mode and there it is it's a beautiful

calculating formula but when we clear

the template we do not want to see that

divided by zero error control Z I'm

going to show you three different

formulas three different ways the first

way is we want only the formula to show

when all three have data in them so I'm

going to click in the cell and hit f2

I'm going to use the or function I want

to say if this one is blank or this one

is blank or this one is blank or means

if any one of those are true then or

delivers a true so even if I have

something here and here the or will see

a blank here if there's nothing there

and it will deliver true and we will put

a blank in this formula otherwise when

they're all filled in we'll let the

formula calculate so we're going to use

the if and then if expects a logical

test and since we're doing it in or if

any of those are blank we need to use

the or function so or and then you can

have a lot of arguments and forget how

many to way above 250 or something like

that so we need logical tests and what

are our logical tests we want to say is

that blank or is that blank or is that

like so I'm going to click on the cell

and then type equals is that cell equal

to what well blank is double quote

double quote right comma now comma says

oh we're down to the next logical test

so now I click there and I say is that

equal to double quote double quote comma

finally is that one equal to double

quote double quote now comma closed

parenthesis on the end of the or the or

all the purpose of the or is to deliver

a true or false to the logical test

if any one of these are blank then boom

or we'll say hey give me a true here

well what do we want if it's true I'm

going to type a comma the value of true

we want to see a blank so we say double

quote otherwise if they're all filled in

then the value of false we're just going

to use that little bit of our formula

right there

come to the end close parentheses

control enter now let's test it now I'm

going to type two thousand one thousand

ten and sure enough it calculates or one

hundred now that involves all three

maybe we could do something slightly

different now this is going to be an

array formula actually we're going to do

the same logic all of them have to be

filled out before we show our formula

watch this I'm going to put this into

edit mode and then copy it copy and

because I want to place it in this next

cell but if I copied it over though

those would be relative cell references

so now I'm going to click here f2 and

ctrl V now instead of going through the

hassle of saying this one this one this

one watch this we can highlight inside

of the or and simply highlight all three

of these cells right if any of those and

then type equals blank now this is an

array inside of this inst

the or can handle an array like this in

some situations but since the or is

inside of the if it has no idea what to

do with this range or in this case it's

the the if logical test is expecting a

single cell and we gave it three so it

doesn't know what to do if you hit enter

it says value error but this is an array

form that you can tell Excel to go ahead

and calculate it it's okay if there's an

array inside of the if but you got to

use the secret keyboard shortcut ctrl

shift and enter so then we could copy

our formatting OOP like that and so now

that works now notice something about

this array formula curly brackets right

there those get put in automat

when you control shift enter so that's

another way to do it and you can test it

right now still another way what if you

didn't mind if people

it started to calculate as long as this

cell had something because this is the

cell that's causing the divided by zero

error that cell right there when it's

blank it's seen it as a zero and so it's

dividing by zero so if you only wanted

to check for this cell the formula would

be a little bit easier would say equals

if and we'd say simply is that equal

equal to double quote which is blank if

that's true then double quote otherwise

go ahead and do our formula this minus

this closed parenthesis divided by that

alright and so that does something

slightly different

I copy my formatting right there that's

copying just for many now if I do this

if I type this one in first right this

one comes up but these one don't these

ones do not show the formula because we

have two for those ones enter everything

right but you can see that one avoided

the divide by zero error all right a

little bit about divide by zero error

actually there's one other form you

could do ahead and plan to do this but

let's go ahead and do it anyway this is

only if you have two thousand seven or

ten you say equals if error if error and

the thing we're doing is open

parenthesis this minus this closed

parenthesis divided by this that's the

value and the if error is great because

it just says hey if this happens to come

out to be an error what if you want me

to put in the cell double quote so that

one is 2007 2010 totally awesome

improvement especially on bigger

formulas that involve getting rid of

errors all right we'll see a next trip