Ranking with SUMPRODUCT to ignore blanks

[Celticshadow]

Hi All

I am ranking data using the below formula but if a cell is blank then I would like that cell ignored and only rank cells that contain data

{=SUMPRODUCT(--($A$2:$A$10243=$A2),--(IR$2:IR$10243>IR2))+1}

The A2:A10243 is a column containing an id number for the data held in cells IR2:IR10243.

Thanks in advance.

Regards

 

[Marcelo Branco]

Could you fill the column Impact with values (an example) rather than formulas?
Criteria for tie-breaking (column Impact)?

M.

 

[Mr_Lucky]

1) Two line items ranked are both ranked 2 and require a tie-breaker. Since both line items are ranked 2 and the volume is the same. Therefore I would assume the tie-breaker could be applied based on the order they appear in the list.

E	I	F	H	N	M
Impact_calculated	Impact	Volume	Priority	Ranking	Expected result
Mis-reporting	Mis-reporting	250	Priority_2	2	Ranking:2
Mis-reporting	Mis-reporting	250	Priority_2	2	Ranking:3
Mis-reporting	Mis-reporting	300	Priority_2	1	Ranking:1

<tbody>
</tbody>

2) If the field impacted calculation is blank (not returned a value from "I" impacted) but the cell still contain the formula and excel things that the cell is not blank and calculated a ranking. I ahvve seen a few suggestions by filtering blank and deleting the formula in the blank cells. Another suggestion I seen is to paste values and remove formula, populate the blank cells with a random value e.g @ and than finding and replacing it with blank. Both options work but I wanted to know if there is an alternative that removes the manual steps
​

E	I	H	N	M
Impact_calculated	Impact	Volume	Ranking	Expected results
		300	Ranked:1	Blank
			Ranked:2	Blank

<tbody>
</tbody>

​​

 

[Marcelo Branco]

Sorry, i'm not understanding your data and what you need.
Hope someone can help you.

M.

 

[Mr_Lucky]

Apologies if the previous data was confusing. I have added a fresh example that I hope makes sense.

I am using the below formula to calculate a ranking.

IF(AND([@[Regulatory impact]]<>"", [@Volume]<>"",[@[Top 10]]<>""), SUMPRODUCT(--(C18=$C$18:$C$22),--(D18<$D$18:$D$22))+1,"")

When the ranking references the blank cell in "Top 10" the ranking in the next is not adjusted to 2

Any ideas how I can get the ranking order to go 1,2 instead of 1,3 when column "Top 10 is referenced.

No	Impact	Volume	Top 10	Ranking	Expected result	Pass/Fail
1	Mis-reporting	900	Top 10 Mis-reporting	1	1	Pass
2		800	Top 10 Mis-reporting		-	Pass
3	Mis-reporting		Top 10 Mis-reporting		-	Pass
4	Mis-reporting	400			-	Pass
5	Mis-reporting	300	Top 10 Mis-reporting	3	2	Fail

<colgroup><col width="64" style="width: 48pt;"> <col width="131" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4790;"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"> <col width="152" style="width: 114pt; mso-width-source: userset; mso-width-alt: 5558;"> <col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

 

[Mr_Lucky]

I should also have mentioned that Top 10 references ranking to calculate the cells and ranking references Top 10 to check if its not blank. Therefore they are depend on each other. Cant see a way around this other than ranking not referencing To 10.

 

[Marcelo Branco]

Maybe something like this

	A​	B​	C​	D​	E​	F​
1​	No​	Impact​	Volume​	Top 10​	Ranking​	Pass/Fail​
2​	1​	Mis-reporting​	900​	Top 10 Mis-reporting​	1​	Pass​
3​	2​		800​	Top 10 Mis-reporting​		Pass​
4​	3​	Mis-reporting​		Top 10 Mis-reporting​		Pass​
5​	4​	Mis-reporting​	400​			Pass​
6​	5​	Mis-reporting​	300​	Top 10 Mis-reporting​	2​	Fail​

Formula in E2 copied down
=IF(AND(B2<>"",C2<>"",D2<>""),SUMPRODUCT(--(B2=B$2:B$6),--(C$2:C$6>C2),--(D$2:D$6<>""))+1,"")

M.

 

[Mr_Lucky]

Maybe something like this

	A​	B​	C​	D​	E​	F​
1​	No​	Impact​	Volume​	Top 10​	Ranking​	Pass/Fail​
2​	1​	Mis-reporting​	900​	Top 10 Mis-reporting​	1​	Pass​
3​	2​		800​	Top 10 Mis-reporting​		Pass​
4​	3​	Mis-reporting​		Top 10 Mis-reporting​		Pass​
5​	4​	Mis-reporting​	400​			Pass​
6​	5​	Mis-reporting​	300​	Top 10 Mis-reporting​	2​	Fail​

<tbody>
</tbody>

Formula in E2 copied down
=IF(AND(B2<>"",C2<>"",D2<>""),SUMPRODUCT(--(B2=B$2:B$6),--(C$2:C$6>C2),--(D$2:D$6<>""))+1,"")

M.

That works perfectly. Many thanks.

 

[Marcelo Branco]

You are welcome.

M.

 

[Mr_Lucky]

You are welcome.

M.

If I am including 5 IF(AND(<>"",<>"",<>"",<>"",<>"") before the SUMPRODUCT, would I then need to add a array for each in order for the sequencing of the raking to work or could I change ([@[Regulatory reporting impact]]=[Regulatory reporting impact]) to ([@[Regulatory reporting impact]]<>"").

I am below formula which working except for sequencing the numbers in order. Wold appreciate if you have any suggestions.

Formula
=IF(AND([@[Reported bleed]]<>"",[@[Regulatory reporting impact]]<>"",[@[Test stage_ranking]]<>"",[@[Regulatory reporting impact_ranking]]<>"",[@[Status ranking]]<>""), SUMPRODUCT(--([@[Regulatory reporting impact]]=[Regulatory reporting impact]),--([@[Reported bleed]]<[Reported bleed]))+1,"")

 

[Marcelo Branco]

I'm not seeing your data...Try posting some rows with the relevant columns of the table along with expected results

M.