Percentile Numbers dont add up...

[richphi37]

Hey everyone,

I have a list of 45 GPAs in Column E. Column I is Commandants list (CL) eligibility and Column J is Superior Academic Award (SA). Both I and J are Y/N for whether the student is eligible or not.

Here are the rules:
The top student gets Distinguished Honor Grad (DHG),
If a student is eligible for CL and is in the top 20% of the class, they get CA
Top 40% who are eligible get SA.
Otherwise they're just a grad
Again, there are 45 Students.

I put in this formula:
=IF(F4>PERCENTILE($F$4:$F$48,0.9999),"Distinguished Honor Graduate",IF(AND(I4="Y",F4>PERCENTILE($F$4:$F$48,0.71)),"COMMANDANT LIST",IF(AND(J4="y",F4>PERCENTILE($F$4:$F$48,0.5)),"SUPERIOR ACADEMIC ABCHIEVEMENT","GRAD")))

I had to change the percentile to 71 to get it to 8, which is 20% with the DHG and for SA I had to change to 50% to get 9.
Here I have 1 DHG, 8 CL, 9 SA, and 27 GRAD, those numbers are right, but the percentiles are wrong!?!?!

When I change it to 60 and 80, I get 1 DHG, 6 CL, 7 SA and 31 31 Grad.
=IF(F4>PERCENTILE($F$4:$F$48,0.9999),"Distinguished Honor Graduate",IF(AND(I4="Y",F4>PERCENTILE($F$4:$F$48,0.8)),"COMMANDANT LIST",IF(AND(J4="y",F4>PERCENTILE($F$4:$F$48,0.6)),"SUPERIOR ACADEMIC ABCHIEVEMENT","GRAD")))
I don't understand why 80 and 60 won't work?

If you have an answer I would appreciate it.

 

[Eric W]

One possible issue:

=IF(F4>PERCENTILE($F$4:$F$48,0.9999),"Distinguished Honor Graduate",IF(AND(I4="Y",F4>PERCENTILE($F$4:$F$48,0.8)),"COMMANDANT LIST",IF(AND(J4="y",F4>PERCENTILE($F$4:$F$48,0.6)),"SUPERIOR ACADEMIC ABCHIEVEMENT","GRAD")))

Should that be I4?

Here's an alternative as well:

Book1
	F	G	H	I	J	K	L	M
3	Grades				DHG	CL	SA	GRAD
4	3.1381	SUPERIOR ACADEMIC ACHIEVEMENT		y	1	8	9	27
5	2.6758	GRAD		y
6	2.6416	GRAD		y
7	2.1105	GRAD		y
8	3.7624	Commandant List		y
9	2.0605	GRAD		y
10	2.1563	GRAD		y
11	2.9914	SUPERIOR ACADEMIC ACHIEVEMENT		y
12	3.7709	Commandant List		y
13	2.7228	GRAD		y
14	2.1201	GRAD		y
15	3.1888	SUPERIOR ACADEMIC ACHIEVEMENT		y
16	2.4362	GRAD		y
17	2.2492	GRAD		y
18	3.1642	SUPERIOR ACADEMIC ACHIEVEMENT		y
19	2.7292	GRAD		y
20	3.2257	SUPERIOR ACADEMIC ACHIEVEMENT		y
21	3.2709	Commandant List		y
22	2.0531	GRAD		y
23	3.0059	SUPERIOR ACADEMIC ACHIEVEMENT		y
24	2.0185	GRAD		y
25	2.9777	GRAD		y
26	3.2341	SUPERIOR ACADEMIC ACHIEVEMENT		y
27	2.8423	GRAD		y
28	2.1439	GRAD		y
29	2.8544	GRAD		y
30	2.9256	GRAD		y
31	2.1951	GRAD		y
32	2.5421	GRAD		y
33	3.9560	Distinguished Honor Graduate		y
34	2.4663	GRAD		y
35	3.5356	Commandant List		y
36	3.1442	SUPERIOR ACADEMIC ACHIEVEMENT		y
37	2.6025	GRAD		y
38	3.7676	Commandant List		y
39	2.1439	GRAD		y
40	3.2013	SUPERIOR ACADEMIC ACHIEVEMENT		y
41	3.7998	Commandant List		y
42	2.6126	GRAD		y
43	2.3777	GRAD		y
44	3.7623	Commandant List		y
45	2.5362	GRAD		y
46	2.0577	GRAD		y
47	2.9072	GRAD		y
48	3.3730	Commandant List		y
Sheet2

Cell Formulas
Range	Formula
J4:M4	J4	=COUNTIFS(G4:G48,{"D*","C*","S*","G*"})
G4:G48	G4	=IF(OR(I4="Y",F4=MAX($F$4:$F$48)),LOOKUP(F4,PERCENTILE($F$4:$F$48,{0,0.6,0.8,0.9999}),{"GRAD","SUPERIOR ACADEMIC ACHIEVEMENT","Commandant List","Distinguished Honor Graduate"}),"")
Dynamic array formulas.

Edit: Looks like I might have misread the question. Column J is the column for eligibility for SA? So column I and J might both have a Y in it?