What Excel Version are you running?
This is a discussion on Adding a column of formulas within the Excel Questions forums, part of the Question Forums category; How do I add a column of cells with formulas without getting #DIV/O! for the total?...
How do I add a column of cells with formulas without getting #DIV/O! for the total?
What Excel Version are you running?
To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie
2000
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)
To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie
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?
Post the cell content (the formula) of a cell that is currently producing a #DIV/0 in your sum range
To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie
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.
HTH, Peter
Please test any code on a copy of your workbook.
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?
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.
To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie
=SUM(IF(ISNUMBER(A1:A10),A1:A10))
Peter your suggestion does not work, gives me a sum of 0.
Like this thread? Share it with others