Ranking a column of percentages based on the cells that meet criteria (between 100 to 103)

Hi guys,

I'm stuck on a little formula, i am trying to rank my guys based on those who met target. i tried using =RANK(I2,I$2:I$12,0)) and also i have tried using =IF(S16>99.99%,COUNTIFS(S$16:S$26,">"&S16,S$16:S$26,"<103.00%")+1,RANK(S16,S$16:S$26,0))

the idea is to only rank those who are between 100% - 103%

Column A1:A11 = names
Column B1:B11 = scores in Percentages
column C1:C11 is rankings, based on those who were within 100-103% scores in coloumn B1:B11

If you don't want to do anything special for those over 103%, then...

Formula:

Please Login or Register  to view this content.

Originally Posted by jhren

If you don't want to do anything special for those over 103%, then...

Formula:

Please Login or Register  to view this content.

Thanks jhren, works perfectly for what i want it to do but, how do i exclude anyone who scores above 103%?

If you want to exclude them...

Formula:

Please Login or Register  to view this content.

If you want to give them the same ranking as if they only scored 103%...

Formula:

Please Login or Register  to view this content.

Thanks! works almost perfectly for what i need, just a slight adjustment, not sure if it it can be done or not but hopeful it is. using your formula jhen

Please Login or Register  to view this content.

I have 1 person at 103.8% ( blank cell is returned as hoped for) however seems to still, be counting them as 1 in the ranking system because the next nearest and within target of 102.7% is ranking 2

to give more insight i have an index and match formula elsewhere in my sheet to tell me who is ranked 1st , 2nd and 3rd

This should do it...

Formula:

Please Login or Register  to view this content.

Genius Jhen, works pefectly mate. thank you!