Rounding Multiple Numbers

  • Thread starter Thread starter Adrian54
  • Start date Start date
A

Adrian54

I have 4 percentages which together = 100%. They change and can includ
long decimals. I want to produce a rounded version that still equal
100%. However when I use ROUND, ROUNDUP, CEILING,MULTIPLE etc. I do no
always get the right results.

Example 1): Source 33.3333%, 33.33333%, 33.33333%,0% = 100%. Rounde
result 33%, 33%, 33%, 0% = 99%.
Example 2) Source 24.5%, 24.5%, 25.5%, 25.5% = 100%. Rounded resul
25%, 25%, 26%, 26% = 102%

Does anyone know a solution to this please?

Adria
 
Just use whichever one you want on the first 3 values and then make the 4th
value = 100% - (Val 1 + Val 2 + Val 3)
 
Hi Adrian54!

Rather than round the numbers in the column or row, why not format
them for no decimal places. Then your total should equal 100%

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thank you all for your help.

With regards the web site it concludes that there is no easy answer, s
may be I am just going to have to live with it.

With regards displaying in format without decimal places this produce
the same issues (e.g. 24.5, 24.5, 25.5, 25.5 is formatted to 25, 25
26, 26. which equals 102% when someone types these percentages i
elswhere)

With regards making the 4th value equal to 100% minus the first thre
values; that was the path I was on but I wanted to make the larges
value rounded as rounding a small percentage by 1% or 2% would have
big (undesirable) impact. Each time the model is used it could be
different cell that is the largest of the values. Here I hit on usin
=IF (MAX ...) but in the example above both Val 3 and Val 4 equal MA
which leads to both figures adding or reducing
 
Hi
one way to solve your problem could be the following array entered
formula (CTRL+SHIFT+ENTER)
=IF(A1+ROW(A1)/10000=MAX($A$1:$A$10+ROW($A$1:$A$10)/10000),"value to
roundup","do nothing")
copy this down for your range
 
Back
Top