Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    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. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

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

    Paul Edstein
    [MS MVP - Word]

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •