software

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

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

quot;JTquot; gt; wrote in message
...
gt; We're preparing a report and the detail commission values are rounded to
two
gt; decimals. I also calculate commission as Total Revenue x 5% (rounded to
two
gt; decimals). The problem is the sum of the rounded detail values does not
gt; equal the Total Revenue x 5% value.
gt;
gt; For example:
gt;
gt; Total Revenue = $117,524.11 and the Commission = $5,876.21 (5%).
gt;
gt; However, when multiplying the row-level detail by 5% and rounding to two
gt; places and then summing the column I get $5,876.71, a $0.50 increase.
gt;
gt; Is there anyway to add the rounded values at the detail level so that it
gt; equals the Total Revenue x 5% or am I trying to mix apples and organes?
gt;
gt; Thanks for the help.........
gt;
gt; --
gt; JT
quot;JTquot; wrote:
gt; We’re preparing a report and the detail commission values
gt; are rounded to two decimals. I also calculate commission
gt; as Total Revenue x 5% (rounded to two decimals). The
gt; problem is the sum of the rounded detail values does not
gt; equal the Total Revenue x 5% value.
gt; [....]
gt; Is there anyway to add the rounded values at the detail level
gt; so that it equals the Total Revenue x 5% or am I trying to mix
gt; 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 quot;detail commission
valuesquot; (presumably 5% times allocated revenue)?

I hasten to point out that if you actually pay the quot;detail commission
valuequot;, then for accounting purposes, the total commission paid is
indeed the sum of the quot;detail commission valuequot;, 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 quot;detail commission valuequot;, simply format
the cell with 2 decimal places. Thus, the rounded quot;detail valuequot;
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, quot;okayquot;, quot;error!quot;)

will probably result in quot;error!quot; due to the way that computers do
binary arithmetic. On the other hand, I would expect the following
to work (result in quot;okayquot;):

=if(ROUND(SUM(detailCells),2) = ROUND(5% * totalRevenue,2),
quot;okayquot;, quot;error!quot;)