Top Offender List

[Chrisz23]

Hello,

I have a list of alarm reponses that I need to sort. Ideally I would be able to show:

• The percentage of accounts that are responsible for the majority of alarms (e.g. 90% of alarms come from 9% of accounts)
• A Top Offenders List of the top 25 problem accounts

My sheet includes: A = Date of Alarm, B = Client Name, F = Assigned Time, M = Alarm Signal

My excel skills can surprise some but I have not been able to make anything work

Asking for help please!

 

[Chrisz23]

Really could use some help please!

 

[Eric W]

Welcome to the MrExcel forum.

For future reference, it's not a good idea to bump your thread so quickly. It removes the thread from the "Zero Reply Posts" list which some people use to find open questions. Once you bump it, you pretty much rely on someone spotting it in the recent list.


Given that, I assume you must have a list of clients somewhere. I put it in Q. Consider this layout:

Book1
	A	B	F	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Date of Alarm	Client Name	Assigned Time	Alarm Signal				Client Name			Most Used Accounts			Threshhold	Caused by # of accounts	Caused by % of accounts
2		Q						A			J	8		90%	19	76%
3		P						B			G	7
4		A						C			N	7
5		J						D			P	6
6		S						E			T	6
7		T						F			C	6
8		Q						G			Q	5
9		X						H			S	5
10		U						I			H	5
11		G						J			R	5
12		E						K			D	5
13		K						L			A	4
14		T						M			X	4
15		E						N			E	4
16		B						O			I	4
17		G						P			K	3
18		O						Q			L	3
19		E						R			V	3
20		S						S			U	2
21		J						T			O	2
22		J						U			W	2
23		G						V			Y	2
24		I						W
25		J						X
26		X						Y
27		C
28		O
29		Q
30		W
Sheet1

Cell Formulas
Range	Formula
Y2	=X2/(COUNTA(Q:Q)-1)
U2	=IF(T2<>"",COUNTIF($B$2:$B$200,T2),"")
X2	{=MIN(IF(SUBTOTAL(9,OFFSET(U2,0,0,ROW(U2:U25)-ROW(U2)+1))>(COUNTA(B:B)-1)*W2,ROW(U2:U25)-ROW(U2)+1))}
T2	{=IFERROR(INDEX($B$2:$B$200,MODE(IF($B$2:$B$200<>"",IF(COUNTIF($T$1:$T1,$B$2:$B$200)=0,MATCH($B$2:$B$200,$B$2:$B$200,0))))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Put the formula in T2, confirm with Control+Shift+Enter. Put in the normal U2 formula. Then copy those 2 formulas down the columns as desired. Then you can put in the array formula (with Control+Shift+Enter) in X2. Set the threshold value in W2 as desired.

Hope this helps.

 

[Chrisz23]

Hi Eric,

Thank you for the assistance. I also will follow your advice when posting in the future.

If I understand correctly. I include the list of individual client names in column Q, then when I enter the formulas for columns T and U, this provide the amount of alarms per account in a worst offender list. I had to manipulate the formulas as I have almost 9500 alarms to run through the report. So an example is: {=IFERROR(​

INDEX($B$2:$B$9500,MODE(IF($B$2:$B$9500<>"",IF(COUNTIF($T$1:$T1,$B$2:$B$9500)=0,MATCH($B$2:$B$9500,$B$2:$B$9500,0))))),""​

)}

This takes a while to run but I suppose that is to be expected with so many lines. It does look like this part is working.

The second part does not seem to work but I suspect it is user errror. When I insert the value of 90% in column W and the relative formulas in columns X and Y, I it state that 418 alarms were created by .406614 of accounts so something isnt working correctly there. I am hoping for it to output the largest percentage of alarms by account in order to make a statement like, "90% of the alarms were generated by the following percentage of accounts (3.g. 5-10%)

Any thoughts?

Thank you again!

Chris​

 

[Eric W]

It seems that you've mostly modified the formulas to match your sheet. For the X2 formula, did you change the range from U2:U25 to a higher level? I know you said you wanted a "Top 25" list, which the formula in T2 provides. However, in order for the X2 and Y2 formulas to work, you need to continue dragging down the T2:U2 formulas down further. Without seeing your data, I don't know how much further, but I'd guess around 80% of the number of your clients. Then change U2:U25 to U2:U200 (or whatever that row is). Also, make sure that the Y2 cell is formatted as a percentage.

As far as performance, you're probably right, 9500 rows takes a lot to compute. There's not a lot I can think of to improve the performance, unless you want to try a macro.

 

[Chrisz23]

Thanks again. I did change the range for the U column to 500 (as I have 480ish accounts in the master list). Right now the formulas seem to work but are not presenting the information the way I would have expected. The top 25 accounts have 74-299 alarms per account. Right now the second part of the formula (when I insert 90%), this percentage of alarms comes from 247 of the alarm accounts and consist of 39.97% of alarms. Based on the numbers, I would expect to see something more like 90% of alarms were caused by 10% of the accounts. Am I missing something?

Chris​

 

[Eric W]

Hard to say. It's certainly possible that there's a problem with one of my formulas, but without seeing your data I can't tell. You should be able to check their correctness though. First check the list of accounts. The number of alarms start at 299 and go down. Accounts with 1 or 0 alarms won't show up on the list. Check a few accounts to make sure that the total listed matches the actual list. You can also check that the names in column B are consistent. No extra commas, spaces, honorifics, etc.

Once you've verified the T:U columns, add up the top 25 accounts. Based on your 74-299 range, that would be somewhere between 2075 and 7250. If you have 9500 alarms, that would indicate that the top 25 accounts make up 2075/9500 (21.8%) to 7250/9500 (76.3%). Put that value in W2. X2 should then come out to be 25, and Y2 should be 25/480 (5.2%).

If those numbers work out, you can kind of play around with the percentage in W2. There's usually a "cut-off" percentage. In your case 90% might be a bit high. But you could say 50% of the calls come from 5% of the accounts, and then the rest tend to even out. If you go to 60%, then the account percentage jumps to 25%, 70% jumps to 50%, etc. You can look down the U column to see where the values tend to get smaller/closer together.

Let me know what you find.