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:
<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
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