Ranking in ascending order include zeros as highest rank

[Celticshadow]

Hello Forum

It has been a while since I last posted and trust you are all well as I require your help for a formula as below.

I have ratings (Col B )associated to an ID number (Col A) and I would like a formula to rank the rating in column B in asscending order but any zero's are to be ranked highest. Hopefully the mini sheet will help to clarify, however any queries then please do not hesitate to ask further.

Regards

 

[maabadi]

Try this:

Book1(AutoRecovered).xlsm
	A	B	C	D
1	ID	Numbers	Rank
2	1	0	8
3	1	1.53	5
4	1	0.04	7
5	1	0.97	6
6	1	3.56	4
7	1	4.85	3
8	1	6.91	2
9	1	8.75	1
10	2	7.41	2
11	2	0	8
12	2	3.33	5
13	2	9.12	1
14	2	5.67	4
15	2	1.04	7
16	2	6.77	3
17	2	2.85	6
18
Sheet4

Cell Formulas
Range	Formula
C2:C17	C2	=COUNTIFS($B$2:$B$17,">" & B2,$A$2:$A$17,A2) +1

 

[Celticshadow]

Try this:

Book1(AutoRecovered).xlsm
	A	B	C	D
1	ID	Numbers	Rank
2	1	0	8
3	1	1.53	5
4	1	0.04	7
5	1	0.97	6
6	1	3.56	4
7	1	4.85	3
8	1	6.91	2
9	1	8.75	1
10	2	7.41	2
11	2	0	8
12	2	3.33	5
13	2	9.12	1
14	2	5.67	4
15	2	1.04	7
16	2	6.77	3
17	2	2.85	6
18
Sheet4

Cell Formulas
Range	Formula
C2:C17	C2	=COUNTIFS($B$2:$B$17,">" & B2,$A$2:$A$17,A2) +1

Hi Maabadi

Many thanks for replying it is appreciated. However I require the rank to be ascending in order with Zero's to be classed as the largest rank as in the posted example.
Your formula as rank in descending order with Zero's ranked in the nos position instead of last/highest position.

Regards

 

[maabadi]

Sorry my misunderstanding, try formula at column D:

Book1.xlsx
	A	B	C	D	E
1	ID	Numbers	Rank	Rank2
2	1	0	8	8
3	1	1.53	5	3
4	1	0.04	7	1
5	1	0.97	6	2
6	1	3.56	4	4
7	1	4.85	3	5
8	1	6.91	2	6
9	1	8.75	1	7
10	2	7.41	2	6
11	2	0	8	8
12	2	3.33	5	3
13	2	9.12	1	7
14	2	5.67	4	4
15	2	1.04	7	1
16	2	6.77	3	5
17	2	2.85	6	2
18
Sheet1

Cell Formulas
Range	Formula
C2:C17	C2	=COUNTIFS($B$2:$B$17,">" & B2,$A$2:$A$17,A2) +1
D2:D17	D2	=IF(B2=0,COUNTIFS($A$2:$A$17,A2),COUNTIFS($B$2:$B$17,"<"&B2,$A$2:$A$17,A2))