formulas / rounding

[JT]

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.........

 

[soxcpa]

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.

 

[Bob Phillips]

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)

 

[joeu2004]

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.
[....]
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!")