+ Reply to Thread
Results 1 to 5 of 5

Help on SUM cells with formulas

  1. #1
    Benjamin Orozco
    Guest

    Help on SUM cells with formulas



    At F199 I have the formula =SUM(F177:F198) but rows 177 to 198 contain
    formulas like =(+E180-E179) and F199 formula won't work.
    Do I need to extract correctly the values only? is it possible?
    Thanks much.
    Benjamin



  2. #2
    Ragdyer
    Guest

    Re: Help on SUM cells with formulas

    What exactly does "formula won't work" mean?

    What kind of return are you getting?

    No answer? - Wrong answer? - Error message?
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "Benjamin Orozco" <benjamin.orozco@sbcglobal.net> wrote in message
    news:2CGBd.5968$yV1.1069@newssvr14.news.prodigy.com...
    >
    >
    > At F199 I have the formula =SUM(F177:F198) but rows 177 to 198 contain
    > formulas like =(+E180-E179) and F199 formula won't work.
    > Do I need to extract correctly the values only? is it possible?
    > Thanks much.
    > Benjamin
    >
    >



  3. #3
    Benjamin Orozco
    Guest

    Re: Help on SUM cells with formulas

    Hi Rag, it turns out that the formula =(+E180-E179) gives me an output
    of -1E+05 and it gets carried to cell F199.
    I tested the scenario with only numbers and it works, what I think is
    happening is that E180 is empty and E179 has a value number therefore the
    formula outputs a negative number. The F column gets populated in time but
    the -1E+05 error will always exist on the immediate cell to be filled and I
    still don't know how to get around this.
    Benjamin
    "Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
    news:eMSKR4F8EHA.2788@TK2MSFTNGP15.phx.gbl...
    > What exactly does "formula won't work" mean?
    >
    > What kind of return are you getting?
    >
    > No answer? - Wrong answer? - Error message?
    > --
    > Regards,
    >
    > RD
    >
    > --------------------------------------------------------------------------

    -
    > Please keep all correspondence within the NewsGroup, so all may benefit !
    > --------------------------------------------------------------------------

    -
    > "Benjamin Orozco" <benjamin.orozco@sbcglobal.net> wrote in message
    > news:2CGBd.5968$yV1.1069@newssvr14.news.prodigy.com...
    > >
    > >
    > > At F199 I have the formula =SUM(F177:F198) but rows 177 to 198 contain
    > > formulas like =(+E180-E179) and F199 formula won't work.
    > > Do I need to extract correctly the values only? is it possible?
    > > Thanks much.
    > > Benjamin
    > >
    > >

    >




  4. #4
    Ragdyer
    Guest

    Re: Help on SUM cells with formulas

    I believe that your cells are formatted in scientific notation.
    What you described appears to be in the neighborhood of
    -10,000
    When you click in the cell, you should see a recognizable number in the
    formula bar.
    Click in the cell, then <Format> <Cells> <Number> tab,
    And see what you have formatted for that particular cell.

    Format all your cells to either "General" or "Number".

    Also, in your formulas, the "+" sign and the parenthesis are unnecessary.

    =E180-E179
    is a perfectly legitimate formula.

    Just had another thought.
    Open a brand new sheet and type:
    1234567890
    Then <Enter>.

    Now, drag the column width very slightly narrower.
    You should see the number change to scientific.
    If not, drag it ever so slightly narrower again until it does change.
    Once you see the scientific notation, continue the narrowing of the column
    and you'll eventually see the display change to the more familiar lb. signs,
    which usually signifies a too narrow column of numbers.
    The double display change usually occurs when a cell is originally formatted
    to "General", as a new sheet usually is.

    So, check the width of your columns on your original sheet if their format
    appears to be OK (general).
    --
    HTH,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------

    "Benjamin Orozco" <benjamin.orozco@sbcglobal.net> wrote in message
    news:WgHBd.5978$yV1.4100@newssvr14.news.prodigy.com...
    > Hi Rag, it turns out that the formula =(+E180-E179) gives me an output
    > of -1E+05 and it gets carried to cell F199.
    > I tested the scenario with only numbers and it works, what I think is
    > happening is that E180 is empty and E179 has a value number therefore the
    > formula outputs a negative number. The F column gets populated in time but
    > the -1E+05 error will always exist on the immediate cell to be filled and

    I
    > still don't know how to get around this.
    > Benjamin
    > "Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
    > news:eMSKR4F8EHA.2788@TK2MSFTNGP15.phx.gbl...
    > > What exactly does "formula won't work" mean?
    > >
    > > What kind of return are you getting?
    > >
    > > No answer? - Wrong answer? - Error message?
    > > --
    > > Regards,
    > >
    > > RD
    > >

    >
    > --------------------------------------------------------------------------
    > -
    > > Please keep all correspondence within the NewsGroup, so all may benefit

    !
    >
    > --------------------------------------------------------------------------
    > -
    > > "Benjamin Orozco" <benjamin.orozco@sbcglobal.net> wrote in message
    > > news:2CGBd.5968$yV1.1069@newssvr14.news.prodigy.com...
    > > >
    > > >
    > > > At F199 I have the formula =SUM(F177:F198) but rows 177 to 198 contain
    > > > formulas like =(+E180-E179) and F199 formula won't work.
    > > > Do I need to extract correctly the values only? is it possible?
    > > > Thanks much.
    > > > Benjamin
    > > >
    > > >

    > >

    >
    >



  5. #5
    Jerry W. Lewis
    Guest

    Re: Help on SUM cells with formulas

    If you only want to sum positive numbers, you could use
    =SUMIF(F177:F198,">0")

    IF F199 should only return a value if there are numbers in both cells,
    you could use
    =IF(COUNT(E179:E180)=2,E180-E179,"")

    Jerry

    Benjamin Orozco wrote:

    > Hi Rag, it turns out that the formula =(+E180-E179) gives me an output
    > of -1E+05 and it gets carried to cell F199.
    > I tested the scenario with only numbers and it works, what I think is
    > happening is that E180 is empty and E179 has a value number therefore the
    > formula outputs a negative number. The F column gets populated in time but
    > the -1E+05 error will always exist on the immediate cell to be filled and I
    > still don't know how to get around this.
    > Benjamin
    > "Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
    > news:eMSKR4F8EHA.2788@TK2MSFTNGP15.phx.gbl...
    >
    >>What exactly does "formula won't work" mean?
    >>
    >>What kind of return are you getting?
    >>
    >>No answer? - Wrong answer? - Error message?
    >>--
    >>Regards,
    >>
    >>RD
    >>
    >>--------------------------------------------------------------------------
    >>

    > -
    >
    >>Please keep all correspondence within the NewsGroup, so all may benefit !
    >>--------------------------------------------------------------------------
    >>

    > -
    >
    >>"Benjamin Orozco" <benjamin.orozco@sbcglobal.net> wrote in message
    >>news:2CGBd.5968$yV1.1069@newssvr14.news.prodigy.com...
    >>
    >>>
    >>>At F199 I have the formula =SUM(F177:F198) but rows 177 to 198 contain
    >>>formulas like =(+E180-E179) and F199 formula won't work.
    >>>Do I need to extract correctly the values only? is it possible?
    >>>Thanks much.
    >>>Benjamin
    >>>
    >>>
    >>>

    >
    >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1