Basic excel user - just need help with a formula

[rob1234]

client rank.xlsx
	A	B	C	D	E	F	G
1	Client	Fee	Recovery	Adjusted fee	Rank
2	D Ltd	3,800	142%	5,396	?
3	H Ltd	6,500	74%	-	?
4	I Ltd	4,000	106%	4,240	?
5	C Ltd	3,000	103%	3,090	?
6	A Ltd	2,400	124%	2,976	?
7	G Ltd	1,200	128%	1,536	?
8	E Ltd	1,400	88%	-	?
9	J Ltd	2,800	39%	-	?
10	B Ltd	1,200	87%	-	?
11	F Ltd	800	102%	816	?
12
13
14	I want to rank these clients 1 to 10 based firstly on column D 'adjusted fee' column (highest amount first); and when the adjusted fee amount is zero the ranking should then be based on column C 'recovery', highest percentage first.
15
16	All Green entries (above 100%) should always be ranked higher than reds
17	All red entries (below 100%) should always be ranked lower than greens
18
19	What formula can do this?
20
21
22
23	Desired result
24
25	Client	Fee	Recovery	Adj fee	Rank
26	D Ltd	3,800	142%	5,396	1
27	H Ltd	6,500	74%	-	9
28	I Ltd	4,000	106%	4,240	2
29	C Ltd	3,000	103%	3,090	3
30	A Ltd	2,400	124%	2,976	4
31	G Ltd	1,200	128%	1,536	5
32	E Ltd	1,400	88%	-	7
33	J Ltd	2,800	39%	-	10
34	B Ltd	1,200	87%	-	8
35	F Ltd	800	102%	816	6
36
qusr

Cell Formulas
Range	Formula
D2:D11,D26:D35	D2	=IF($C2<1,0,$B2*$C2)

 

[hrayani]

TEST FILE.xlsm
	A	B	C	D	E
1	Client	Fee	Recovery	Adjusted fee	Rank
2	D Ltd	3800	1.42	5396	1
3	H Ltd	6500	0.74	0	9
4	I Ltd	4000	1.06	4240	2
5	C Ltd	3000	1.03	3090	3
6	A Ltd	2400	1.24	2976	4
7	G Ltd	1200	1.28	1536	5
8	E Ltd	1400	0.88	0	7
9	J Ltd	2800	0.39	0	10
10	B Ltd	1200	0.87	0	8
11	F Ltd	800	1.02	816	6
Sheet5

Cell Formulas
Range	Formula
D2:D11	D2	=IF($C2<1,0,$B2*$C2)
E2:E11	E2	=IF(D2>0,RANK(D2,$D$2:$D$11,0),RANK(C2,$C$2:$C$11,0))