Adding a column of formulas

Thanks:  0
Likes:  0

# Thread: Adding a column of formulas

1. ## Adding a column of formulas

How do I add a column of cells with formulas without getting #DIV/O! for the total?

2. ## Re: Adding a column of formulas

What Excel Version are you running?

2000

4. ## Re: Adding a column of formulas

The more important question is How do you want to handle the Cell(s) within the Sum(RANGE) that at least one (maybe more) are themselves producing a #DIV/0 value?

You could change your column formulas to (and copy down):
=IF(ISERROR(A2/B2),0,(A2/B2))

This assigns a 0 (zero) to any cell producing a #DIV/0 value and
your SUM() should now add up all your values, Converting the #DIV/0's
to o (Zero)..

In xl2007 there is a new function:e so:

=IFERROR((A3/B3),0)

5. ## Re: Adding a column of formulas

I can't change the cells that have #DIV/O!. They are a formala that changes to a number when information is added to another sheet. As the #DIV/O! changes I want the number added to the total.
Is there any other way of getting around my problem?

6. ## Re: Adding a column of formulas

Post the cell content (the formula) of a cell that is currently producing a #DIV/0 in your sum range

7. ## Re: Adding a column of formulas

Try

=SUM(IF(ISNUMBER(A1:A10),A1:A10))

which is an array formula and must be entered using CTRL + Shift + Enter, not just Enter. If entered correctly Excel will surround the formula in the formula bar with curly braces {} - do not try to add these yourself.

8. ## Re: Adding a column of formulas

The sum is =SUM(B5:B56)

From B23-B56 cells have #DIV/O!

Formula for B23-B56 cells is =(12500/OFFSET(Data!C\$27,(ROW(B19)-1)*45,0,1,1))*(OFFSET(Data!C\$64,(ROW(B19)-1)*45,0,1,1)-OFFSET(Data!C\$27,(ROW(B19)-1)*45,0,1,1))

When you say post the cell content (the formula) of a cell that is currently producing a #DIV/0 in your sum range
How do I add it to make it work?

9. ## Re: Adding a column of formulas

VoG has offered exactly what you want (which leaves the #DIV/0 condtion within your Sum range).

Back in the early days there was the focus to eliminate the problem at it's earliest time, which meant altering the formula to produce a 0, versus a #DIV0 result. Your posted formula is a bit over the top, so forget making any change to it.

10. ## Re: Adding a column of formulas

=SUM(IF(ISNUMBER(A1:A10),A1:A10))
Peter your suggestion does not work, gives me a sum of 0.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•