# Help on SUM cells with formulas

1. ## 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. ## Re: Help on SUM cells with formulas

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

What kind of return are you getting?

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

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

>
>

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

#### 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