Simple formula question

[kleivakat]

I'm trying to add up numbers in a column. It should be very simple, but I
keep getting a zero result. The formula is =sum(J5:J113). I thought that
the problem was maybe in the formatting of the numbers in column J since they
were exported from another program, so I went into the Format menu and made
sure they were formatted as currency. I'm still getting a zero result. Any
other suggestions?

 

[akphidelt]

Just a random question, when you highlight one of the cells does a clipboard
pop up? Most of the time when I import data if a number is showing up as text
you'll be able to change it by highlighting the data and clicking the
clipboard and changing the value to a number.

 

[kleivakat]

No, no clipboard shows up. But I did change the entire J column to currency
in the Format menu. I do think it has something to do with formatting....
I just can't figure out what!

 

[kleivakat]

One more very strange thing. I just re-entered a couple of numbers exactly
as they show up in the cell, and the numbers that I re-entered are calculated
in the formula, just not the other numbers that were imported. I've tried
copying and paste special (values), and that doesn't help. It's a fairly
long sheet, so I really don't want to retype the numbers if I don't have to.

 

[kleivakat]

I just figured it out. The numbers imported with a dollar sign, which must
continue to be read as something other than a number. Since I have the
column formatted as currency and to show the dollar sign, I didn't see that
they were entered differently. I'll just eliminate the dollar signs and that
seems to take care of it. You learn something new every day.

 

[KC Rippstein hotmail com>]

go way off to the right in a blank area of your worksheet and copy any blank
cell
Next, highlight the data giving you trouble (J5:J113) and click on Paste
Special, "Add"
This will convert the column to proper numbers.

 

[David Biddulph]

What do you get from
=ISNUMBER(J5) ?
=ISTEXT(J5) ?
Have you checked whether are spaces or non-printing characters in the cells
in question, either before or after your "numbers"?

 

[akphidelt]

wow, that actually taught me something too.

Glad you figured it out!