+ Reply to Thread
Results 1 to 5 of 5

Rounding by a percent split

  1. #1
    Registered User
    Join Date
    06-29-2018
    Location
    Maidstone, England
    MS-Off Ver
    365
    Posts
    10

    Rounding by a percent split

    I need to round a number by a percentage split. but I need it to be a whole number and also add back up to the original number.

    In the attached example, I am trying to split 160 by the percent Split Column (these percentages will change but will always add up to 100%)
    I have shown 4 examples of the rounding available in Excel.

    First one is with no rounding at all. All adds back up to 160. But I need it to round
    Second one is the ROUND function. Adds up to 159
    Third is the ROUNDUP function. Adds up to 164
    And Fourth is ROUNDDOWN. Adds up to 158

    What is the best rounding to get it back to its original figure?
    Attached Files Attached Files
    Last edited by SiGill1979; 01-27-2021 at 02:22 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,500

    Re: Rounding by a percent split

    By definition (Rounding to the nearest x or 0.1) is going to give you different numbers and thus a potentially different total.
    What I would do, is take your largest percent (52) and instead (in your example), in D8
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The result is actually 52.5% but close enough for government specs.

    Reminds me of the true story of when (Feb 6, 1897) the legislature of Indiana tried to legally change the value of Pi to 3.2. Really, wanted to make it 3.2 officially, not just for business purposes, easy to remember, no more pesky infinite numbers. What's the harm? LOL
    Last edited by ChemistB; 01-27-2021 at 02:05 PM.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    06-29-2018
    Location
    Maidstone, England
    MS-Off Ver
    365
    Posts
    10

    Re: Rounding by a percent split

    Thanks for that ChemistB Only problem is the highest % could change location. Lets say, for example, that 52% represents Thomas, and the 2% represents Mark. In another report Mark could possibly have the highest percent. I like your thinking and have produced this formula. Which looks for the highest % and minuses from the sum of the other percents. Which works well, until I get 2 percents the same? Any thoughts?

    =IF(B3=MAX($B$3:$B$9),C$2-SUMIF($B$3:$B$9,"<>"&MAX($B$3:$B$9),$G$3:$G$9),ROUND(C$2*B3,0))
    New Example attached

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,500

    Re: Rounding by a percent split

    There may be better ways but here's what I did.
    1. I inserted a blank row 10 between your values and totals (then made the row height small, you can hide instead)
    2. In G3 copied down to G9
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,774

    Re: Rounding by a percent split

    Please try this at H3

    =ROUND(B3*$C$2,0)+(MATCH(1,FREQUENCY(0,1/(ABS($B$3:$B$9*$C$2-ROUND($B$3:$B$9*$C$2,0))*10^6+$B$3:$B$9)),)=ROWS(H$3:H3))*($C$2-SUM(ROUND($B$3:$B$9*$C$2,0)))
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] auto percent split and dollar value results
    By jitterbug888 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-23-2020, 02:51 AM
  2. [SOLVED] Commission Percent based on Gross Margin Percent Formula
    By canselmi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-15-2019, 07:05 PM
  3. Commission Percent based on Gross Margin Percent Formula
    By canselmi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-15-2019, 01:10 PM
  4. [SOLVED] How to Calculate Percent Split of Total Value Separated by Two Sets of Criteria
    By garrett.grillo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-29-2015, 04:06 PM
  5. Formula or function to find percent of a percent in a population
    By maldron in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-25-2013, 08:26 PM
  6. Rounding Errors from Percent to Numbers
    By akh2103@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-15-2006, 09:55 AM
  7. [SOLVED] Formula for rounding to the nearst eighth of one percent
    By Ron Green in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-07-2005, 04:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1