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

Westie102

New Member
Joined
Feb 3, 2019
Messages
18
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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,811
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
Joined
Feb 3, 2019
Messages
18
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,811
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
Joined
Feb 3, 2019
Messages
18
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,717
Messages
5,637,947
Members
416,994
Latest member
cappiccolo

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top