Rank table of Percentages from another table.

[RohitJung]

Hello,
I want to rank lists of percentages that include ("N/A", Negative and Positive % Values), based on the highest percentage that has to be 1st and lowest percentage (till highest negative percentage) last, if the value is "N/A" return "N/A". The rank should relate to the symbol.

I want something like this

Worksheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Symbol	8/5/2021	8/12/2021	8/19/2021	8/26/2021	9/2/2021	9/9/2021		Symbol	8/5/2021	8/12/2021	8/19/2021	8/26/2021	9/2/2021	9/9/2021
2	BNHC	N/A	5.50%	4.50%	8.50%	9.60%	7.90%		BNHC	N/A
3	MBJC	N/A	N/A	4.60%	3.50%	8.50%	7.40%		MBJC	N/A
4	MKJC	N/A	N/A	N/A	N/A	9.50%	4.50%		MKJC	N/A
5	GHL	39.03%	0.41%	-3.06%	-4.13%	-4.04%	0.23%		GHL	1
6	KPCL	29.91%	10.53%	-5.36%	-6.54%	-5.38%	-2.56%		KPCL	2
7	AKJCL	22.55%	0.38%	13.47%	-5.23%	-7.70%	1.26%		AKJCL	3
8	DHPL	20.44%	0.00%	6.79%	1.96%	-8.39%	-1.07%		DHPL	4
9	KKHC	20.31%	-1.30%	16.05%	-10.88%	1.53%	-0.25%		KKHC	5
10	API	18.74%	13.41%	-1.93%	-2.59%	-11.54%	-6.41%		API	6
11	JOSHI	16.62%	-1.19%	7.69%	-4.69%	-7.49%	0.03%		JOSHI	7
12	LEC	12.82%	4.13%	-4.96%	-3.97%	-7.39%	-1.88%		LEC	8
13	HDHPC	12.46%	7.30%	3.22%	-0.68%	-10.32%	4.36%		HDHPC	9
14	BPCL	11.84%	6.68%	-5.65%	0.97%	-7.85%	-3.50%		BPCL	10
15	GLH	11.84%	13.14%	-3.53%	-4.87%	-10.04%	5.49%		GLH	11
16	AHPC	9.97%	8.64%	10.81%	3.37%	-3.68%	1.38%		AHPC	12
17	MHNL	8.84%	13.10%	-7.77%	-5.52%	-13.36%	7.59%		MHNL	13
18	HURJA	5.82%	9.30%	2.07%	-7.47%	-2.76%	-5.10%		HURJA	14
19	AKPL	5.40%	15.94%	20.71%	-0.42%	-27.33%	-0.83%		AKPL	15
20	CHL	5.26%	9.00%	-2.14%	-4.69%	-6.07%	-0.70%		CHL	16
21	CHCL	3.76%	2.17%	-4.80%	-2.33%	-8.48%	-2.56%		CHCL	17
22	BARUN	0.32%	11.08%	0.58%	-3.74%	-14.18%	2.43%		BARUN	18
23	MEN	-0.54%	32.23%	11.36%	7.67%	-16.34%	2.66%		MEN	19
24	HPPL	-2.45%	19.05%	-6.07%	-10.43%	-9.84%	6.40%		HPPL	20
Sheet1

 

[severynm]

Excel has two functions for that: Rank.EQ and Rank.AVG. Decide exactly which one you want to use, but otherwise, the formula in J2 would look something like:

=IFERROR(RANK.EQ(B2,B$2:B$24),NA())

This can be carried throughout the rest of the second table.