Cash by priority

[Perry Kew]

I have the following spreadsheet of items that are by
priority. If I had $20, I could allocate all money to
priority 1, if I had $40 I could allocate it to two items
and $60 would make it three items etc. If I had 45, I
could get two items and allocate $5 to the third.

The spreadsheet is set up this way:

Item Amt Requested Amt Allocated
Item 1 $20
Item 2 $20
Item 3 $20
Item 4 $20
Item 5 $20

Is there a way to get the Amt Allocated (in col C) by a
formula? If $45 was available, Items 1 and 2 would get 20,
Item 3 would get 5, and items 4 and 5 would be 0.

Thanks.

--Perry

 

[Frank Kabel]

Hi Perry
try the following in column C (assumption D1 stores the amount you have
to allocate):
- in C1 enter
=MIN(B1,$D$1)

- in C2 enter
=MIN(B2,$D$1-SUM($B$1:B1))
and copy down