Excel - Problem summing values that is text 2

[keysol]

Hi,

I received a spreadsheet in which I wish to SUM a column of numbers to then determine the average for the whole.
When I use the auto-sum button there is no proposed range between the brackets. When I change the format of one cell to Number the auto sum works only for that cell. This implies "editing" each cell in the column manually to complete the conversion. Not a good idea.

So, how do I change a whole column of numbers which are apparently text to actual values, in one fell swoop?

Thanks,

Gerr

 

[DaleWatson123321]

keysol,

Here's a fairly easy solution...

1) In the adjacent column, write a formula using the "Value" function ...e.g. =Value(a1).

2) Copy the formula down for each value.

3) Highlight the column of formulas.

4) Use "Edit - Copy-Special - Values", and paste the
values over top of the original "text numbers".

5) Erase the formulas.

Having converted the text numbers to actual values will then allow your sum formula to work.

Hope this resolves your situation.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[dianemarie]

Hi Gerr, I'm assuming that Excel is not recognizing your range as numbers even after changing your cell format. I have also run into this, mostly with spreadsheets that have been exported from other programs into Excel. This might save you a few clicks from Dale's suggestion:

Key the number 1 into a blank cell - be sure the blank cell is formatted as General. Copy that cell. Highlight the range of cells you want recognized as numbers. Right-click and choose Paste Special (or Edit, Paste Special) and choose Multiply. This will force the number formatting.

 

[Dreamboat]

Diane, good one!

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[Dreamboat]

Oops. Sorry, Dale, but she IS right. :-X

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[keysol]

Hi all,

Thanks for the help. It's now a snap!

Gerr

 

[DaleWatson123321]

Diane, and Dreamboat,

...always happy to be "come second" because the "first place contributor" is naturally providing everyone with a better solution, including myself.

Thanks !!!


...Dale Watson dwatson@bsi.gov.mb.ca

 

[dianemarie]

Yikes...I feel like I just beat Michael Jordon in a game of one-on-one. LOL