Rank IF

[richphi37]

I know there is no RankIF function, and I wish there was. I am trying to rank test grades by number if they have not failed a test previously, then by those who have failed a test in the class.
For example. If a student has a 98 average but failed a test, he would rank below a student with a 97 who has not failed any test. This must be done for the whole list. Rank the Green boxes then the red ones behind it. I tried looking at some COUNTIFS factors but could not make it work for me. If you can help I would GREATLY appreciate it.
The workbook I am working off of will only let me target the color of the failed previously, not the "Y" or the "N". I included the colors on the boxes in my example sheet picture.
Thanks.

 

[awoohaw]

my suggestion: when doing your averages subtract a very small amount to the score if they have failed a prior test. Row() / 100000 is a very small number unless you have a lot of students. averagescore + IF(Failed Prior = FALSE,0,Row(cellref)/1000000)

ignore, i'll wait until you answer the next question.

 

[awoohaw]

I know there is no RankIF function, and I wish there was. I am trying to rank test grades by number if they have not failed a test previously, then by those who have failed a test in the class.
For example. If a student has a 98 average but failed a test, he would rank below a student with a 97 who has not failed any test. This must be done for the whole list. Rank the Green boxes then the red ones behind it. I tried looking at some COUNTIFS factors but could not make it work for me. If you can help I would GREATLY appreciate it.
The workbook I am working off of will only let me target the color of the failed previously, not the "Y" or the "N". I included the colors on the boxes in my example sheet picture.
Thanks.

so, do you want all non fails ranked higher than the fails? IE the 88 is ranked higher than the 95?

 

[richphi37]

so, do you want all non fails ranked higher than the fails? IE the 88 is ranked higher than the 95?

Right I want the non-fails ranked best to worst, then the fails ranked behind them best to worst.
Thanks

 

[awoohaw]

okay, try this:

mr excel questions 22.xlsm
	A	B	C	D
1	Overall Grade	Failed Previously	Pct.	Rank
2	95.18%	Y	95.18%	1
3	90.30%	Y	95.12%	2
4	95.12%	Y	93.79%	3
5	92.37%	N	91.80%	4
6	95.63%	N	90.30%	5
7	92.66%	N	95.63%	6
8	93.79%	Y	92.70%	7
9	90.68%	N	92.66%	8
10	88.15%	N	92.37%	9
11	91.80%	Y	90.68%	10
12	85.92%	N	88.15%	11
13	92.70%	N	85.92%	12
richphi37

Cell Formulas
Range	Formula
C1:D13	C1	=LET(ys,FILTER(A2:A13,B2:B13="Y",""),ns,FILTER(A2:A13,B2:B13="N",""),nbrYs,COUNTA(ys),nbrNs,COUNTA(ns),lrgYs,LARGE(ys,SEQUENCE(nbrYs)),lrgNs,LARGE(ns,SEQUENCE(nbrNs)),nbrtotal,COUNTA(A2:A13),VSTACK({"Pct.","Rank"},HSTACK(VSTACK(lrgYs,lrgNs),SEQUENCE(nbrtotal))))
Dynamic array formulas.