rank in order and shade using conditional formatting

Greetings

I attached a small sheet where I am trying to rank a column for the top 4 performers and to be shaded to green for the first orange for second blue for 3rd and red for rourth


I tried but keeping on getting errors and if there is a decimal than it looks at the number before the decimal so for example 23 and 23.3 both rank as 3 in place 3rd and 4th because 23.3 higher than 23



Thanx in anticipation for the kind help

raj

I would recommend wrapping the formulas in columns M:R using IFERROR(...current formula),"") to avoid #N/A's.
I converted the formulas in columns M:R to values because cells in the columns to be ranked will need to be numeric values or blanks in order to get the following to work.
1. For column S: =IF(ISNUMBER(N3),SUMPRODUCT((ISNUMBER(N$3:N$20))*(N$3:N$20>N3))+1,"")
2. For column T: =IF(ISNUMBER(P3),SUMPRODUCT((ISNUMBER(P$3:P$20))*(P$3:P$20<P3))+1,"")
3. For column V: =IF(ISNUMBER(M3),SUMPRODUCT((ISNUMBER(M$3:M$20))*(M$3:M$20>M3))+1,"")
Let us know if you have any questions.

What error? Attach a workbook showing the issue.

Maybe this?

=IFNA(INDEX('G:\Desktop\sa racecards\[Kp-significators-samaster.xlsm]1'!AV:AV,MATCH(1,(#REF!='G:\Desktop\sa racecards\[Kp-significators-samaster.xlsm]1'!#REF!)*($A3='G:\Desktop\sa racecards\[Kp-significators-samaster.xlsm]1'!B:B),0)),"")

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.