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!

 

[repairman615]

Hello!

Try selecting the cell below and then press the autosum button.

 

[thankyou]

Hi R-man! Great 2cu again. OK, if i click the empty space below the 9, instead of the 9 converting to =SUM(), that empty cell converts to =SUM(). Thanx

 

[repairman615]

Check that the cells are formated to text??


Glad to see you as well!

 

[thankyou]

indeed they are. i get the same prob either way, whether formatted as #s or text (including the empty cell that follows).

 

[repairman615]

Are these formulas returning number?

I can reproduce this with formulas in all cells like:

="1"

Using the quotes makes it text...even though the format says general

however

=1

in all cells make auto sum act normal

...if a formula is used...post it up.

How is the invoices working out?

 

[thankyou]

I'm sorry Rman. I had trouble understanding you.

Looking at the first part, you were asking about formulas. I don't have any formulas in this case. I'm just trying to do (shame) the most basic thing like highlight a few cells in a column and then hit one of the quick-and-easy formulas up top near the "E", for example, "Average" or "Sum". But that =SUM() keeps replacing the last cell instead of actually putting that calculation, almost like it's asking me to enter the N7:N10 type of thing. Anyway, I'm just remembering when you just highlight a set of cells and then hit the one-touch formulas. It was automatic. Thanks for your help. Oh! Yeah, definitely! The invoices are holding up strong. Thank you!

 

[repairman615]

The way you are thinking it should work is correct.

Without any alignment (left, right, center) are the numbers in the cells at the right or left of the cell?

If left, the cell is text. When this happens, I get the undisired result.
=SUM()

If right it seems to work as intended for me.

If it still doesn't for you try entering a few number into another set of unused cells to see if it also happens there.

 

[Peter_SSs]

I'm not sure I have followed all this but if you select all your numbers and the blank cell underneath before clicking the AutoSum, does that do what you want?

 

[thankyou]

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

Anyway, I tried opening up a new excel and keying in some fresh numbers, and lo and behold it works fine. However, when I copy over even a few "innocent" cells from the original .xls to a fresh one, same problem.

It's weird cuz I don't see any formulas in the data (eg 3 or 4 cells) that I'm copying over, but if I type new ones into new excel, it's fine, it's just apparently the cells within my excel sheet are "rigged" lol. So, still not entirely sure what's going on. But I hope this narrows things down a bit for us.