Link to home
Start Free TrialLog in
Avatar of DPMTL
DPMTL

asked on

Excel 2013 - issue with decimal place format when adding cells with percentages in them

We've recently upgraded to Excel 2013 and users are finding an issue with the decimal place format when adding up a number of cells with percentages in them.  We set the percent format to 2 decimal places but when the field calculates, it blows out the setting to 17 decimal places.  I have all the latest service packs installed and have tested it on a variety of workstations in-house using an existing Excel file with this formula in it or creating a new spreadsheet as a test...we can make this problem happen.  Of the 3 places you can control the format of the decimal place (\Options\Advanced, cell format or conditional formatting) all have been verified as not being the cause of the issue.

Here's the formula that results in the blown out decimal places:

 =C8*C62+C16*C63+C29*C64+C36*C65

Each of these cells has a formula in it that is calculating a number which is formatted as a percent.

Darrin
Avatar of regmigrant
regmigrant
Flag of United Kingdom of Great Britain and Northern Ireland image

it would help  if you post an example of the file I can't reproduce the behaviour in 2013 using random percentage values in those cells - even if I use much higher numbers of decimal places
Avatar of DPMTL
DPMTL

ASKER

These are the values and Cell positions related to the formula in my original email.  As you can see, the number in the middle of the list with the 17 decimal places is the cell with the formula in it.

      
2.52%      C8
-5.29%      C16
-7.02%      C29
9.65%      C36

-0.21660058514035400%      

30%        C62
35%        C63
15%  C64
20%  C65

Thanks,

Darrin
>>-0.21660058514035400%
make sure the cell's format is a Number or Percentage and not a Text ?

and then check the cell formatting and make sure it's properly formatted?

you could also copy the cell formatting of C36 to the targeted cell containing value: -0.21660058514035400% by using Format Painter at your top left corner at Home tab, see if that works for you?
Avatar of DPMTL

ASKER

Hi,

Thanks for your response.  I verified the cell formatting and all is set properly (percentage, 2 decimal places) and there are no conditional formatting settings.  I tested the "Format painter" as per your note above and it didn't resolve the problem.  After further testing, it's clear that this is not an issue with my specific file per say...if you used that formula to calculate percentages of any value in a completely blank worksheet on your end (using Excel 2013 latest updates), you should get the same result as I am getting.  The strange thing is, that it may work once and present properly but if you recalc once or twice or double click on the formula cell and then close it forcing the recalc, the decimal places get blown out.  

Thanks,

Darrin
can you upload your file so we can diagnose for you? I guess it's probably more like a "value issue" in your spreadsheet.
ASKER CERTIFIED SOLUTION
Avatar of regmigrant
regmigrant
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DPMTL

ASKER

Thanks to regmigrant and all others who contributed on this case, I appreciate your assistance.

Darrin