Adding a column of formulas

hotrod

Board Regular
Joined
Feb 3, 2009
Messages
103
How do I add a column of cells with formulas without getting #DIV/O! for the total?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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)
 
Upvote 0
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?
 
Upvote 0
Post the cell content (the formula) of a cell that is currently producing a #DIV/0 in your sum range
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
=SUM(IF(ISNUMBER(A1:A10),A1:A10))
Peter your suggestion does not work, gives me a sum of 0.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top