
1. Re: Rounding
ninja Aug 6, 2009 5:44 AM (in response to JBizkit)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
comment_1 Aug 6, 2009 6:30 AM (in response to JBizkit)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
RickWhitelaw Aug 6, 2009 12:09 PM (in response to JBizkit)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)
Rick.