Thread: Allocation based on Rank

Hi,

I need to allocate a resource based on ranking of records in a spreadsheet:

Product Limit Rank Allocation
200-25 40 9
200-27 50 9
200-30 60 4 60
200-33 45 3 45
200-35 32 5 10
200-37 11 6
200-40 20 11
200-43 10 11
200-46 80 2 80
200-50 70 6
200-52 23 6
200-55 15 1 15
210

This is what I need it to look like - an allocation of 210 between the highest ranked products. The last one that gets the resource is product 200-35 (and it only gets 10 because there is no more left to allocate). How can I do this through formula?

TIA.

Welcome to the forum.

Like this?

       ---A--- --B-- -C-- ----D----- ------------------E-------------------
   1   Product Limit Rank Allocation                                       
   2   200-55     15    1         15 D2 and down: =MIN(B2, 210-SUM(D$1:D1))
   3   200-46     80    2         80                                       
   4   200-33     45    3         45                                       
   5   200-30     60    4         60                                       
   6   200-35     32    5         10                                       
   7   200-37     11    6          0                                       
   8   200-50     70    6          0                                       
   9   200-52     23    6          0                                       
  10   200-25     40    9          0                                       
  11   200-27     50    9          0                                       
  12   200-40     20   11          0                                       
  13   200-43     10   11          0

D2 and down,

=MAX(0, MIN(B2, 210-SUMIF($C$2:$C$13, "<" & C2, $B$2:$B$13)))

Great -- would you please mark the thread as Solved?