Round and Round the Column (2002 SP2)

Topic

New Reply

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!

Reply | Quote

Replies

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

Steve

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Steve

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote