Help with a maths question in Microsoft Excel please, with rounding numbers?

Question

I have a list of numbers that add to 100: (16.55, 55.68, 27.77).

When I round these numbers using ROUND(), I get (17, 56, 28), which add to 101.

When I truncate these numbers using TRUNC(), I get (16, 55, 27), which add to 98.

My question is, is there some function I can use such that rounds the numbers so that the total is still 100? In this case, I would expect the result to be (16, 56, 28), the last two being rounded up because they are closer to the next higher integer than the first number is. Of course I would want the function to work with any group of numbers that add to 100.

Thanks in advance for your help.

If you don't know the actual Excel function to use, perhaps you could help me out by giving the mathematical name for what I'm trying to do :-)

Answer 1

I don't think there is any simple function or name for this. Here's what I would do:
Column 1: the original numbers
Column 2: column 1 plus X, the magic number
... put cell with X at top of column 2
Column 3: column 2 rounded, sum at bottom

Then run goal seek or solver, to make the sum = 100 by changing X.

For this example, I get X = -0.08, but other values will work also.

This idea will not work if, for example, too many of the numbers have the same decimal part (like a bunch of 0.55's).

Answer 2

First, make a decision what number of decimal location you desire displayed. I suppose that Excel will show as much as 30 decimal areas in Office 2002. Select the variety you wish to difference and click on Format, Cells..., Number tab, Number. Now you've gotten the choice of atmosphere the quantity of decimal areas. Then click on OK. Excel exhibits numbers mechanically rounded off to the quantity of decimal areas special.

Answer 3

I like the goal seek solution, it gets you closest to what you're looking for.

Another option is to always round normally, except for the last number which would always be = 100 - sum(all the others).

It will usually be within 1 or 2 of its true rounded value.

Instead of the last one, it may be easier to have the first one be 100-sum(all the others), since you won't have to change the formula every time.

Answer 4

Rounding produces errors. That's the nature of it.

There is no function of individual numbers that always produces an integer and always has the result add up to the original sum. Consider (12.5, 12.5 12.5, 12.5 12.5, 12.5 12.5, 12.5). No matter what ALTERNATEROUND(12.5) is, if it's an integer it won't add up to 100 when added to itself eight times. To make it always add up the same, you would have to keep track of the round-off error, and the whole point of rounding is that you don't have to keep track of spurious information.

However, rounding randomly can produce results that have the expected value of the sum of the rounded numbers is the original sum. Just use the non-integer part as your probability to decide whether to go up or down.

Answer 5

Apply the rounding to the sum, not the individual numbers

Why would you expect 55.68 to be rounded down, but 27.77 to be rounded up?

There is no Excel function that can read your mind. And there is no defined mathematical function to do what you want.

At what point should the function decide to round down or round up? You have to define that first. Then you can write your own function to do it for you. It's easy to do, but you have to define the rules first.