|
|
Transcripts for the above video clip:
SUMIF PROJECT
The If functions in Excel including SUMIF and Countif, are extremely
useful in that they will perform a certain action depending on the
results of a cell.
However there is a risk to the way Excel does this. So for example
what we’ve got here is some Part names, and how many Units were
purchased, and here is a bit of a summary where it will tell you
that Part name AAA, 3 were purchased.
How it does that is
-
it goes to the section and it looks for all the
AAA’s
-
and then it adds up how many units were purchased
What is important to realize is, that as far as Excel is concerned
when it looks for the AAA it’s looking for exactly AAA, and
therefore if there is any misspelling it could be a problem.
Of more concern however is how Excel handles spaces so for example
here you can see that
-
this correctly adds up to the total units purchased
-
and I’ve built a little error check in there,
-
let’s say for whatever reason someone goes into
this AAA and adds something that cannot visible be seen, for
example just add a space,
-
the net result is as far as Excel is concerned,
this AAA space, is no longer the same as this AAA
-
and therefore it does not add it in,
This can cause major problems in a spreadsheet. The way to avoid
this is first and foremost
-
you must build in some error checks
-
and I highly recommend with any If function,
Coutif’s and Sumif’s to basically put all the possibilities down
and add them up
-
and make sure that it equals the raw data,
The other alternate is to enforce that no errors can happen at this
stage, the name stage and this can be done using data validation.
It is extremely important that you understand this, because if for
whatever reason you had not included these sections here and been
able to check it, the only other way to pick up that Excel was not
adding up the correct cells would be to manually do it. And the
whole point of Excel is to avoid manual calculations.
So it is very, very important to build error checks into your
spreadsheets
|
.gif)
.gif)
