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:

Salesman	Product	PredOutPut	Input1	InputX
a1	Apples	5.1
a2	Apples	4.7
..	..	..
a10	Apples	8.3
b1	Bananas	7.3
b2	Bananas	6.3
..	..	..
b8	Bananas	9.5
b9	Bananas	8.5

<tbody>
</tbody>

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
1	Salesman	Product	PredOutPut	Input1	InputX	Ranking
2	a1	Apples	5.1			2
3	a2	Apples	4.7			3
4	a10	Apples	8.3			1
5	b1	Bananas	7.3			3
6	b2	Bananas	6.3			4
7	b8	Bananas	9.5			1
8	b9	Bananas	8.5			2
9	a1	Cantaloupe	1.1			4
10	a2	Cantaloupe	2.7			2
11	a3	Cantaloupe	9.3			1
12	a7	Cantaloupe	2.1			3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula F2 =COUNTIFS($B$2:$B$12,B2,$C$2:$C$12,">"&C2)+1 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.