RankIf without Arrays? (Excel 2010)

[Roghaltz]

Hello All,
I've been using the RankIf functionality (based on SUMPRODUCT)that members have posted on here for a while now and it's great. However, I've run into a problem with a large simulation/optimization problem with this method. Every time it tries a new set of inputs it has to recalculate the spreadsheet which takes 10-15 seconds and sometimes crashes Excel after 5-10 minutes. Even when it doesn't crash at 10-15 seconds per iteration the time needed to run 10,000 iterations is too large.

I am looking for a RankIf type function that does not use array formulas and thus will run faster (when I use the regular RANK function on a subset of the spreadsheet (say 200 rows) it works very fast). I have 50,000 rows in the full model so I don't want to manually enter the RANK function every ten or so rows and adjust the cell references.

Here is the basic layout of the spreadsheet:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Salesman[/TD]
[TD]Product[/TD]
[TD]PredOutPut[/TD]
[TD]Input1[/TD]
[TD]InputX[/TD]
[/TR]
[TR]
[TD]a1[/TD]
[TD]Apples[/TD]
[TD]5.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a2[/TD]
[TD]Apples[/TD]
[TD]4.7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a10[/TD]
[TD]Apples[/TD]
[TD]8.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] b1[/TD]
[TD]Bananas[/TD]
[TD]7.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b2[/TD]
[TD]Bananas[/TD]
[TD]6.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b8[/TD]
[TD]Bananas[/TD]
[TD]9.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b9[/TD]
[TD]Bananas[/TD]
[TD]8.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The column PredOutput is a function of about 10 other columns (input 1...input X) that the optimization applies different weights to. I am looking to rank the PredOutput variable within each product. The rank of PredOutput will change after each simulation iteration. As I said the sumproduct formula works fine in general, but the array causes it to take too long to do a simulation.

I was hoping that since the individual products are grouped to together there might be a way to rank PredOutput without arrays (that is Apples begins in row 2 and ends in row 11, Bananas begins in row 12 and ends in row 20, etc.) There are thousands of products and each one has between 8 and 14 salesmen.

Happy to clarify this further as needed.

Thanks in advance.
Roghaltz

 

[MarcelBeug]

You can try with helper columns for the start and end indexes of the product on each line.
E.g. start indices in column Y: Y2 =if(B2=B1),Y1,ROW())
end indices in column Z: Z2 =IF(B2=B3),Z3,ROW())
So on each "Apples" row, you get 2 in Y and 11 in Z.

Now you can determine the rank as =RANK($C2,INDEX($C:$C,Y2):INDEX($C:$C,Z2))

Drawback of this solution: if you add/delete any rows, you need to copy the formulas in Y2 and Z2 down again to correct reference errors.

 

[Roghaltz]

Thanks Marcel. I will give it a try this weekend. That's the kind of solution I was hoping for.

Roghaltz

 

[Roghaltz]

That worked perfectly. Thank you!

 

[MarcelBeug]

You're welcome.

 

[Eric W]

Here's an alternative which does not require helper columns.
Excel 2010

	A	B	C	D	E	F
Salesman	Product	PredOutPut	Input1	InputX	Ranking
a1	Apples
a2	Apples
a10	Apples
b1	Bananas
b2	Bananas
b8	Bananas
b9	Bananas
a1	Cantaloupe
a2	Cantaloupe
a3	Cantaloupe
a7	Cantaloupe

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]5.1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]4.7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]8.3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]7.3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]6.3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]9.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]8.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]1.1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]2.7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]9.3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]2.1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=COUNTIFS($B$2:$B$12,B2,$C$2:$C$12,">"&C2)+1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Put the formula in F2 and copy down. I can't speak to the performance of it, but it should be quick.

 

[Roghaltz]

Thanks Eric. I will check that out as well.