Divide a # into 3 #'s rounded to a whole #

[TreeHugger1]

I have a Cost that must be divided into 3 separate percentages that m
company can bill against. We don't like to input any budget numbers int
our accounting system that contain cents, so we round all of our number
up or down manually so that they may be added up to equal the origina
total.

So if I have a cost of $17, and I have to divide it into 3 numbers (15%
35%, & 50%), how can i create three separate formulas to automaticall
round the 3 numbers to the nearest whole dollar and still create a su
of $17?

Assume $17 = H35, 15% = H6, 35% = H7, & 50% = H8
I tried the following, which seems to not work for all total numbers:
=Rounddown(H35*H6,0)
=Roundup(H35*H7,0)
=Rounddown(H35*H8,0)

Any help would be appreciate

 

[Ron Rosenfeld]

I have a Cost that must be divided into 3 separate percentages that my
company can bill against. We don't like to input any budget numbers into
our accounting system that contain cents, so we round all of our numbers
up or down manually so that they may be added up to equal the original
total.

So if I have a cost of $17, and I have to divide it into 3 numbers (15%,
35%, & 50%), how can i create three separate formulas to automatically
round the 3 numbers to the nearest whole dollar and still create a sum
of $17?

Assume $17 = H35, 15% = H6, 35% = H7, & 50% = H8
I tried the following, which seems to not work for all total numbers:
=Rounddown(H35*H6,0)
=Roundup(H35*H7,0)
=Rounddown(H35*H8,0)

Any help would be appreciated

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)

 

[joeu2004]

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.

Consider an example with 15% in H6:H11 and 10% in H12. With a total of 17
in H35, the result in I6:I12 would be 3,3,3,3,3,3 (a subtotal of 18!)
and -1(!).

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.

The ROUND function in I12 should mask any arithmetic anomalies that arise
because SUM(H6:H12) might not be (usually is not) exactly 100%. But if you
prefer, the formula in I12 could be:

I12: =$H$35-SUM($I$6:I11)

The result in I6:I12 is 3,2,3,2,3,2,2, which does indeed sum to 17.

For TreeHugger's original example, the result is 3,6,8, which is the same as
Ron's approach.

-----

For an algorithm that claims to minimize absolute relative error, you might
look at Bernd Plumhoff's webpage at
http://www.sulprobil.com/html/largest_remainder.html.

But frankly, I usually find his descriptions confusing. And I have not been
able to download his Excel implementations for a long time. (Is a donation
__required__ now?) Moreover, many (all?) of the files now require Excel
2007 or later.

PS: And I coulda sworn his we

 

[CellShocked]

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.

Consider an example with 15% in H6:H11 and 10% in H12. With a total of 17
in H35, the result in I6:I12 would be 3,3,3,3,3,3 (a subtotal of 18!)
and -1(!).

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.

The ROUND function in I12 should mask any arithmetic anomalies that arise
because SUM(H6:H12) might not be (usually is not) exactly 100%. But if you
prefer, the formula in I12 could be:

I12: =$H$35-SUM($I$6:I11)

The result in I6:I12 is 3,2,3,2,3,2,2, which does indeed sum to 17.

For TreeHugger's original example, the result is 3,6,8, which is the same as
Ron's approach.

-----

For an algorithm that claims to minimize absolute relative error, you might
look at Bernd Plumhoff's webpage at
http://www.sulprobil.com/html/largest_remainder.html.

But frankly, I usually find his descriptions confusing. And I have not been
able to download his Excel implementations for a long time. (Is a donation
__required__ now?) Moreover, many (all?) of the files now require Excel
2007 or later.

PS: And I coulda sworn his we

Nice thread, guys. Just sayin'. Instructive... educational even.

 

[Paga Mike]

I have a Cost that must be divided into 3 separate percentages that m
company can bill against. We don't like to input any budget numbers int
our accounting system that contain cents, so we round all of our number
up or down manually so that they may be added up to equal the origina
total.

So if I have a cost of $17, and I have to divide it into 3 numbers (15%
35%, & 50%), how can i create three separate formulas to automaticall
round the 3 numbers to the nearest whole dollar and still create a su
of $17?

Assume $17 = H35, 15% = H6, 35% = H7, & 50% = H8
I tried the following, which seems to not work for all total numbers:
=Rounddown(H35*H6,0)
=Roundup(H35*H7,0)
=Rounddown(H35*H8,0)



Any help would be appreciated

With 17 in A1, In A2 thru A4, enter:

=ROUND(0.15*A1,0)
=ROUND(0.35*A1,0)
=A1-A2-A

 

[Ron Rosenfeld]

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.

Thank you for that observation. You are, of course, correct.

 

[TreeHugger1]

Wow, very easy. Thank you very much for the help.

With 17 in A1, In A2 thru A4, enter:

=ROUND(0.15*A1,0)
=ROUND(0.35*A1,0)
=A1-A2-A

 

[CellShocked]

Wow, very easy. Thank you very much for the help.

I hope you read the whole thread.

 

[TreeHugger1]

I did review everything. Thank you. The 1st response is easy t
understand. It took me a while to figure out the 2nd response. Thank yo
both!

 

[joeu2004]

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.