Rounding percentages in calculations

[CVC]

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

 

[Domski]

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

 

[Mike Blackman]

Hi,

How many percentages are you working with?

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

 

[Domski]

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]

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]

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.