On Jun 20, 12:40 pm, Parry wrote:
I found a general cell, put a one in it, copied it to the cell in question
that kept showing the formula...and now I have a total, but it's wrong.
Instead of 17.00 it's 0.00. I also don't know what you mean by Paste Sepcial
using the Multiply operation...so I couldn't do that. Can you be more
specific?
--
Novice
Thanks

"Ken Johnson" wrote:
On Jun 20, 10:48 am, Parry wrote:
Hi Dave...this worked for a couple of other formulas, but not for the ones in
question. I'm running Excel 2007. I'm stumped. Can you help?
--
Novice
Thanks

"Dave" wrote:
Hi,
In earlier versions, Tools menu, Options.
On the View tab, deselect Formulas.
Not sure how this differs for 2007
Regards - Dave.

Reformatting from Text to Number doesn't fix the problem of the cells
being treated as Text. Enter a 1 into a free cell that is definitely
General format then copy it then select E27:E42 and Paste Special
using the Multiply operation. The Sum should then be OK if that was
the problem.

Ken Johnson

Hi Parry,

I know you've solved the problem.
Just thought I'd try to clear up any confusion about my suggestion.
If cells (E27:E42) are formatted as Text and then have numbers entered
into them, a formula summing those cells will return 0.
If you reformat the cells to either General or Number, even though the
format has changed, Excel still treats them as Text and the sum
remains 0.
My suggestion was to get Excel to treat them as numbers by copying a 1
from a General formatted cell, then select E27:E42 then go Edit|Paste
Special... to bring up the Paste Special dialog. On that dialog there
is an area with the heading "Operation". The choices are None
(default), Add, Subtract, Multiply and Divide. If you choose Multiply
(or Divide), then pasting multiplies (or divides) each cell by 1. This
has no effect on the cells' values but from that point on Excel treats
them as numbers and the sum formula should return the expected result.

If you have a cell with a formula and instead of seeing the calculated
result you see the formula, then that can be caused by the cell being
formatted Text before the formula was entered. The above method will
not work in this case.
You can instead reformat the cell to General, select the whole formula
in the Formula Bar (or double click the cell and select it in the
cell) then copy and paste (Ctrl C then Ctrl V) then Enter.

Having said all that, I prefer RagDyer's method. It works the same way
for both formulas and values.


Ken Johnson