Excel 2003 sum formula problem

thenoo · 16 Jul 2011

I have been entering currency data in Column B in Euros to an EXCEL 2003 s/s and the formula used has added these totals entered in that Column correctly until I reached a total sum of 74364.70 Euros ,this being the sum of entries in Rows B6 to B82 . This was reached with the formula showing as "=SUM(B6:B82)" - without quotation marks.

The next entry into Row 83 was a sum of 27618.75 Euros.Entry of this into Column B caused the cell showing the totals to change to a series of #### but with the new total showing correctly in parenthisis when the pointer was hovered over that cell. The formula was showing as expected as "=SUM(B6:B83)".

When I experimented with other entries into Column B , I found EXCEL adding these correctly to the Total of Column B but only if the total reached after any entries did not reach or exceed a figure of 100,000 !

Can anyone please explain what the problem is here ?

Irene · 17 Jul 2011

It simply means that your column be needs to be wider.

Hover your cursor over the right hand side of the top of 'B' until you see an upright line with arrows pointing left and right > hold down a right-click and drag to the right (alternatively just double-click the line with arrows - that should extend the width to the correct point). :)

thenoo · 17 Jul 2011

Thanks Irene - that solved it !

I'm not a frequent user of EXCEL but I've never come across that problem before . Previously , when anything I've entered into a cell was too big for that cell , this merely resulted in the overhanging balance of the data being left out - a fact which then made it obvious that the column required to be made wider . This was the first time I got that result with the hashes appearing in the TOTAL cell which contained the SUM Formula. Perhaps this was the first time it happened for me using numeric data - I was usually only entering script before I suppose.

I live and learn - thanks to all the kind helpers in these Forums!

seavixen32 · 17 Jul 2011

You can also do it via Format>AutoFit Column Width.

Irene · 17 Jul 2011

thenoo said:

Thanks Irene - that solved it !

I'm not a frequent user of EXCEL but I've never come across that problem before . Previously , when anything I've entered into a cell was too big for that cell , this merely resulted in the overhanging balance of the data being left out - a fact which then made it obvious that the column required to be made wider . This was the first time I got that result with the hashes appearing in the TOTAL cell which contained the SUM Formula. Perhaps this was the first time it happened for me using numeric data - I was usually only entering script before I suppose.

I live and learn - thanks to all the kind helpers in these Forums!

The #'s only appear with numerical data is to wide for the column.

When you have entered text, it will extend across the next cell (providing that nothing is entered there). Should there be an entry in the next cell, the problem can be resolved by a right-click in the cell (where the text is) > format the cell > alignment > check the option for 'wrap text'. That will automatically make the row deeper to allow the text to 'wrap'.