Forum Discussion
Unable to get cells with % Formulas to add up to100% but yet the Sum Total in the cell shows 100%
Example Column Cell K5=SUM(C5,E5,G5,I5)
Then all of Column K is added up with the following formula K31 =SUM(K5:K30)
Column L, rows 5 thru 30 are a % of the rows Total (Cell K31) or the formula of Cell K31 =SUM(K5:K30)
Cell L5=K5/K31 to get a % or 24% This is duplicated for Cells L6 through L30 for a total of 100% in cell L31 but the actual total of the column is only 99%. How can this be corrected so that the numbers are accurate and don't need to manually add to see if the total is correct. I have had to do manual adjustments to make the numbers add up to the 100%. Your assistance is greatly appreciated.
If all formulae are correct that's most probably Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps | Microsoft Learn
To calculate each percent try to use ROUND with, let say, 6 digits.
- dscheikeyBronze Contributor
Excel calculates 100 % correctly
Can you change the example so that your problem can be understood.
- Susan2021-10-26Copper Contributor
dscheikey Here is a quick view of the excel chart - The total % of Responses manually adds up to 99%
- dscheikeyBronze Contributor
This is a normal rounding problem. If you set the rounding to 3 decimal places, you will also get 100,000 when adding the displayed values. With one decimal place 99.9 and with 2 decimal places 100.1.
You will not be able to solve this without "incorrectly rounding" one of the values.