Ranking ignoring blank cells

[Beneindias]

Hey again, guys.

I have a new problem, that I can´t get to the solution after trying a lot of formulas, and being stuck to this for a day.

So, I have an excel file where I compare several quotes to make a decision.
I have spaces to compare nine quotes, but I don't always get nine, leaving some blank.

Thats where my problems begin.

I'm trying to have them ranked with a formula, but I'm not able to ignore the blank cells and they end up being ranked as well.

I have this formula:

=IF(J32;RANK.EQ(J32;($J$32;$O$32;$T$32;$Y$32;$AD$32;$AI$32;$AN$32;$AS$32;$AX$32);1)+COUNTIF($J$32:J32;J32)-1;"")


This one takes care of repeated rankings and all that, but it does not take care of blank cells, considering them the first ranks.
The cell that shows the ranking stays blank, but it is still ranked and I need this formula to completely ignore blank cells


Thanks to all of you

 

[Skovgaard]

I think you need to show, I tried below and as you can see, it ignores blanks.

Book1
	A	B
1	Number	Rank
2	1	6
3	2	5
4		#N/A
5	3	4
6	4	3
7		#N/A
8	5	2
9	6	1
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=RANK.EQ(A2,$A$2:$A$9)

/Skovgaard

 

[Beneindias]

I think you need to show, I tried below and as you can see, it ignores blanks.

Book1
	A	B
1	Number	Rank
2	1	6
3	2	5
4		#N/A
5	3	4
6	4	3
7		#N/A
8	5	2
9	6	1
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=RANK.EQ(A2,$A$2:$A$9)

/Skovgaard

If I have data in a range like that, it works, but it's not working in my file.
I suspect that the problem is because my formula is trying to rank cells that are also formulas...maybe thats why i'm having problems with this.

Mod.RE.OBR-3.1_01 - Mapa Comparativo - 201806 XPS_GEOTEXTIL.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY
13
14		m²	200,00			0,360			72,00		0,500			100,00		0,4500			90,00		0,500			100,00		0,360			72,00
15		m²	24,00			6,550			157,20		11,346			272,30		6,7200			161,28		7,960			191,04		6,420			154,08
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32					- €				229,20 €					372,30 €					251,28 €					291,04 €					226,08 €					- €					- €					- €					- €
33
34					PRAZO DE ENTREGA / DISPONIBILIDADE
35					LOCAL DE ENTREGA																					N/ Transporte
36					COND. PAGAMENTO
37					OBSERVAÇÕES											Portes
38
39					Posição:					6					9					7					8					5
40
Modelo

Cell Formulas
Range	Formula
J13:J31,AX13:AX31,AS13:AS31,AN13:AN31,AI13:AI31,AD13:AD31,Y13:Y31,T13:T31,O13:O31	J13	=$D13*G13*(1-H13)*(1-I13)
K13	K13	=IF(E13=0,"",IF(G13=0,"",J13/$F13-1))
K14:K31,AY13:AY31,AT13:AT31,AO13:AO31,AJ13:AJ31,AE13:AE31,Z13:Z31,U13:U31,P13:P31	K14	=IF($E14=0,"",IF(G14=0,"",J14/$F14-1))
J32,F32,AX32,AS32,AN32,AI32,AD32,Y32,T32,O32	J32	=SUM(J13:J31)
K32,AY32,AT32,AO32,AJ32,AE32,Z32,U32,P32	K32	=IF($F32=0,"",(J32/$F$32-1))
D15	D15	=42-18
F13:F22,F24:F31	F13	=+E13*D13
K39,AY39,AT39,AO39,AJ39,AE39,Z39,U39,P39	K39	=IF(J32,RANK.EQ(J32,$J$32:$AX$32,1)+COUNTIF($J$32:J32,J32)-1,"")

 

[Skovgaard]

I tried to paste your Mini Sheet and wrote below formula in J41.
Is ranking 4 the correct result for J32?

/Skovgaard

 

[Beneindias]

I tried to paste your Mini Sheet and wrote below formula in J41.
Is ranking 4 the correct result for J32?


View attachment 76118

/Skovgaard

No, J32 should be rank 2, AD32 rank 1.
AI, AN, AS and AX32 should not be ranked, as they are blank.

Thanks for your help

 

[Skovgaard]

Ok, then try this:

=IF(J32=0,"",RANK.EQ(J32,$J$32:$AX$32,1)-COUNTIF($J$32:$AX$32,0))

/Skovgaard

 

[Beneindias]

Ok, then try this:

=IF(J32=0,"",RANK.EQ(J32,$J$32:$AX$32,1)-COUNTIF($J$32:$AX$32,0))

/Skovgaard

So, my problem was in the countif part.
This one worker like a charm.


Thank you very much...my problem is solved thansk to you.

 

[Skovgaard]

So, my problem was in the countif part.
This one worker like a charm.


Thank you very much...my problem is solved thansk to you.

You're Welcome, glad to help

/Skovgaard

 

[Beneindias]

Ok, then try this:

=IF(J32=0,"",RANK.EQ(J32,$J$32:$AX$32,1)-COUNTIF($J$32:$AX$32,0))

/Skovgaard

Well, actualy, now I have a probelm that I had solved in my old rank formula.

This formula that you wrote, gives me the same ranking when I have a quote with the same price, and then it skips the same number of ranks..

So, I tried it in an old comparison that I have here, and i'm getting rank 1, 2, 2, 2, 5, 6, 7, 8 and 9.

I should have rank 1, 2, 3, 4, 5, 6, 7, 8, 9. Or, if it's not possible to do that, at least I should have 1, 2, 2, 2, 3, 4, 5, 6 and 7.

 

[Skovgaard]

Try this one then

=RANK.EQ(J32,$J$32:$AS$32)+COUNTIF($J$32:J32,J32)-1

/Skovgaard