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)