Thread: Force several numbers to equal 100% (Excel 2003)

1. Force several numbers to equal 100% (Excel 2003)

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

2. Re: Force several numbers to equal 100% (Excel 2003)

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

3. Re: Force several numbers to equal 100% (Excel 2003)

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

Steve

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•