Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Adding a column of formulas

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?...

  1. #1
    Board Regular
    Join Date
    Feb 2009
    Posts
    103

    Default Adding a column of formulas

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

  2. #2
    Board Regular jim may's Avatar
    Join Date
    Jul 2004
    Location
    Roanoke, VA
    Posts
    6,334

    Default Re: Adding a column of formulas

    What Excel Version are you running?
    To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie

  3. #3
    Board Regular
    Join Date
    Feb 2009
    Posts
    103

    Default Re: Adding a column of formulas

    2000

  4. #4
    Board Regular jim may's Avatar
    Join Date
    Jul 2004
    Location
    Roanoke, VA
    Posts
    6,334

    Default 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)
    To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie

  5. #5
    Board Regular
    Join Date
    Feb 2009
    Posts
    103

    Default 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. #6
    Board Regular jim may's Avatar
    Join Date
    Jul 2004
    Location
    Roanoke, VA
    Posts
    6,334

    Default 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
    To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie

  7. #7
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,255

    Default 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.
    HTH, Peter
    Please test any code on a copy of your workbook.

  8. #8
    Board Regular
    Join Date
    Feb 2009
    Posts
    103

    Default 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. #9
    Board Regular jim may's Avatar
    Join Date
    Jul 2004
    Location
    Roanoke, VA
    Posts
    6,334

    Default 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.
    To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie

  10. #10
    Board Regular
    Join Date
    Feb 2009
    Posts
    103

    Default 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.

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com