+ Reply to Thread
Results 1 to 7 of 7

Eliminating rounding errors in a data set

  1. #1
    Registered User
    Join Date
    05-01-2020
    Location
    Massachusetts
    MS-Off Ver
    Excel for Mac 16.80 (Microsoft 365)
    Posts
    28

    Eliminating rounding errors in a data set

    I'm stumped and not certain that there is anyway to solve this problem in Excel natively. For reference, my issue is well described in this third party add-on's site, but that software costs $247 a year -- not worth it for a single spreadsheet I need to solve the problem with!! I can't directly post the link (forum rules) but you can find it if you Google "think cell data rounding".

    I am disseminating a number into multiple tranches by multiplying a number against various factors (once for each tranche). The total sum of the results should equal the original number. Because my numbers represent people, I need whole numbers, not fractional. These whole numbers will be applied against a sales price, so only whole numbers can be used.

    Rounding, including using any of the Excel rounding functions (ROUND, ROUNDDOWN, FLOOR, etc.) can produce totals that are either higher or lower than the original total. Outside of paying $247 for think-cell, I can't figure out how to get Excel to handle this.

    The attached file should clarify (hopefully!). In column A are non-rounded numbers. The TOTAL PEOPLE = STARTING NUMBER, but there are fractional people in each tranche. I can't have this. Column B and C get rid of the fractional people using ROUND() and ROUNDDOWN(), respectively, but TOTAL PEOPLE ≠ STARTING NUMBER, which it needs to. How to achieve both requirements?

    Lastly--I tried by making one of the tranches "absorb" the error -- but as my actual data will extend many months (years, in fact), this overloads the tranche and creates unrealistic data. The tranches essentially need to take turns absorbing the error, if there is one. This is what think-cell does, but again, I can't spend $247 for a single spreadsheet.

    THANK YOU!
    Attached Files Attached Files
    Last edited by seh0872; 05-01-2020 at 01:50 PM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Eliminating rounding errors in a data set

    I don't understand the algorithm you want to use (and have no idea what algorithm Think Cell uses) so I cannot recommend something specific. You seem to vaguely describe a "two stage" algorithm where you:

    1) Start with a given number of people (D4, E4, F4).
    2) Distribute "integer people" among the tranches (E7:E10, F7:F10)
    3) Compare the total number of people distributed with the starting value to make sure there are the same number of people (compare E12 and E4, F12 and F4).
    4a) If you end up with the same number of people, end.
    4b) If you end up with more/fewer people than you started with, figure out how many people over/under the starting value and adjust the values in the tranches accordingly. For example, E12 is 1 greater than E4, so you need to remove 1 person from one of the tranches. How will you choose which tranche to remove this person from? For example #2, F12 is 3 less than F4, so you need to add 3 people to the tranches. Do you add all 3 people to one tranche, add 1 person to 3 separate tranches, or some other choice? The part of the algorithm needs to be generic enough to handle all practical scenarios output from step 2.

    Is that a reasonable overview of the algorithm you have in mind? Can you fill in the details that I cannot? Once we have a better idea of the algorithm you want to use, then we can figure out the programming details in the spreadsheet.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Re: Eliminating rounding errors in a data set

    Try this:
    File
    Options
    Advanced
    When Calculating This Workbook
    place a checkmark: Set Precision As Displayed. (Ignore warning) Click OK.

    See if that works for you. Good luck.

    Pete

  4. #4
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Eliminating rounding errors in a data set

    Enter 110 in G4. If G6 is always empty, enter the following into G7 and copy into G8:G10:

    =ROUND(G$4*SUM($C$7:$C7)-SUM(G$6:G6), 0)

    If G6 is not always empty, you need to make a special case for G7, to wit: =ROUND(G4*$C7,0) .

    The results are 31, 17, 53 and 9, which are 28.18%, 15.45%, 48.18% and 8.18% of 110.

    Note that 17 is closer to 15% instead of 16%. That cannot be helped. Whenever you round, some will lose, and some will gain.

    There are other methods that seek to minimize the "mean square deviation". But the approach above might be sufficient for your purposes.
    Last edited by joeu2004; 05-01-2020 at 05:37 PM.

  5. #5
    Registered User
    Join Date
    05-01-2020
    Location
    Massachusetts
    MS-Off Ver
    Excel for Mac 16.80 (Microsoft 365)
    Posts
    28

    Re: Eliminating rounding errors in a data set

    Hey guys..I was going to reply to Mr. Shorty but wanted to spend a little more time analyzing my spreadsheet firt. joeu, your answer may do the trick. The actual spreadsheet has 60 columns of data, but I'm going to try to adapt your solution and see if it solves my issue, and if not, respond to Mr. Shorty's reply. Stay tuned...

  6. #6
    Registered User
    Join Date
    05-01-2020
    Location
    Massachusetts
    MS-Off Ver
    Excel for Mac 16.80 (Microsoft 365)
    Posts
    28

    Re: Eliminating rounding errors in a data set

    OK gents...

    Joeu2004's solution solved one of my rounding issues (thanks!), but now I'm on to the final (and more challenging) one.

    This is attrition data, where a certain percentage of existing customers stop being customers. Again, as people, they have to be whole people.

    In lay terms, its essentially a distribution problem. If I have 100 customers, and 10 leave, I have 90 left. That'a 10% attrition rate. But if those customers are now spread out among various tranches, it's harder to get the total to equal 90. see the attached simplified example.

    In "Starting Number" we have the number of customers (total = 100). 5% leave, so we will end up with 95 in the next period. They can't quite leave ratably from each tranche without leaving partial people, and the rounding function produces an error in that failed to remove 1 person (rounding to 96 instead of 95). I attempted to implement a version of joeu's solution for the other round issue previously mentioned, but I couldn't get it to work.

    Want to puzzle over this one?

    Thanks!
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Eliminating rounding errors in a data set

    In this new file, what happened to the "percent per tranche" numbers that were in C7:C10? I think joeu2004's solution depends on having those percentage per tranche values and that they must add up to 100%.

    The quick solution that I see is to go back to the original sample file and joeu2004's original formula, and replace the 110 that is entered into G4 and enter a formula in G4 that will calculate the attrition. =ROUND(110*0.95,0) or equivalent into G4, with the rest of the formulas as joeu2004 described in post #4.

    Would that work, or are you now required to perform the calculation without the percentage per tranche values as previously provided?

+ 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. How to Stop Rounding Errors?
    By funwithcolors in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-22-2014, 12:55 AM
  2. Dealing with Rounding Errors
    By stockgoblin42 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-29-2013, 01:23 AM
  3. Excel 2007 : rounding errors
    By meonly123 in forum Excel General
    Replies: 1
    Last Post: 09-24-2011, 08:07 PM
  4. Rounding errors
    By Divran in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-07-2010, 05:25 AM
  5. [SOLVED] Eliminating Errors Resulting from If Functions with Multiple Conditions
    By Stephanie in forum Tips and Tutorials
    Replies: 1
    Last Post: 08-25-2005, 03:43 AM
  6. Rounding Errors Help
    By mattflow in forum Excel General
    Replies: 2
    Last Post: 08-12-2005, 04:05 PM
  7. How Stop Rounding Errors?
    By Al Franz in forum Excel General
    Replies: 3
    Last Post: 01-30-2005, 12:07 AM

Tags for this Thread

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