Dividing by zero #Div/!

  • Thread starter Thread starter Al Copone
  • Start date Start date
A

Al Copone

Any help will be appreciated
I am working on a spread sheet for fiancial analisis. In doing so I
sometimes enter data for two quarters/years or four. I then set up a
cell for the total increase/decrease.
Example:
A1 A2 A3 A4 A5 A6 A7 A8
#Div/0! 4thq/y #Div/0! 3rdq/y etc. with the total going in A8.

In analizing a company I use a lot of percentages. Example
4th q/y - 3rd q/y / 3rd q/y (or A2-A4/A4) with the answer going to
A1. The problem is when I don't have enough information to fill in
all of the cells. This gives me the #Div/0! error in the cell that is
suppose to give me the product of the equation (A1 in the above
example). I've got an "excel bible" which addresses this problem and
talks of Arrays as the answer. It's not very clear on where the Array
goes. Does the Array go just in the totals column? In all of the
cells that have the products in them? Or do you need to setup another
cell with an array to reference back to the totals column? The
formula that I tried using =sum(A2-A3)/A3 works when all of the
columns are filled in but other wise it's a no go. Thanks for any
help.
John
 
Al (or John), forget about arrays. In any cell that produces an error
because some other cell is blank, use an IF statement along these lines:

=IF(ISERROR(my formula),"",my formula).

So to use your example:

=IF(ISERROR((A2-A3)/A3),"",(A2-A3)/A3).

This will yield an empty cell whenever any of your input cells are empty,
and a formula result when your input cells have been filled.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com
 
First, you never need

=SUM(A2-A3)

it just adds a superfluous function. Use

=(A2-A3)

instead.

Also for any cell where the divisor may be zero, use this instead:

=IF(A3=0,"",(A2-A3)/A3)

or, a bit more efficiently:

=IF(A3=0,"",A2/A3-1)
 
Back
Top