Excel bug report on SUM function

[Guest]

Cant seem to find any direct way of reporting a bug to Microsoft so this is
the closest I could find. Hope it's ok. Anyway, in Excel if you have some
cells formatted as 'text' and you enter numbers in them, but then decide that
you wish to change the format to 'number', the SUM function ignores the fact
that you have changed them to number format and returns a result of zero.
Other individual operators such as '+' or '-' seem to work fine, so it's
possible to get 2 different results on summing 2 (or more) cells using either
SUM(A1:B1) or A1 + B1, with only the latter giving the correct result.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...2ad606b&dg=microsoft.public.excel.crashesgpfs

 

[Guest]

The fault does not lie with the SUM function. It was designed to sum numbers
only. Changing the cell format from Text to Number, does not change the
contents of the cell to a number - see the alignment of the number in the
cell is still left aligned. However, if you select the cell, press <F2> and
<Enter>, and not only will the alignment change, the SUM will also calculate
correctly.

 

[Nick Hodge]

Equally if you have a lot to 'shift' back to 'numbers' then enter a 1 in a
spare cell, copy it, highlight the 'numbers' and select edit>paste
special...>values+multiply

(Obviously make sure you don't do this over formulas)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)