Hey guys it's Eddie the Magic Monk. Today I want to talk about how to make
the sum function in Excel ignore errors so let's just create some fake data so I
can show you guys the problem so let's say that I have two columns of numbers
let's make the first column called y the second column called X and let's just
put in some fake numbers and let's put in some fake numbers here and then I'm
going to create a third column and I'm going to make the third column equal to
the number in the first column divided by the number in the second column so if
I press ENTER and then if I click on this and fill down then you can see all
of these numbers are generated so 6 divided by 6 is 1 4 divided by 7 is 0.5
7 and so on but there is a number with an error an error so if I want to sum up
the numbers in this column what will happen is if I press equals sum
brackets and I highlight these numbers and I finish off the bracket look what
happens it won't do it it won't do it because there is an error in one of
these numbers now the easiest way to fix that is if you just wanna if you just
delete the contents of this cell then it will add the numbers without that error
but if you don't want to do that then it's a bit complicated so what we're
going to do is we're going to
use another column and let's call this column z1 and what I'm going to do is
I'm going to use an if function I'm going to say well firstly let's show you
a function called is error so equals is error alright what this function can do
is check whether a number is an error or not so if you go is error bracket choose
the number on the Left finish the bracket press ENTER and then if you fill
this down what happens is it checks if the number on the left is an error or
not so you can see that all of these numbers here is not an error so it's got
false but when it hits this cell you can see that this number is an error so it
says true and then all of these other ones are false again so why is this
useful well let's change up the formula a little bit we're going to say if the
number on the left is an error then we're going to fill in this cell with a
blank so that's written with two quotation marks however if it is false
which means if it's not an error then I will just put in the contents of this
cell so if you finish off with a bracket and fill down again then it's
automatically giving us the the number on the left without the without the cell
that's an error so now you can just sum this column so sum this column and then
the cell with the error disappears and I guess the best way to do this is to
preempt the error in your calculation in your formula in
the first place so if you go back to the formula instead of just doing that you
can go if if D 2 divided by e 2 is an error so is error D 2 divided by e 2
then just show blank otherwise show D 2 divided by e 2 so if you preempt that in
the first place then when you fill down you wouldn't get the error in the first
place so what I'm trying to say is you guys should try and avoid the error in
the first place rather than trying to get the sum function to ignore the error
now the same thing goes with the average function as well okay thanks for
watching guys see you next time.