I have no idea how this would work as a function, or if it's even possible as a function. Perhaps it would only be solvable as a macro.
Anyway... Here's my problem
(this is kind of complex but I'll do my best to explain)
I'm trying to prioritize a column of a calculated score, and put them into a "priority bucket", where the priority values are 1-10.
So... Priority 1 is a bucket, priority 2 is a bucket, and so on...
So this is what I want to solve:
Score Priority 5531.2 1 3798.7 2 2974.6 3 981.5 4 872.4 4 785.2 5 636.1 5 436.2 6 230.9 6 218.1 7 186.9 7 124.4 8 82.6 8 74.0 9 38.1 9 15.7 10 7.8 10
So in this table I am wanting to sort the highest score with the highest priority bucket, which is 1. The lowest score receives the lowest priority bucket, which is 10.
The complication lies in a few things.
Priority is never more or less than 1-10. So when we have more scores than 10, like this case; the priority bucket is added to from the lowest first. Which is where there's 2 of every priority, except for priority buckets 1, 2, and 3.
So if we were to add another score to this list; the priorities would move respectively and add an addition to priority bucket 3 (since it's the next in line of the priority buckets which hasn't been doubled up yet).
Again in the same scenario, if we add 4 more scores. We'd have 2 of every priority bucket, except for 3 in priority bucket 10. (I hope I haven't lost you yet)...
And now I'm going to add another complication to this. A duplicated score, will receive the same priority for what gets calculated for its counterpart.
So in our example, if we add another 7.8, it would receive a priority 10. If we add another 2974.6, it would receive a priority of 3.
I'm trying to figure out how to make this work, while making sure that the list of scores can grow indefinitely.
I totally understand that this might be complicated and not worth your time of trying to figure out.
I would appreciate any help or direction. Or if you're an excel guru and looking for a possible challenge; feel free to give it a try.
Let me know if there's any other information that could help.
Thanks
-Joel
Bookmarks