Multiplying Percentages causes 15 decimal place format

David Morris

New Member
Joined
Feb 15, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.
PercentDecimalDisplay.jpg
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
 
Last edited:

David Morris

New Member
Joined
Feb 15, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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%")
 
Last edited:

David Morris

New Member
Joined
Feb 15, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

New Member
Joined
Feb 15, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,872
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
See the article in the link below about sending bug reports to Microsoft about Excel.

 

David Morris

New Member
Joined
Feb 15, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
See the article in the link below about sending bug reports to Microsoft about Excel.

Excellent, MARK858 - Thanks!
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,961
Messages
5,599,061
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top