Force several numbers to equal 100% (Excel 2003)

Topic

New Reply

Is there a formula I can use to force 2 numbers to come out as whole numbers so they equal 100%? For instance, 23.5 and 76.5 and make them formatted with no decimals places it comes out as 24 and 77 which add up to 101. I need them to add up to 100 so either 23.5 has to be 23 or 76.5 has to be 76. Not sure how to do this. Thanks

Reply | Quote

Replies

Hi jha,

Whilst I’m sure there’s a way to do it using an IF Test combined with ROUNDUP & ROUNDDOWN, the problem remains as to which values should be rounded up or down. Suppose you’ve got two numbers: 49.5 and 50.5. Rounding both to 50 gives rather a different view of their relative weights than rounding to 49 and 51, respectively, and in some contexts that’s important. A common way of averaging out the rounding errors in the financial arena is to use a ROUNDEVEN function (Excel has one).

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

A simple way is to round one of them as desired and then use 100- the first number to give the second number.

Steve

Reply | Quote