Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas / rounding
Were preparing a report and the detail commission values are rounded to two
decimals. I also calculate commission as Total Revenue x 5% (rounded to two decimals). The problem is the sum of the rounded detail values does not equal the Total Revenue x 5% value. For example: Total Revenue = $117,524.11 and the Commission = $5,876.21 (5%). However, when multiplying the row-level detail by 5% and rounding to two places and then summing the column I get $5,876.71, a $0.50 increase. Is there anyway to add the rounded values at the detail level so that it equals the Total Revenue x 5% or am I trying to mix apples and organes? Thanks for the help......... -- JT |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas / rounding
As soon as you start rounding you purposely enter a level of error into
the spreadsheet. Essentially, you are telling Excel you are comfortable with an error as long as it is less that a certain amount. Adding numerous errors which, by themselves, are immaterial could aggregate to a material error, but this would be statistically improbable. For every number, there is a statistically equal chance of rounding up or down. By the same token, it is statistically improbable that for any given number of roundings exactly half rounded up and half rounded down. (Which is what must occur for the sum of the rounded detail to match the rounded total.) Basically, you have to decide if the 50 cents is important enough to concern your spreadsheet users. My experience tells me someone always catches the 50 cents and wants to use it to cast doubts on the veracity of the spreadsheet as a whole. For this reason, I would note the item as '$.50 difference due to rounding' in a comment to small text box and carry on. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas / rounding
You could calculate the total commission using the revenue details
=SUMPRODUCT(rev_range*5%) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "JT" wrote in message ... We're preparing a report and the detail commission values are rounded to two decimals. I also calculate commission as Total Revenue x 5% (rounded to two decimals). The problem is the sum of the rounded detail values does not equal the Total Revenue x 5% value. For example: Total Revenue = $117,524.11 and the Commission = $5,876.21 (5%). However, when multiplying the row-level detail by 5% and rounding to two places and then summing the column I get $5,876.71, a $0.50 increase. Is there anyway to add the rounded values at the detail level so that it equals the Total Revenue x 5% or am I trying to mix apples and organes? Thanks for the help......... -- JT |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas / rounding
"JT" wrote:
Were preparing a report and the detail commission values are rounded to two decimals. I also calculate commission as Total Revenue x 5% (rounded to two decimals). The problem is the sum of the rounded detail values does not equal the Total Revenue x 5% value. [....] Is there anyway to add the rounded values at the detail level so that it equals the Total Revenue x 5% or am I trying to mix apples and organes? The latter, sort of. My first question is: why are you computing 5% times Total Revenue instead of simply using the sum of the "detail commission values" (presumably 5% times allocated revenue)? I hasten to point out that if you actually pay the "detail commission value", then for accounting purposes, the total commission paid is indeed the sum of the "detail commission value", not 5% times Total Revenue. However, if you still want to pursue these dual computation with impunity, you might consider doing the following. Instead of explicitly rounding each "detail commission value", simply format the cell with 2 decimal places. Thus, the rounded "detail value" will appear in the spreadsheet, but because the cell will contain a more exact value, the sum of the cells should equal 5% times Total Revenue, at least to 2 decimal places. Personally, that would not be my choice. But it might satisfy your needs. Some caveats .... First, a sharp reader might discover that the sum of the formatted cells does not equal the sum computed in the spreadsheet. On the other hand, this is such a common situation that no one really should be surprised -- or at least, they should readily accept the explanation. Second, if you actually tried to compare the two results in an Excel formula, it probably will fail. That is: =if(SUM(detailCells) = 5% * totalRevenue, "okay", "error!") will probably result in "error!" due to the way that computers do binary arithmetic. On the other hand, I would expect the following to work (result in "okay"): =if(ROUND(SUM(detailCells),2) = ROUND(5% * totalRevenue,2), "okay", "error!") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to minimize rounding error with complicated formulas | Excel Discussion (Misc queries) | |||
Rounding Formulas | Excel Discussion (Misc queries) | |||
rounding a formula's number | Excel Worksheet Functions | |||
formulas for rounding | Excel Discussion (Misc queries) | |||
specific rounding of formulas which may need to include an IF stat | Excel Worksheet Functions |