Help on SUM cells with formulas

[Benjamin Orozco]

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

[Ragdyer]

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" <[email protected]> wrote in message
news:[email protected]...
>
>
> 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
>
>

[Benjamin Orozco]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> >
> >
> > 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
> >
> >
>

[Ragdyer]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > >
> > > 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
> > >
> > >
> >
>
>

[Jerry W. Lewis]

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" <[email protected]> wrote in message
> news:[email protected]...
>
>>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" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>
>>>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
>>>
>>>
>>>
>
>