easy one, thank you: =SUM()

[thankyou]

Hi. Hope you can help. I think this may be easy for you, but appreciated nonetheless!

When I drag down a column of numbers, say,

4
7
2
9

and then hit the "E" (Sum), the last item (in this example the 9) changes to "=SUM()", instead of calculating and putting the sum on the following empty cell.

What am I doing wrong? Thanks so much.

PS If it helps, I've noticed this for all other one-touch toolbar functions too (like "Average") and I've made sure the cells are formatted as numbers as opposed to text etc. I'm sure it's something simple, maybe a setting. Thank you!

 

[thankyou]

Rman- YES, when I do that, it (as you probably know) copies the data from that "A" column (well, I used C but whatever), and then, importantly perhaps, it does summate or average or whatever I want from autosum > whatever function. So, seems like a good sign! What does that mean is the prob and solution? Thanks a lot.

 

[starl]

The items in the cells are digits (2,3,4,5 etc), so doesn't matter what format I have them in. They started as "text". To answer your 2nd question, if I highlight a few of those cells, no it doesn't show the sum in the lower bar. However, I do notice that: a) if I type in "fresh"/new data (digits and formatted as numbers) into other cells on the same sheet and select them, YES, those cells show a "Sum = (correct sum)" b) if I copy some of those to new other cells on the same sheet- no matter what the format- it does not do that. c) Interestingly, as you and Rman intimate, if I put a) and b) next to each other, a) is left-adjusted and b) is right-adjusted. The right-adjusted ones (the fresh numbers I enter into fresh cells (versus copying them from the original data) properly summate in lower bar and from the "E" functions. Hope this clarifies.

If you are NOT getting a sum in the status bar then those digits are NOT numbers. It does not matter if you have the cells formatted as numbers - Excel does NOT see them as such. Not until you actually force them to be numbers. You said they started off as text numbers. Did you highlight the cells and select General or number? That was NOT enough to force them to become numbers. Try this
1. Select the range
2. Go to Data, Text to Columns
3. Click Finish

now - if this doesn't work - then we have a bigger problem.

 

[repairman615]

It means that the original cells are text.

The +0 in the formula is forcing the original cell to a number.
One solution could be to:
Copy
Paste Values...convert text to numbers.

This is a good sign.

 

[starl]

note that repairman's test also proves your "numbers" are still text - adding 0 also forces them to turn into proper numbers.

 

[repairman615]

I forgot to say that after pasting, you can choose the ! box while the range is still selected (the error box maybe??) and choose convert to numbers.

 

[starl]

I wondered about that too, repairman.. but it was never mentioned previously.. figured they were turned off.

 

[repairman615]

I wondered about that too, repairman.. but it was never mentioned previously.. figured they were turned off.

Hello Tracy,
I am a little thick sometimes .. I am sure this is gonna make simple sense, but what did you think was off?

 

[starl]

The green triangle/error pointer thingy. Background error checking

 

[thankyou]

YEP THAT DID THAT TRICK. BOTTOM LINE (for me): 1. thanks all. 2. nice trick to see if truly #s or text, highlight some cells and look at the lower/status bar for sum = (correct sum of highlighted cells). If you DON'T see that, then you have to... 3. Highlight (only one column at a time from what I could see)...then Data > "Text to columns". Thanks again Rman, Starl, and y'all invidividual "brain" cells. Together, we are the shizzit (sheet).

 

[starl]

If you are importing a lot of data, I'd suggest turning on Background Error Checking - it'll highlight any numbers that are stored as text and prevent a LOT of headaches!