I need to allocate $100,000 over a range of ranked items. Additionally the lowest value needs to start at $500 and build from there. Any help would be appreciated. I am stumped.
Item Rank $ Total to allocate= $100,000
A 6 Starting amount=$500
B 3
C 1
D 1
E 7
F 4
G 5
See my proposition in the attached file
I'd start with formulad in column F to find lowest rank, count all ranked items, sum all the ranks above lowest so (as lowest is 1)
A 5
B 2
C 0
etc. which gives 20
the sum to be allocated to higher ranks is total_sum - 7*500
so per additional rank is 996500/20 = 49825
and now for each item we have
A (rank 6) so 5*49825+500
B (rank 3) so 2*49825+500
etc.
Whoops, I did it for 1M not 100K - easy, just change value to be allocated in attached spreadsheet
Best Regards,
Kaper
PS. If it would be my decisuion, I'd start from $3 700.00 for rank 1 and then for each additional position there would be $3 705.00, so for rank 3 $11 110.00 etc.
Thank you! That worked. I agree with your second suggestion however my issue had a much larger more complex group of data. I just needed a place to start and this was it.
Glad to hear that, and as that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks