Rank formula with 2 columns ignoring zero values

[gaftalik]

Hi everyone,

In column A1: A25 i have some cells with values and others are blank or equal to 0
In column C1:C20 i have the same thing
How to use a Rank formula in cells B1:B25 and D1:D20 to rank in ascending order using as criteria the data from column A1:A25 and C1:C20?

Thank you for your support !

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post some sample data, along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[gaftalik]

Thank you Fluff for your guidance!

Here is my new thread :

I need in B2: B21 and D2:D21 a rank formula to return the rank of A2:A21 and C2:C21 ignoring zero value cells (blank cells) to return blank when filling down the formula.
Results should be as in column B and D in red color and in descending order.

Thank you much !

Rank multiple crieria ignoring zero values.xlsx
	A	B	C	D	E	F	G	H	I
1	Percentage	Rank	Percentage	Rank
2	47.7%	20			=if(A2>0,rank(A2,$A$2:$A$21,$C$2:$C$21,1),"") ???
3
4
5			28.5%	6
6	28.1%	3	27.4%	2
7
8	33.7%	14	31.7%	11
9			33.3%	13
10	28.2%	4
11
12	28.2%	4	31.7%	12
13
14	27.3%	1	29.1%	8
15	39.3%	19	37.3%	19
16			31.4%	10
17			34.6%	15
18
19
20	28.9%	7	30.7%	9
21	36.8%	17	36.4%	16
22
Sheet1

 

[jasonb75]

Allowing for the error in D15 of your example, this looks correct.

Book1
	A	B	C	D
1	Percentage	Rank	Percentage	Rank
2	47.71%	20
3
4
5			28.53%	6
6	28.08%	3	27.37%	2
7
8	33.69%	14	31.72%	11
9			33.25%	13
10	28.17%	4
11
12	28.17%	4	31.74%	12
13
14	27.28%	1	29.06%	8
15	39.29%	19	37.29%	18
16			31.38%	10
17			34.64%	15
18
19
20	28.95%	7	30.74%	9
21	36.85%	17	36.37%	16
Sheet2

Cell Formulas
Range	Formula
B2:B21,D2:D21	B2	=IF(A2="","",COUNTIF($A$2:$A$21,"<"&A2)+COUNTIF($C$2:$C$21,"<"&A2)+1)

 

[gaftalik]

Yeah thank you that worked great! but i dont know why on my sheet i inserted 2 more rows above and had to delete the+1 from the end of formula and worked perfectly.
I never knew i dont need the Rank formula.

Many thanks Jason!

 

[jasonb75]

i dont know why on my sheet i inserted 2 more rows above and had to delete the+1 from the end of formula

Not sure exactly what has happened there but you shouldn't need to change that. I can insert 2 rows above, or between the existing and it still works fine. If it is behaving differently for you then there could be something else causing the problem which, if not found might cause further issues that could go unnoticed.

 

[gaftalik]

Please check the difference on different columns and see the correct answer in AV4 is by deleting the +1 !?
Why that is happening ?

Rank multiple crieria ignoring zero values.xlsx
	AT	AU	AV	AW	AX	AY
1
2
3
4	47.71%	21	20
5
6
7				28.53%	7
8	28.08%	4		27.37%	3
9
10	33.69%	15		31.72%	12
11				33.25%	14
12	28.17%	5
13
14	28.17%	5		31.74%	13
15
16	27.28%	2		29.06%	9
17	39.29%	20		37.29%	19
18				31.38%	11
19				34.64%	16
20
21	0.00%	1
22	28.95%	8		30.74%	10
23	36.85%	18		36.37%	17
24
25
Sheet2

Cell Formulas
Range	Formula
AV4	AV4	=IF(AT4="","",COUNTIF($AT$4:$AT$23,"<"&AT4)+COUNTIF($AW$4:$AW$23,"<"&AT4))
AX4:AX23,AU4:AU23	AU4	=IF(AT4="","",COUNTIF($AT$4:$AT$23,"<"&AT4)+COUNTIF($AW$4:$AW$23,"<"&AT4)+1)

 

[jasonb75]

AU4 is correct, not AV4. Check your results again very carefully.

The data set is exactly the same as the original with 1 exception, it contains an extra value of 0.00% in AT21 which now occupies the position of rank 1 meaning that everything else moved up 1 position.

 

[gaftalik]

Sorry it was my mistake i had row 21 hidden on my sheet and in the mini sheet it appeared, i didnt know, once i deleted the formula from that cell and added +1 on your formula it worked well.
Thank you much !

 

[jasonb75]

Do you need it to ignore hidden hows if there are any?

It will need a different formula but it should be possible.