# Rounding percentages in calculations

#### CVC

##### New Member
How do you make sure that all values add up to 100% when rounding is involved?

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### Domski

##### Well-known Member
Hi,

As long as you use ROUND and not ROUNDUP or ROUNDDOWN I would always expect them to if they've been calculated correctly in the first place.

I could well be wrong though.

Edit - Which I obviously am having tried it. Question - Why round the percentages in the first place?

Dom

Last edited:

#### Mike Blackman

##### Well-known Member
Hi,

How many percentages are you working with?

EDIT. HI Domski, unfortunately using round it is possible not to get exactly 100%.

#### Domski

##### Well-known Member
Hi,
EDIT. HI Domski, unfortunately using round it is possible not to get exactly 100%.

I realised that as soon as I thought about it properly. My brain's shutting down in preperation for Glastonbury I think .

#### Jon von der Heyden

##### MrExcel MVP, Moderator
You could round all but one of your percentages to say 2 decimal places. Then calculate the final percentage as =1-SUM(A1:A100) ...where A1:A100 are the rounded percentages. If all percentages are rounded to 2 places then this result will also be.

Nothing like a bit of reverse engineering

Regards
Jon

#### Mike Blackman

##### Well-known Member
If you only have two or three percentages you could use something hideous like this, it does the trick though.

=IF(AND(MID(A1,(FIND(".",A1)+3),3)>MID(A2,(FIND(".",A2)+3),3),MID(A1,(FIND(".",A1)+3),3)>MID(A3,(FIND(".",A3)+3),3)),ROUND(A1,2)+0.01,ROUND(A1,2))

=IF(AND(MID(A2,(FIND(".",A2)+3),3)>MID(A1,(FIND(".",A1)+3),3),MID(A2,(FIND(".",A2)+3),3)>MID(A3,(FIND(".",A3)+3),3)),ROUND(A2,2)+0.01,ROUND(A2,2))

=IF(AND(MID(A3,(FIND(".",A3)+3),3)>MID(A1,(FIND(".",A1)+3),3),MID(A3,(FIND(".",A3)+3),3)>MID(A2,(FIND(".",A2)+3),3)),ROUND(A3,2)+0.01,ROUND(A3,2))

I wouldn't normally post something like this, but it took me so long I just had to.

Replies
3
Views
113
Replies
10
Views
478
Replies
5
Views
89
Replies
8
Views
222
Replies
8
Views
220

1,195,749
Messages
6,011,437
Members
441,614
Latest member
TiaGtz

### 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.

### Which adblocker are you using?

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

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