# Thread: Round and Round the Column (2002 SP2)

1. ## Round and Round the Column (2002 SP2)

I just spent an hour trying to figure out why a SUMPRODUCT function gave the wrong value. I was sure that it was due to my not using the function correctly.

So I created a new book that contained only the function and a bit of data intending to post it here to get assistance with the function.

Lo and behold, when I put the function in the new book it worked fine.

So I tried eliminating everything 1 by 1 to make the 2 books look just like each other.

At the very end I decided to do a Paste Special->Column Widths and suddenly the "good" sheet went "bad".

It seems that Excel rounds numbers when the column is too narrow to show all the decimal places.

I have tried many searches of help and the web but have not found this documented anywhere.

Beware!

2. ## Re: Round and Round the Column (2002 SP2)

I have never heard this. Could you post an example of a sheet with SUMPRODUCT not working correctly due to column width?

Steve

3. ## Re: Round and Round the Column (2002 SP2)

If a column containing a big number is too narrow, it should show a series of hashes (##############). I have not come across an option to switch this on and off. I find it unusual that excel did not do this in your case?

4. ## Re: Round and Round the Column (2002 SP2)

Well, that's the point. I don'tneed to get as complicated as the sumproduct formula.

In the attched sheet, G4 & H4 both contain the value 1.5 but I see G4 as 2 and H4 as 1.5

If I widen column G, then the value is once again shown as 1.5

I have included a picture of what I see in D4

5. ## Re: Round and Round the Column (2002 SP2)

If a number is formatted to display a fixed number of decimal places, for example "Number" or "Currency" with 2 decimal places, Excel will display # characters if the column is too narrow to display that number of decimal places. If the number format is "General", Excel will adjust the number of decimal places to the column width.

6. ## Re: Round and Round the Column (2002 SP2)

Sounds like you have set Tools, Options, Calculation, "Precision as displayed".

7. ## Re: Round and Round the Column (2002 SP2)

No I haven't. I just checked. I tried setting and unsetting it and it made no difference to this.

In post 411849, Hans has said "If the number format is "General", Excel will adjust the number of decimal places to the column width." and this is the effect that I am seeing, but it does not seem to be a generally known fact.

8. ## Re: Round and Round the Column (2002 SP2)

Yes it is displayed as 2 since when displayed with no decimals 1.5 = 2. The value in the column is still 1.5 and it is used in calculations. If you need it displayed with a particular precision, set that precision (1,2 or whatever decimals). Baut note the column width must be large enough to display it.

If you have a number that has many decimals, you will notice (if the format is general) that as the column width shrinks/grows the number will adjust and round to fit the column width. The number in the cell is calc'd with all 15 figures, however.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•