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

#### Westie102

##### New Member
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

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Eric W

##### MrExcel MVP
Try:

Book1 (version 1).xlsb
ABC
1EntryScore
2124Priority 1
327Priority 4
4320Priority 1
5414Priority 2
6515Priority 2
76No score
8715Priority 2
9813Priority 3
10910Priority 3
Sheet9
Cell Formulas
RangeFormula
C2:C10C2=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

##### New Member
Hi Eric,

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

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

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

Cell Formulas
RangeFormula
C2:C11C2=IF(B2="","No score","Priority "&LOOKUP(B2,QUARTILE.INC(\$B\$2:\$B\$101,{0,1,2,3}),{4,3,2,1}))
D2:D11D2=IF(B2="","No score","Priority "&LOOKUP(B2,{1,6,11,14},{4,3,2,1}))
E2:E11E2=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:F11F2=C2=D2
G2:G11G2=C2=E2
H2:H11H2=D2=E2
K2:M5K2=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

##### New Member
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

Replies
1
Views
284
Replies
9
Views
264
Replies
6
Views
222
Replies
9
Views
239
Replies
2
Views
226

1,132,912
Messages
5,655,912
Members
418,250
Latest member
Jebacmakro

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back