avoid

Remove the DIV#/0! Error in Excel



Sharing buttons:

hey it's just a top we're from blue peak

and computer training in this video

we're going to look at an error that you

sometimes might see in Excel it's the

devera so we're going to look at how to

deal with this error also going to look

at why it would appear on your

spreadsheet well very simply the div

error appears when you try and divide

something by zero that's mathematically

not possible you have to divide

something by something divided by zero

will cause the error so let's just look

at this column here where we have some

de Vere's what we're doing is we're

dividing column a by column B it works

on all of these occasions but as soon as

we divide by zero we get the error or if

we have a blank cell we end up dividing

by effectively zero in this instance

you'll also get the div error here so

how can we deal with these scenarios

this looks pretty ugly on your

spreadsheet but it may be on occasion

that you know you have got blank cells

or whatever rather than having this

error turning up you just want Excel

either to ignore or just to leave the

cell blank or maybe return a zero well

two ways you might deal with this issue

is to use one the if error function and

that's available from Excel 2007 and the

other option if you have an earlier

version of Excel is just to use a

standard if function so let's just look

at how to use the if error I just delete

these options you can see this actually

wherever there's been a devourer it's

left a blank let's see how that actually

operates if error is very easy to use

basically you've got your value so our

value is the thing that you're

interrogating or looking at to see if it

will return an error so we're saying

this divided by this

okay comma the second argument is what

you want to return if the value returns

an error so if I put in that two

consecutive double speech marks

basically saying return nothing or you

might put a zero in instead let's just

put nothing so it will do the

calculation if an error isn't returned

so it will perform the value part of the

function or perform that otherwise it

will return nothing if you wanted to use

an if instead what you could do for

example is say equals if you could say

if column B contains a zero that blue

isn't turning out very well on the

screen isn't so let me just open this up

so you can see what I'm doing so if B 3

contains a zero then what you want to do

is return either a zero or a blank or

whatever otherwise you want to perform

the calculation so that effectively does

the same thing I've just chosen to

return the zero instead now this is a

fairly obvious example of where a div

error might occur but sometimes in our

training we it occurs places you might

not expect so for example what we've got

here is basically a list of sales

figures for Bob Bill and Brenda and then

what we've done is summed up those sales

figures for each month using a simple

sum function but then we want to find

out what percentage January's figures is

of the total figures so what we've done

is basically in this cell here we've

said be 14 divided by I 14 now let me

just delete these formulas that formula

works fine for here it's dividing that

figure by that figure if I copy this

across

instead of actually doing the same

calculations for these numbers it's

returning the div error now if you know

much about EXO you'll realize why that's

happening I click up here you can see

that as I copy the formula long it's

changing the letters in both of my cell

addresses so what we end up with is

we're dividing by empty cells for going

onto the cell because it's divided by

that cell so what we need to do to

overcome this error is to actually fix

our reference to I 14 so when we copy

across our reference to I 14 doesn't

change so what we do is we go back to

our first formula we click into the cell

reference here by pressing f4 on my

keyboard and that fixes that reference

by putting dollars before each character

now copy across and I know now going to

get that devera another scenario you

might get is where you've set up a

spreadsheet with formulas already placed

in it waiting for values to be entered

so for example we have a call center

here where Bob Bell and Brenda work and

it's not until I put my numbers in that

do I get rid of this dear Vera because

what i'm doing here is i'm dividing b18c

18 by B 18 okay and until I put the

numbers in I'm not getting I'm getting a

div error because I'm dividing by 0 I'm

divided by nothing again

so you know if I put some numbers in 218

drop calls 13 I get a perfectly

respectable answer but until then I get

these ugly devours so again you might

use an if error function to get rid of

this problem so you could say very

simply once you've typed in your phone

just put in if error you've already got

your formula there you just put the

affair in front of it and you say

basically return nothing if the Devourer

occurs or any error occurs in fact

okay so devera problem occurs if you do

if you're trying to divide something by

zero even if you're inadvertently trying

to find divide something by zero and

also where you set up formulas in

spreadsheet where the values haven't yet

been entered you can appear pretty ugly

if you've got loads of devera so number

of ways around it if error if or it's a

matter sometimes of just fixing a cell

reference so there'll be loads of other

scenarios where the devera occurs but

hopefully you can see some ways of

resolving them here okay thank you very

much