Allocation based on Rank

[ValGlad]

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.

[shg]

Welcome to the forum.

Like this?

Please Login or Register  to view this content.

[ValGlad]

Sorry, forgot to mention - ranking can not be sorted.

[shg]

D2 and down,

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

[ValGlad]

Shg - thanks very much! Problem's solved.

[shg]

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