Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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?
    Regards,
    Rudi

  4. #4
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Round and Round the Column (2002 SP2)

    Sounds like you have set Tools, Options, Calculation, "Precision as displayed".
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •