Help on SUM cells with formulas

  • Thread starter Thread starter Benjamin Orozco
  • Start date Start date
B

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
 
What exactly does "formula won't work" mean?

What kind of return are you getting?

No answer? - Wrong answer? - Error message?
 
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
 
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).
 
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
 
Back
Top