Divide a # into 3 #'s rounded to a whole #
Errata.... I wrote:
"Ron Rosenfeld" wrote:
In general, you would compute n-1 of the numbers, then
subtract them from the total to get the nth number.
So if your formulas are in I6:I8, they might look like:
$I$6: =ROUND($H$35*H6,0)
$I$7: =ROUND($H$35*H7,0)
$I$8: =$H$35-SUM(I6:I7)
Although that does work for the example given, and it might work for any
__3__ percentages, it does not work "in general" for any n percentages.
[....]
A more reliable, but still simple approach is:
I6: =ROUND(H6*$H$35,0)
I7: =ROUND(SUM($H$6:H7)*$H$35-SUM($I$6:I6),0)
Copy I7 down through I12.
On second thought, neither approach is always reliable.
Consider the following example, which is intended to sum to 27:
data myRound ronsRound
0.02 0 0
0.54 1 1
1.39 1 1
4.60 5 5
7.74 7 8
11.44 12 11
1.27 1 1
Ron's result is more intuitive, IMHO.
|