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) = 2
=Roundup(H35*H7,0) = 6
=Rounddown(H35*H8,0) = 8
Sum of numbers = $16

I need the 3 formulas to work with any number that I input into H35. I may be changing the 3 %'s also in the future, so the formula would have to work with different %'s.

Any help would be appreciated

On Thu, 7 Feb 2013 00:19:53 +0000, TreeHugger1 wrote:


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)

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

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

On Wed, 6 Feb 2013 23:54:46 -0800, "joeu2004" wrote:

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.

On Wed, 6 Feb 2013 23:54:46 -0800, "joeu2004" wrote:

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.

On Thu, 7 Feb 2013 16:54:26 +0000, TreeHugger1
wrote:


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

Paga Mike;1609193 Wrote:
With 17 in A1, In A2 thru A4, enter:

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

I hope you read the whole thread.

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

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.