MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Issue


Posted by Ranjan Kumar on February 04, 2002 9:05 AM

Hi,

I have developed a spreadsheet to do a task of Amount Allocation in different segments based on percentage of amount invested.

First Cell: Amount

Row1:
Second Cell: Percent
Third Cell: Amount Invested (which is equal to Amount * Percent/100)

Row2:
Second Cell: Percent
Third Cell: Amount Invested (which is equal to Amount * Percent/100)

and so on....

I'm not sure if this can be done but can it allocate to the penny in the third column??

Here are some examples....
Using 12263.49 with 4 allocations of 25% one allocation should be 3065.88 the rest s/b 3065.87

Or using 5745.49 50/50 split

Thanks,

Ranjan.


Posted by Yogi Anand on February 04, 2002 12:51 PM

Hi Ranjan:
The way to approach this would be use the ROUND function ... as depicted below, for three allocations, I used the ROUND function, and then for the 4th allocation, I deducted the sum of 3 allocations from the total amount

Amount Percent AmountInvested
12263.49 25 3065.8725 3065.87 <----- =ROUND(C29,2)
25 3065.8725 3065.87 <----- =ROUND(C29,2)
25 3065.8725 3065.87 <----- =ROUND(C29,2)
25 3065.8725 3065.88 <----- Amount-sum_of_3_allocations above

HTH

Yogi Anand
ANAND Enterprises (broken link)