# Scoring (Ranking?) items that fall within a number of 39 parameters

1. ## Scoring (Ranking?) items that fall within a number of 39 parameters

Hello Excel Forum; this is my first post.

I need help building a formula.

I have seen lots of Ranking help in the past forum topics (thanks) but couldn't find exactly what I was after and made an utter hash of trying to amend the previous responses to be what I wanted, so:

In relatively plain English, my goal is apply a score/grade/rank to a list of circa 100 items depending on how much the value of each item is when put into circa 40 'buckets' of from£-to£ values. So if an item costs £4500 it will fall into a bucket for items with value £4260 to £4750. The cheapest bucket has parameters of £0k up to £3250 so any items falling into this bucket will have a score of 1 (regardless of how many items fall into that bucket); the next cheapest bucket has parameters of £3260 to £3750 and any items falling into that bucket will have a score of 2. The next cheapest bucket has a score of 3 and so on. Even if there are no items which fall into a bucket I still want that bucket to have score/grade/rank assigned to it so that if any Items fall into bucket 4 they will still get a score/grade/rank of 4 even if there were no Items in bucket 3.

Same question in slightly more technical language:
Goal: to apply a sequentially numbered "Score" for each "Item" based on the "Value £k" of any Item falling into the Parameters between "From £k" to "To£k" rising from the cheapest to the most expensive.
Notes A: the lowest "Score" is 1 which equates to the "Item" (or Items) falling to lowest Parameter (i.e. the cheapest); the highest "Score" number equates tom the "Item" (or Items) falling into the highest Parameter (i.e. the most expensive).
Notes B: where more than one "Item" falls into the same Parameter as another "Item" then those "Items" should have the same "Score" and the next "Score" should increase sequentially and not skip any numbers.
Notes C: where no "Item" falls into a bucket that bucket must still have a "Score" associated with it and any "Items" will only have a "Score" 1 digit sequentially lower or higher than it's neighbouring Parameter, so any "Item" falling into the lowest Parameter gets a "Score" of 1, if there are no "Items" in the second lowest Parameter, then no "Item" gets a "Score" of 2, any items falling into the third cheapest Parameter get a "Score" of 3.

Below are two tables:
The first table below with two columns contains the Items and the Values
The second table below with three columns contains the Parameters of each bucket and the Score attached to each bucket.

Item Value £k
A £4.5
B £4.5
C £6.0
D £6.0
E £3.0
F £5.0
G £4.0
H £3.0
I £3.0
J £3.0
K £6.0
L £5.0
M £2.9
N £5.0
O £6.0
P £4.0
Q £6.0
R £6.0
S £4.5
T £6.0
U £5.0
V £9.5
W £4.5
X £9.0
Y £8.8
Z £7.5
AA £3.0
BB £8.0
CC £2.8
DD £15.0
EE £5.2
FF £15.0
GG £4.8
HH £10.5
II £7.2
JJ £6.0
LL £4.5
MM £4.7
NN £8.0
OO £9.0
PP £7.2
QQ £7.5
RR £5.0
SS £7.5
TT £9.0
UU £17.0
VV £5.0
WW £5.0
XX £7.5
YY £5.0
ZZ £5.8
AAA £3.0
BBB £9.0
CCC £9.5
DDD £4.5
EEE £6.0
FFF £6.0
GGG £6.0
HHH £3.3
III £3.5
JJJ £4.2
KKK £10.0
LLL £7.0
MMM £6.5
NNN £5.0
OOO £3.4
PPP £13.5
QQQ £4.7
RRR £9.6
SSS £6.5
TTT £8.0
UUU £6.2
VVV £7.5
WWW £11.0
XXX £13.0
YYY £8.2
ZZZ £12.0
AAAA £6.5
BBBB £7.0
CCCC £5.2
DDDD £9.0
EEEE £5.2
FFFF £8.0
GGGG £6.0
HHHH £6.0
IIII £5.6
JJJJ £11.0
KKKK £6.0
LLLL £7.0
MMMM £13.0

Parameter
From £k To £k Score
£- £3.25 1
£3.26 £3.75 2
£3.76 £4.25 3
£4.26 £4.75 4
£4.76 £5.25 5
£5.26 £5.75 6
£5.76 £6.25 7
£6.26 £6.75 8
£6.76 £7.25 9
£7.26 £7.75 10
£7.76 £8.25 11
£8.26 £8.75 12
£8.76 £9.25 13
£9.26 £9.75 14
£9.76 £10.25 15
£10.26 £10.75 16
£10.76 £11.25 17
£11.26 £11.75 18
£11.76 £12.25 19
£12.26 £12.75 20
£12.76 £13.25 21
£13.26 £13.75 22
£13.76 £14.25 23
£14.26 £14.75 24
£14.76 £15.25 25
£15.26 £15.75 26
£15.76 £16.25 27
£16.26 £16.75 28
£16.76 £17.25 29
£17.26 £17.75 30
£17.76 £18.25 31
£18.26 £18.75 32
£18.76 £19.25 33
£19.26 £19.75 34
£19.76 £20.25 35
£20.26 £20.75 36
£20.76 £21.25 37
£21.26 £21.75 38
£21.76 £22.25 39

What formula can do what I want, or do I need to go through two or more stages?

The spreadsheet the above comes from contains circa 50 columns and over 100 rows, so the above forms a relatively small part of the overall work. I could do it manually, but that would be cheating!

Grateful for any help

2. ## Re: Scoring (Ranking?) items that fall within a number of 39 parameters

PS - as you can probably see, I can't get the above two tables tabulated properly and whilst I can form a gridlined table I don't know how to get my data into the table once formed.

3. ## Re: Scoring (Ranking?) items that fall within a number of 39 parameters

Hope this helps

In C2 enter =IF(B2="","",INDEX(F:F,MATCH(B2,E:E,1))) copy down to match data

E1 enter the lowest value in each bucket ie 0,3.26,3.76,4.26 etc
F1 enter 1,2,3,4 etc
May need to add one more for any value more than 22.16 (instead of 40 put "Out of range" or similar.
Use COUNTIF to count individual bucket totals.

4. ## Re: Scoring (Ranking?) items that fall within a number of 39 parameters

Brilliant! Thank you very much indeed; problem resolved!

There are currently 1 users browsing this thread. (0 members and 1 guests)