I want to simply add several rows in a column - all containing currency
formatted cells. No matter what I do the total always appears as "0.00". My
formula seems pretty straightforward: =SUM(G7:G135). I've researched this
for the past 2 hrs. and even had our IT people involved, but no seems to be
able to help. I have a fairly large spreadsheet and don't want to have to
revert to my calculator. Anyone?

Don't mean to too obvious but have you formatted the relevant cells to
currency ? If not, highlight the column or cells containing the data, right
click, Format Cells, Number, Currency then choose your currency and decimal
places.


--
Es


"Gaileen" wrote:

I want to simply add several rows in a column - all containing currency
formatted cells. No matter what I do the total always appears as "0.00". My
formula seems pretty straightforward: =SUM(G7:G135). I've researched this
for the past 2 hrs. and even had our IT people involved, but no seems to be
able to help. I have a fairly large spreadsheet and don't want to have to
revert to my calculator. Anyone?

Try using "Trace". This will lead you to from where the data is coming
up. Also look for Circular References (if there is). Just today I
helped our accountant over the same issue the same way.

Thanks,

Shail





Gaileen wrote:
I want to simply add several rows in a column - all containing currency
formatted cells. No matter what I do the total always appears as "0.00". My
formula seems pretty straightforward: =SUM(G7:G135). I've researched this
for the past 2 hrs. and even had our IT people involved, but no seems to be
able to help. I have a fairly large spreadsheet and don't want to have to
revert to my calculator. Anyone?

Try this in a different cell:

=count(g7:g135)

This counts the number of cells that contain numbers--it will not include the
cells that are text, but looks like numbers.

If you get something you don't expect (like 0), then one way to fix this is to:

select an empty cell
edit|copy
select G7:G135
edit|paste special|Add

Your text numbers will be converted to number numbers.

Yep. Just formatting the cells as Numbers (or currency or General or anything
else) won't change the value in the cell.

After you've converted the values to real numbers, you can apply the formatting
you want.

==========
If this doesn't help, maybe you've taken your data from a web site???

If you have, you could have those pesky HTML non-breaking spaces in the cells,
too.

David McRitchie has a macro that cleans up this kind of stuff:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()"

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gaileen wrote:

I want to simply add several rows in a column - all containing currency
formatted cells. No matter what I do the total always appears as "0.00". My
formula seems pretty straightforward: =SUM(G7:G135). I've researched this
for the past 2 hrs. and even had our IT people involved, but no seems to be
able to help. I have a fairly large spreadsheet and don't want to have to
revert to my calculator. Anyone?

--

Dave Peterson

the cells may be formatted as currency, but the data may still be text.
If the data comes from an outside database, this is real possible.
try putting 1 in an empty cell, copying it, Select G7:G135 and paste special
multiply
to try to convert to numbers.

you could also in an empty cell try =isnumber(G7) to see if they are numbers
or text.

"Gaileen" wrote:

I want to simply add several rows in a column - all containing currency
formatted cells. No matter what I do the total always appears as "0.00". My
formula seems pretty straightforward: =SUM(G7:G135). I've researched this
for the past 2 hrs. and even had our IT people involved, but no seems to be
able to help. I have a fairly large spreadsheet and don't want to have to
revert to my calculator. Anyone?

I have tried everyone's suggestions but to no avail. I have used empty
cells, new worksheet, multiplying, subtracting, adding, but still get "0".
For some reason Excel is just not calculating basic math, either that, or I'm
having one huge mental block here that I'll never live down. I'll re-boot my
system and make sure I have all current MS Office updates and if that doesn't
work, I'll try your links below. Thanks for the effort. Signed;
Spiralling into Deep Depression.

"Dave Peterson" wrote:

Try this in a different cell:

=count(g7:g135)

This counts the number of cells that contain numbers--it will not include the
cells that are text, but looks like numbers.

If you get something you don't expect (like 0), then one way to fix this is to:

select an empty cell
edit|copy
select G7:G135
edit|paste special|Add

Your text numbers will be converted to number numbers.

Yep. Just formatting the cells as Numbers (or currency or General or anything
else) won't change the value in the cell.

After you've converted the values to real numbers, you can apply the formatting
you want.

==========
If this doesn't help, maybe you've taken your data from a web site???

If you have, you could have those pesky HTML non-breaking spaces in the cells,
too.

David McRitchie has a macro that cleans up this kind of stuff:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()"

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gaileen wrote:

I want to simply add several rows in a column - all containing currency
formatted cells. No matter what I do the total always appears as "0.00". My
formula seems pretty straightforward: =SUM(G7:G135). I've researched this
for the past 2 hrs. and even had our IT people involved, but no seems to be
able to help. I have a fairly large spreadsheet and don't want to have to
revert to my calculator. Anyone?

--

Dave Peterson

Thanks everyone for your effort. I have fixed the problem by rebooting my
system. Aaargh! Now if I can Excel to caculate how may calories I burned
and strands of hair I've pulled out in the last 3 hrs. it'll be worth every
penny!

"Gaileen" wrote:

I want to simply add several rows in a column - all containing currency
formatted cells. No matter what I do the total always appears as "0.00". My
formula seems pretty straightforward: =SUM(G7:G135). I've researched this
for the past 2 hrs. and even had our IT people involved, but no seems to be
able to help. I have a fairly large spreadsheet and don't want to have to
revert to my calculator. Anyone?