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]

Hi there Peter. I'm sorry, I did not see your question before replying to Rman.

While I think my reply to him will also clarify my situation for you, to answer your question, yes, whether I include the blank cell at the end, or just the cells with numbers, I still get that same prob. [If I start a fresh .xls, I do not. However, if I copy over some of those cells (which don't seem to have formulas???), then it brings over the problem to the fresh sheet].

Thank you.

 

[repairman615]

Strange..

Whether I make them left or right-adjusted, text or number, I get the same problem.

I am still unsure about their alignment. Basically without any alignment specified where is the number, left or righthand of cell?

You will see the difference using these formulas in different cells:
="1"
=1

the first cause the problem,
the second works as expected.

 

[crook_101]

Might be worth a try in case you have some gremlins,,,,,,go to your original sheet and highlight your "dodgy" cells, depending on version of excel:

2003 - Edit>>Clear>>Formats

2007+ Home tab>>Clear>>Formats

HTH
Ian

 

[thankyou]

Thank you Rman. To answer your question, the numbers are to the left (text). I always use shortcut and do Ctrl F1, which makes it so easy to see their format (as number, general, text, etc). But they are left-adjusted (text in this case). Even if I reformat them as numbers, I experience the same issue.

 

[thankyou]

(make that Ctrl 1, not Ctrl F1 of course). I am writing my answer to HTH now

 

[thankyou]

Thanks Ian. Well, I did a clear all formats on those cells, which for Rman & others info cleared formatting and changed the cells from "text" to "general". However, I tried an autosum, and again, the same problem as originally posted. Still no solution. Thanks for efforts.

 

[thankyou]

While we're here, I'm using Windows 7 and I notice every time I do a save on the excel file--even if I don't make any changes-- it moves it to the upper left of my screen. Just curious if anyone knows solution to that too. It happens all the time and is annoying.

 

[starl]

Are you absolutely sure the numbers are actually numbers? Just because you format a cell as General, Number, etc doesn't mean the format of the data changes. If you highlight the numbers, do you get the sum in the bottom right of the status bar?

Is it only this one strange Excel file that moves when you save? Or does a brand new workbook also do this? How are you doing a Save? Do you have any addins (regular or COM) installed?

 

[repairman615]

Ok time to break out the stops, How about a new approach...

Try this and see if we can get to the bottom of this:

=A1 + 0

where A1 is your top cell of original list.

Place into unused column and drag down untill you get to the bottom of your original list.

now try the auto sum on this.

Any difference?

 

[thankyou]

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.