Formula to figure out percentiles

[eanhow]

Hello!
I need some help figuring out the formula to calculate these values. The answer would be in column V

Class Rank %

Top 10%

Top 25%

2nd Quartile

3rd & 4th Quartile

No Rank

These are examples of my spreadsheet
S T U

Class Rank from Application	Out of Class Total	Class Rank %
N/A	N/A	No Rank
10	320	3%
60	90	67%
28	250	11%

Thank you so much!

 

[mackc557]

Was not sure what you needed, so here are two options I was able to think of. If you have more information for us to better understand what you require, please let us know.
Option 1 is just an indicator to say which group the given percentage falls within.
Option 2 uses the class total to determine what value would be needed to fit within each group.

Book1 (version 1).xlsb
	A	B	C	D	E
2	Class Rank from Application	Out of Class Total	Class Rank %	Option 1: Judging based on Class Rank %	Option 2: Showing the value need for each level
3	N/A	N/A	No Rank	No Rank	N/A
4	10	320	3%	Fourth Quartile	Top 10%: >=288 Top 25%: >=240 Second Quartile: >=160 Third Quartile: >=80 Fourth Quartile: <80
5	60	90	67%	Second Quartile	Top 10%: >=81 Top 25%: >=67.5 Second Quartile: >=45 Third Quartile: >=22.5 Fourth Quartile: <22.5
6	28	250	11%	Fourth Quartile	Top 10%: >=225 Top 25%: >=187.5 Second Quartile: >=125 Third Quartile: >=62.5 Fourth Quartile: <62.5
7	9	10	90%	Top 10%	Top 10%: >=9 Top 25%: >=7.5 Second Quartile: >=5 Third Quartile: >=2.5 Fourth Quartile: <2.5
Sheet1

Cell Formulas
Range	Formula
C3:C7	C3	=IF(OR(NOT(ISNUMBER(A3)),NOT(ISNUMBER(B3))),"No Rank",A3/B3)
D3:D7	D3	=IF(ISBLANK(C3),"",IF(C3="No Rank",C3,IF(ISNUMBER(C3),IF(C3>=90%, "Top 10%",IF(C3>=75%,"Top 25%",IF(C3>=50%, "Second Quartile",IF(C3>=25%,"Third Quartile",IF(C3>=0,"Fourth Quartile","ERROR: Negative!"))))),"ERROR: Not Valid Value")))
E3	E3	=IF(NOT(ISNUMBER(B3)),B3,"Top 10%: >=" & B3*0.9 & CHAR(10) & " Top 25%: >=" & B3*0.75 & CHAR(10) & " Second Quartile: >=" & B3*0.5 & CHAR(10) & " Third Quartile: >=" & B3*0.25 & CHAR(10) & " Fourth Quartile: <" & B3*0.25)
E4:E7	E4	=IF(NOT(ISNUMBER(B4)),B4,"Top 10%: >=" & B4*0.9 & " Top 25%: >=" & B4*0.75 & " Second Quartile: >=" & B4*0.5 & " Third Quartile: >=" & B4*0.25 & " Fourth Quartile: <" & B4*0.25)