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.

 

[mole999]

could you

=ROUND(A1*A2,2) as the formula

OR custom format 0.00% on the cells

I do understand starting from the whole number and trying to mould to percent is inconvienent

 

[David Morris]

Thanks, mole999. You have inspired a work around until the bug is fixed or I revert to Excel 2007:

Using =ROUND(A1*A2,2) with my example values results in Excel leaving A3 as the default format General and a value of 0.03, and no change in column width.
This 0.03 is almost 2% less than the 0.0306248913911489 non-rounded value, too large a difference.
However, since using ROUND forces me to to take the additional step of formatting the formula cell to percentage, 1 decimal place, I could use =ROUND(A1*A2,6) (=0.030625) before this reformatting.
The underlying bug would then only force me to remember to use ROUND to 6 decimals and then format to percentage, 1 decimal.
This would be marginally better than using =A1*A2 as I wouldn't have to fix the column width after formatting to percentage, 1 decimal place.

I still want to know how to get Microsoft to fix the bug and save me from having to take any extra steps. There's no reason for Excel to have changed percentage multiplications from using source cell formats (=A1*A2 > no additional steps) to 15 decimal place & extreme column width formatting > 2 additional steps.

 

[mole999]

if you didn't mind it as text you could =text(ROUND(A1*A2,2),"0.00%") or as number =--text(ROUND(A1*A2,2),"0.00%")

or your need =--text(ROUND(A1*A2,6),"0.0%")

 

[David Morris]

mole999, were you able to replicate the bug, where =A1*A2 results in percentage with 15 decimal place formatting? If so,which version(s) of Excel did you use? I don't see my 365 version listed under your name, so I am not sure we're on the same page.

Assuming that you were able to duplicate the bug, it appears did you did not understand my reply that your first rounding option was too inaccurate as it truncates all results below 10% to a single digit.
Your rounding suggestion was helpful as it made me realize that by rounding to 6 (instead of 2), results would be truncated to an acceptable 5 digits for percentages less than 10% and 6 digits for percentages >= 10%.

My test using =--text(ROUND(A1*A2,2),"0.00%") result in a General number format with displayed and actual single digit results of 0.03.
My test using =--text(ROUND(A1*A2,6),"0.00%") result in a General number format with displayed and actual three digit results of 0.0306.
What were your test results that made you suggest what you did?

 

[mole999]

I think 365 was 16 then 19, my cells are formatted as %, not sure if that was automatic or developed

my last offering was =--text(ROUND(A1*A2,6),"0.00%") minus the second zero which displays as 3.10% for me

If I multiply 17.5 & 17.4999379377994
I get

306.248913911490000000 which I expect

 

[David Morris]

Has anyone recreated the percentage & column width formatting bug initially described?
If so, any suggestions on how to inform Microsoft and submit a request to fix it?

Meanwhile, using the mole999 inspired ROUND to 6 followed by manual percentage formatting appears to be a workable option pending my reversion to Office 2007.

mole999: I must confess I did not see the last line of your reply where you offered a formula with rounding to 6 decimal places, followed by truncating the text to one percentage decimal place, and then followed by converting the percentage format to general to yield a final value And display of 0.031. As this does not meet either the minimum accuracy or percentage formatting criteria, please explain your thinking; I am missing where you are coming from.

 

[MARK858]

See the article in the link below about sending bug reports to Microsoft about Excel.

Sending Effective Bug Reports to the Excel Team

I recently had a conversation with several members of the Excel team about how they process submissions that are sent in through the Feedback icon that appeared on the Help tab in Excel. There is a specific set of steps you can use that will make...

www.mrexcel.com

 

[David Morris]

See the article in the link below about sending bug reports to Microsoft about Excel.

Sending Effective Bug Reports to the Excel Team

I recently had a conversation with several members of the Excel team about how they process submissions that are sent in through the Feedback icon that appeared on the Help tab in Excel. There is a specific set of steps you can use that will make...

www.mrexcel.com

Excellent, MARK858 - Thanks!

 

[mole999]

I'm not understanding the difference between 3.1% and 0.0306 using 17.5%, only there is an issue with wrapped text not displaying the % sign