3 Replies Latest reply on Aug 6, 2009 12:09 PM by RickWhitelaw






      I am trying to round up to the nearest whole number.  The only thing that I have found in my searches is:


      "To always round up to the next nearest integer, the calculation formula would be:

      Round (Number + .499999, 0)"


      But if I were to add .499999 to 1.8, instead of rounding up to 2 the formula would force the calculated number above 2 making it round up to 3.  How do I get around this?

        • 1. Re: Rounding

          Howdy JBizkit,


          Keep your "real" numbers in one field (or set of fields as appropriate), and your rounded result in another.  Only round the result, not the raw data.  This will avoid the 'compounded rounding'.


          PS. This holds true for XL, JMP, minitab and any other spreadsheet as well.  Keep the raw data raw, cook the books somewhere else.


          Enjoy the day!

          • 2. Re: Rounding

            Which version are you using? Don't you have the Ceiling() function?



            JBizkit wrote:
            if I were to add .499999 to 1.8, instead of rounding up to 2 the formula would force the calculated number above 2 making it round up to 3.

            If you were to add .499999 to 1.8, you'd get 2.299999, and rounding it to the nearest integer would return 2. However, that doesn't mean it's a good method (it isn't - try it with 0.0000001).

            • 3. Re: Rounding

              Rounding takes place depending on needs. I have an example. My local union has a 3% work dues rate on all work which is deducted from a worker's fee and remitted to the local. If the total fees for an engagement are $31,000.00, the first thought is simple: the total work dues amount is 31000.00 * .03 or 930.00, but the amount contributed by each worker is accounted for separately by the union and attributed to his/her account. If someone's base fee is $1467.95, 3% comes to $44.0385, an amount not manageable without rounding. So, the grand total of work dues is calculated on the SUM of rounded individual contributions which is not always the same figure as Total Fees * .03. I've found that with distributed amounts calculated in percentage rates, rounding as early as possible works best. It also avoids check stubs with anomalies like "Fee=1300.00, Tax= 39.00, Grand Total = 1339.01" (or 1338.99). Of course, this rounding is the only rounding necessary regardless of the number of further "non percentage" calculations performed. The Sum never needs rounding. The very principle of rounding involves "playing the odds" to a certain extent, but even on huge sums, the differences produced by various rounding schemes would be considered "insignificant variance" by accounting standards.


              My 2 cents, (and "preaching to the choir" I'm sure)