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
DPMTLAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

regmigrantCommented:
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
DPMTLAuthor Commented:
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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>-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?

Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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?
DPMTLAuthor Commented:
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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
can you upload your file so we can diagnose for you? I guess it's probably more like a "value issue" in your spreadsheet.
DPMTLAuthor Commented:
regmigrantCommented:
I took a look at your sheet and can see nothing wrong with what you done and I can duplicate the behaviour you mentioned - but only if I press enter after editing the cell with F2, clicking elsewhere in the sheet leaves it 'normal'. I also found that the actual format changes from 2 to 17 in the dialog.

I think you have hit upon an example of one of Excel's floating point issues though that probably doesn't make you feel better - however putting brackets around the formula seems to solve the problem.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DPMTLAuthor Commented:
Thanks to regmigrant and all others who contributed on this case, I appreciate your assistance.

Darrin
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.