# How to round up percentage values in a pivot table that add up to 100?

1. ## How to round up percentage values in a pivot table that add up to 100?

Please forgive me if I don't describe my problem accurately, I've been forced into this and I haven't used excel for many years.

I have several survey responses that I'm trying to make pie charts out of. I have mapped these to a pivot table, I think correctly, and now I'm trying to make pie charts for each question.

What I can't solve for the life of me, is how to perform rounding at the percentage values that adds up to 100. Here is one sample. ``Please Login or Register  to view this content.``
If I format the number to 0 decimals, the percentages add up to 99%. If I try to ROUNDUP the pivot table value cells I get "You cannot edit the formula for a calculated item while custom calculations is used". I don't want to write them by hand because there's several questions.

Accuracy doesn't really matter either. If I can push the highest percentage to fill the rest up to 100, I'm fine with that!

Thank you for any help!

PS: I'm so happy Excel forum still exists! I used it first around 10 years ago, and it's the only website that remembers me every year on my birthday!  Register To Reply

2. ## Re: How to round up percentage values in a pivot table that add up to 100?

Welcome to the Forum mechanicarts !

The rounding error you are describing is an inherent mathematical problem with rounding; it's not an Excel problem. However, here is a solution to round the largest number in such a way as to force the total to be 100%. This assumes your percentage numbers are in column A.

A better solution might be possible if you attach your file with sample data. See yellow banner at the top of the page.

Values as displayed
 A B C 1 Original Data Rounded numbers Adjusted numbers 2 71.43% 71% 72% 3 7.14% 7% 7% 4 14.29% 14% 14% 5 7.14% 7% 7% 6 0.00% 0% 0% 7 100.0% 99.0% 100.0%

Underlying formulas
 A B C 1 Original Data Rounded numbers Adjusted numbers 2 0.7143 =ROUND(A2,2) {=IF(A2=LARGE(\$A\$2:\$A\$6,1),1-(SUM(ROUND(\$A\$2:\$A\$6,2))-ROUND(A2,2)),ROUND(A2,2))} 3 0.0714 =ROUND(A3,2) {=IF(A3=LARGE(\$A\$2:\$A\$6,1),1-(SUM(ROUND(\$A\$2:\$A\$6,2))-ROUND(A3,2)),ROUND(A3,2))} 4 0.1429 =ROUND(A4,2) {=IF(A4=LARGE(\$A\$2:\$A\$6,1),1-(SUM(ROUND(\$A\$2:\$A\$6,2))-ROUND(A4,2)),ROUND(A4,2))} 5 0.0714 =ROUND(A5,2) {=IF(A5=LARGE(\$A\$2:\$A\$6,1),1-(SUM(ROUND(\$A\$2:\$A\$6,2))-ROUND(A5,2)),ROUND(A5,2))} 6 0 =ROUND(A6,2) {=IF(A6=LARGE(\$A\$2:\$A\$6,1),1-(SUM(ROUND(\$A\$2:\$A\$6,2))-ROUND(A6,2)),ROUND(A6,2))} 7 =SUM(A2:A6) =SUM(B2:B6) =SUM(C2:C6)

This is an array formula. If you are not using Excel 365, after typing in the formula, do not hit ENTER--hit CTRL+SHIFT+ENTER. You have done it correctly if the formula in the formula box has {braces} around it. You cannot type in the braces; they are just an indicator that it is an array formula.  Register To Reply

3. ## Re: How to round up percentage values in a pivot table that add up to 100?

Thank you sincerely very much for your time. I struggled a bit to understand it, but in the end, I found a much simpler solution.

Since I only need to visualize the data in charts (and not for scientific use), I just clicked on the pie labels, I selected "percentage" and it did a nice rounding on its own. I can't believe the solution was that simple!  Register To Reply