Ranking a value in a "priority bucket" 1-10

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

mrice this is awesome, thank you so much. It works great. Took me a minute to figure out, but I got it.

Is there a snippet I can add into the defined function that will let me increase the range indefinitely? So if it's reading a blank cell, it doesn't read that as a part of the priority list?

If not that's ok. This is still pretty fantastic.

Thanks again.

Hey mrice. Thanks for the addition. I noticed something on it that I don't understand.
I'm trying to use this module across different sheets. But the results I'm getting are quite different than the first sheet. The first sheet I used it on (which is a master sheet), worked great. Did everything just right.
But on 3 other sheets (I've taken the data from the master sheet and separated them into other sheets based on an identifier for the scores), it changes the priority process.
One sheet only counts to 8, another sheet starts the priority at 4. I'm not sure what's causing the problem.
It's not matching the data to what's in the master sheet, so I'm not sure.

Again I really appreciate your help. If you feel you've devoted enough time into this already, I understand. Don't want to be a beggar or a bother.

Cheers.

SCORE_CALCULATION.xlsm

Attached. The first 4 sheets are the collective information of what I'm pulling from a SQL DB; then separating them into the IDs. The rest of the sheets is mostly the raw data from the DB.

Thanks
-PJ