Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JT
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to minimize rounding error with complicated formulas Erick T. Excel Discussion (Misc queries) 1 January 25th 06 03:05 PM
Rounding Formulas Chandra Excel Discussion (Misc queries) 5 July 5th 05 05:18 PM
rounding a formula's number [email protected] Excel Worksheet Functions 2 March 9th 05 08:33 PM
formulas for rounding Mas Excel Discussion (Misc queries) 1 February 10th 05 07:46 PM
specific rounding of formulas which may need to include an IF stat Gerry Wilkins Excel Worksheet Functions 2 January 5th 05 05:11 AM


All times are GMT +1. The time now is 11:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"