I need to be able to assign a 'priority rating' to a list based on score value

[Westie102]

Hi everyone,
I looking for some help again.

My table in excel has a number of entries to make up a scoring matrix. Let's say it has 100 rows (but growing), with a total score value from 0 (no data entered) to 24 (current highest possible score).

I want to effectively take the number of rows, and then split to 4 'sections' - let's say 25% each for simplicity. The highest quartile would be Priority 1, down to the lowest quartile being 'priority 4'

So I want to be able to work out what quartile each total score fits into from the overall number of rows with values (not the ones not yet scored - so have a "0" value) which may be more or less than 100 rows.
Then based on the quartile each one falls into, display "priority 1 though to 4"

I'll try an mock up a sanitised dataset I can share here.

I've managed to write the formula to work out the quartile, and I can get it to display the "priority x" result I want but not for all 4 and not a different message if it's displaying "0". So I think I'm on the right track, but it's a bit outside my skill level, so hoping to find some help here

Many thanks, everyone
Nicky

 

[Eric W]

Try:

Book1 (version 1).xlsb
	A	B	C
1	Entry	Score
2	1	24	Priority 1
3	2	7	Priority 4
4	3	20	Priority 1
5	4	14	Priority 2
6	5	15	Priority 2
7	6		No score
8	7	15	Priority 2
9	8	13	Priority 3
10	9	10	Priority 3
Sheet9

Cell Formulas
Range	Formula
C2:C10	C2	=IF(B2="","No score","Priority "&LOOKUP(B2,QUARTILE.INC($B$2:$B$101,{0,1,2,3}),{4,3,2,1}))

I set a range of B2:B100. You can set the range to B2:B1000 if you want, the Quartile function ignores empty cells. Or with a little more work, we can figure out a dynamically changing range. Let us know how this works.

 

[Westie102]

Hi Eric,

Thank-you so much! That is brilliant, and does work

May I impose just a little more on your helpfulness once more, please?

My fault - I didn't get my logic quite right. Is it possible to somehow increase the variance, or perhaps quartiles was not the right way of thinking...
Using the quartiles I get a lot of Priority 2 and 3 but not many 1's and 4's... I need to even that up a bit more if possible.

So, I think, using an old fashioned calculator I'm looking at Priority 1 being in the range of 14-24 (My Max), P2 11-13, P3 6-10 and P4 1-5. I naively assumed quartiles would be the right function - but I think I might have got that wrong!

There is a possibility the scoring system might change in the future, so I was hoping to avoid fixed values, but perhaps that's the only way?

Many thanks again Eric, I really appreciate your help

Nicky

 

[Eric W]

Hmm, kind of odd you're getting that distribution. Consider:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Entry	Score	Formula1	Formula2	Formula3
2	1	24	Priority 1	Priority 1	Priority 1	TRUE	TRUE	TRUE		Priority 1	25	51	25
3	2	7	Priority 4	Priority 3	Priority 4	FALSE	TRUE	FALSE		Priority 2	26	14	26
4	3	20	Priority 1	Priority 1	Priority 1	TRUE	TRUE	TRUE		Priority 3	22	11	23
5	4	14	Priority 2	Priority 1	Priority 2	FALSE	TRUE	FALSE		Priority 4	25	21	24
6	5	15	Priority 2	Priority 1	Priority 2	FALSE	TRUE	FALSE
7	6		No score	No score	No score	TRUE	TRUE	TRUE
8	7	15	Priority 2	Priority 1	Priority 2	FALSE	TRUE	FALSE
9	8	13	Priority 3	Priority 2	Priority 3	FALSE	TRUE	FALSE
10	9	10	Priority 3	Priority 3	Priority 3	TRUE	TRUE	TRUE
11	10	17	Priority 2	Priority 1	Priority 2	FALSE	TRUE	FALSE
Sheet9

Cell Formulas
Range	Formula
C2:C11	C2	=IF(B2="","No score","Priority "&LOOKUP(B2,QUARTILE.INC($B$2:$B$101,{0,1,2,3}),{4,3,2,1}))
D2:D11	D2	=IF(B2="","No score","Priority "&LOOKUP(B2,{1,6,11,14},{4,3,2,1}))
E2:E11	E2	=IF(B2="","No score","Priority "&LOOKUP(B2,SMALL($B$2:$B$101,INT(COUNT(B$2:B$101)/{10000,4,2,1.333})+1),{4,3,2,1}))
F2:F11	F2	=C2=D2
G2:G11	G2	=C2=E2
H2:H11	H2	=D2=E2
K2:M5	K2	=COUNTIF(C:C,$J2)

This isn't my whole sample range. C2 is my original formula, D2 is the formula with your particular ranges hardcoded, and E2 is an attempt to create a range using SMALL instead of QUARTILE. I then put formulas in to compare the results. On my test data, the C and E formulas returned almost the same results. And in the J:M table, you can see that each category for the C/E formulas has about 1/4 of the total. On the D formula, you should be able to see how to hardcode your ranges. Change the array constant {1,6,11,14} so it has the lower bound of each range you want.

So you can experiment with these formulas, and try to see how each works for your situation. Good luck!

 

[Westie102]

Hi Eric,

Thank-you so much! I have it working great now . Really appreciate your help, and I have learned a lot in the process

Best wishes
Nicky