Multiplying Percentages causes 15 decimal place format

[David Morris]

I frequently multiply two cells formatted as percentage with either 1 or 2 decimal places. Excel formats the cell with the multiplication formula as percentage with (usually) 15 decimal places and widens the column width to display the result; manually reformatting back to the source cells' format (percentage / 1 or 2 decimal places) and minimum column widths is an ongoing issue. If this is not a bug, what is the solution; as it probably is a bug, how do we get Microsoft to fix it?

Here are my steps: in Windows 8.1 / Excel 365, open a new workbook. Verify the default cell format is General and that the Normal Style's number format is General.
A1: Enter 0.175, change format to percentage, 1 decimal place to display 17.5%
A2: Enter 0.174999379377994 (15 digits after 0.), change format to percentage, 1 decimal place to display 17.5%
A3: Enter formula =A1*A2
Excel re-formats column width to display 3.062489139114890%, format check shows percentage, 15 decimal places
I must then change cell format to percentage, 1 decimal and double click the column to reset the width

B1: Enter 0.175, change format to percentage, 1 decimal place to display 17.5%
B2: Enter 0.1749793774 (10 digits after 0.), change format to percentage, 1 decimal place to display 17.5%
B3: Enter formula =B1*B2
Excel displays 3.062139104500%, format check shows percentage, 12 decimal places
I must then change cell format to percentage, 1 decimal and double click the column to reset the width

If the same process is done in columns C & D, changing the values in row 2 to 8 and 6 digits after 0., the results are percentage formats with 11 & 9 decimal places respectively.

I don't have Excel 2010 / 2013 to test and verify, but I believe this bug started in 2010. Excel 2007 used the source cells' formats to format the results cell.

I googled sitemrexcel.com "15 decimal places" and learned that on Jan 10/14, Smitty answered a similar question by saying this was default behavior with un-recalled logic, while on Jul 1/14, RoryA wrote that it sounds like abug.Over the last couple of years, I have googled about this issue many times and learned that this is indeed a bug. It literally bugs me as I have many workbooks with formulas multiplying 2 percentages and have to constantly reformat both cells and column widths. Any and all help is appreciated. If not, I may have to revert to Excel 2007.

 

[David Morris]

