CODEWITHSUNDEEP
excelrangerank
Chane May
Chane May

Reputation: 13

Excel: Calculate allocation based on Rank

I have a data set with Cost Per Purchase, Volume of Purchase, Budget, Rank and Total Allocation.

I am trying to figure out how to allocate a portion of the Total Allocation based on rank - ideally, the lower the rank (1 is best) the higher the allocation. Is there a formula for this?

In addition, the range of the top 3-5 items (depending on the range between them), should account for 30% of allocation while the rest accounts for 70% (depending on the number of total items). Ideally, budget allocation should higher towards values with a lower cost per purchase - inverse of the current allocation.

https://i.sstatic.net/04Ony.png

Upvotes: 1

Views: 865

Answers (1)

Imran Malek
Imran Malek

Reputation: 1719

Here's my attempt , I'm not exactly sure if it fits the need but you can modify the same example for changes.

Basically you can find out the % of some value that you want to allocate

After that you need find out how many ranks fall under 30% like you've mentioned (3-5)

Lastly you can take sum of values for those top ranks and rests based on which you can find out the ratio of the allocation.

enter image description here

Yellow cells are just headers for the explanation

Orange cells are formula results

Grey cells are your predefined values

Upvotes: 1

Related Questions