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
Bookmarks