Try this one, mole999, or anyone else:
Also, as I am new here, please advise whether it's okay to expand my question from only multiplication to include +, -, & /, or if I should create a new post.
This bug happens with any of the four basic mathematical operations (+-*/) performed on cells formatted as percentage:
If the operation results in a value with many decimal places, the cell with the operation formula is formatted as percentage with up to (15+leading 0's) decimal places AND the column is automatically widened to display them all.
In my case, these calculations are done on a financial portfolio of cash plus 25 securities to produce a quarterly forecast for the next two years, with 2 columns per qtr, 1 for buys/sells/income and 1 for the qtr's new subtotal. Each security has a target expressed as a percentage of the portfolio's total. There are columns for the initial values and percentages, the target values and percentages, the income values and the yield percentages. I use this to plan when to buy how much of each security and to play "what if" for multiple scenarios.
Earlier today, I inserted a column to calculate the difference between the securities' final percentages of the total and their targets.
Cell H4 contained =P4/$P$36, with a value of 0.0253212165303891 and formatted to display 2.5%
Cell AM4 contained =AK4/$AK$36, with a value of 0.0252516868334505 and formatted to display 2.5%
Cell I4 was formatted as general and I entered =AM4-H4
Excel widened column I to display -0.006952969693855770% as it auto-formatted to percentage with 18 decimal places.
I changed this to 2 decimal places, auto-filled the formula and formatting down the column and double-clicked the column label to adjust the width.
I have no idea why Excel did not auto-format to 17 decimal places, 2 for the initial 0's + 15 for the digits in decimal places 3-17, but an extra 0 was added in place 18.
I guess I shall have to start using =ROUND(+-*/,6) whenever I'm calculating using percentages.

 

[mole999]

excel has only ever worked to 15 places (as far as i know), I think it was by initial design to accommodate other accounting software, so its not an actual bug, and has been reported of such many times. As for column widths, if it didn't auto expand then you would only see #################. Its something we all have to work with.. You might consider ROUNDUP or ROUNDDOWN as ROUND will just make a standard calculation.

I think it is possible to use longer strings with text entry data, but the calculated output will be to the 15

 

[David Morris]

excel has only ever worked to 15 places (as far as i know), I think it was by initial design to accommodate other accounting software, so its not an actual bug, and has been reported of such many times. As for column widths, if it didn't auto expand then you would only see #################. Its something we all have to work with.. You might consider ROUNDUP or ROUNDDOWN as ROUND will just make a standard calculation.

I think it is possible to use longer strings with text entry data, but the calculated output will be to the 15

Yes, I know all that, including when to use ROUND, ROUNDUP and ROUNDDOWN. In my portfolio workbook, I don't need all 15 digits, which is why I am choosing to use =ROUND( ,6). As previously explained, =ROUND( ,2) looses too many digits for my purposes. Since I did explain that, I was confused by your comment about being able to use longer strings with calculated output limited to the embedded 15.

Your comment that Excel's 15 "places" is not a bug leads me to conclude that you have not understood what the bug is, and may be confusing decimal places and digits.
Excel uses up to 15 digits (not decimal places) in calculations. This is not a limiting factor for me, and most users would not say it is a bug.

I tested =ROUND(AM4-H4,6) with the cells described in my previous reply.
Excel displayed the 0.007% value as 0.0%, auto-formatting From general To percentage with 1 decimal place, and as 0.0% was not too wide for display, the column width did not change. No bugs here.

What is the bug? Excel does NOT do the same formatting as it does for the ROUND function if the formula is =AM4-H4 (or =AM4+H4, =AM4*H4 or AM4/H4). Instead, it auto-formats to percentage with enough decimal places to display all 15 digits (digits, not decimal places: 0.001234567890123450% consists of the 15 digits but is formatted with 18 decimal places), and then Excel correspondingly adjusts the column width to display the formatted value.

I do not have access to all versions of Office / Excel to verify this, but I believe this bug was introduced in the 2010 version, which is why I have mentioned reverting to my 2007 version.

The fix for this bug is simple: use the same formatting logic as is used to format I4 when entering =ROUND(AM4-H4,6):
Excel uses the format from the first term, AM4, which was formatted as percentage with 1 decimal and displayed AM4's 15 digit value of 2.52516868334505% as 2.5%) to format I4 and display the 0.007% value as 0.0%.

 

[mole999]

Yes, I know all that, including when to use ROUND, ROUNDUP and ROUNDDOWN. In my portfolio workbook, I don't need all 15 digits, which is why I am choosing to use =ROUND( ,6). As previously explained, =ROUND( ,2) looses too many digits for my purposes. Since I did explain that, I was confused by your comment about being able to use longer strings with calculated output limited to the embedded 15.

Your comment that Excel's 15 "places" is not a bug leads me to conclude that you have not understood what the bug is, and may be confusing decimal places and digits.
Excel uses up to 15 digits (not decimal places) in calculations. This is not a limiting factor for me, and most users would not say it is a bug.

I tested =ROUND(AM4-H4,6) with the cells described in my previous reply.
Excel displayed the 0.007% value as 0.0%, auto-formatting From general To percentage with 1 decimal place, and as 0.0% was not too wide for display, the column width did not change. No bugs here.

What is the bug? Excel does NOT do the same formatting as it does for the ROUND function if the formula is =AM4-H4 (or =AM4+H4, =AM4*H4 or AM4/H4). Instead, it auto-formats to percentage with enough decimal places to display all 15 digits (digits, not decimal places: 0.001234567890123450% consists of the 15 digits but is formatted with 18 decimal places), and then Excel correspondingly adjusts the column width to display the formatted value.

I do not have access to all versions of Office / Excel to verify this, but I believe this bug was introduced in the 2010 version, which is why I have mentioned reverting to my 2007 version.

The fix for this bug is simple: use the same formatting logic as is used to format I4 when entering =ROUND(AM4-H4,6):
Excel uses the format from the first term, AM4, which was formatted as percentage with 1 decimal and displayed AM4's 15 digit value of 2.52516868334505% as 2.5%) to format I4 and display the 0.007% value as 0.0%.

Good luck, i'm not going to respond further

 

[David Morris]

Good luck, i'm not going to respond further

Thanks for all your input! Again, I would not have started using =ROUND( ,6) without you.