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!
Bookmarks