=Rank() as a macro?

[Ronnet2]

Hi guys,

I've made a simulation model in Excel with the help of many of you (so thank you all), but the model is very slow. The main reason is the use of the =rand() formula that ranks events in ascending order. This calculation must be made after each event and takes a long time to complete. This because of the length of the list that it must rank which can be up to 20,000 rows. This formula becomes exponentially slower based on the length of the list. I'm wondering whether replacing it with a macro would speed up operations.

Imagine a list of dates in the range B2:B20000. Would it be possible to develop a macro that would rank these dates in range A2:A20000? For example, B4500 has the date 01-01-15 and is the lowest value in the list. A4500 would therefore receive the rank of '1', while B30 has the value 12-31-15 and is the highest value on the list and receives a ranking of '19999' in cell A30.

- Would it be possible to write a macro for this computation?
- Would it be quicker then the =rank() formula?

Best wishes,
Ronnet

[Bernie Deitrick]

Typically, it is much faster to use native Excel functionality rather than coding something. So, it would be better if, instead of such a specific question, you showed your code. And, as always, you can try to wrap your code with this:

Please Login or Register  to view this content.

[Ronnet2]

Hi Bernie, could you explain what these lines do? Screenupdating = false I am familiar with, but I'm not sure if the others are applicable in my case.

As for my code, it does not directly relate to the sheet in question. The sheet in question is rather simple: a long list of dates, and all the code does to this sheet is re-calculate. But one of the formula's on the sheet happens to be the =rank() formula, and it is slowing down the re-calculation of the sheet tremendously. I'm now wondering if it might not be faster to have a code calculate the ranking values, do you think it is possible to do so?

[Bernie Deitrick]

Are you using the rank of every cell, or just finding the highest (newest) and lowest (oldest) values?

As for the code - I was confused. I thought you had a macro that was slow.

[Ronnet2]

My apologies, I should be more clear. Here is a file that depics the context: Context.xlsm

It concerns the rank formula in column A of sheet OPERATIONS, which relates to the dates in column F of the same sheet. In this example the rank range is short (2000) but in the actual model it is longer, and takes longer to compute.

The file also includes the code. As you can see it currently simply recalculates the OPERATIONS sheet after an event on the MAINTENANCE sheet, which leads to Excel recalculating the rank() formulas. Im not just interested in newest and oldest values, but in the rank of each date in column F. As the code runs through the model, the dates in column F keep changing based on the events in the MAINTENANCE sheet. So the ranks should continously update after a row has been updates in the MAINTENANCE sheet.

Note: this is a simplified version of the actual model which is too large to upload here.

[Bernie Deitrick]

You are re-calcing the entire sheet "OPERATIONS" each time you loop. It is hard to tell exactly what it is that you are doing that requires an entire sheet to be re-calc'd 20,000 times rather than randomized once - but adding this line to the top

Please Login or Register  to view this content.

and this to the bottom

Please Login or Register  to view this content.

seemed to speed up the process by about a factor of four.

[Ronnet2]

Hi Bernie, could you explain what this line does for the file? It might not be appropriate considering what I'm doing with it.

The reason why it needs to recalculate is because the MAINTENANCE sheet processes one event at a time, which generates a new repair date. The new repair date must be send back to the OPERATIONS sheet. On the operation sheet this event could be in row 2 but also row 200, depending on the failure date it generated based on the random input. In addition to processing he repair date on the OPERATION sheet, failure date is also generated based on the procesed repair date of the same component. And this new failure date must be ranked based on its relative position towards all previous failure date. It might very well be that a newly generated failure date is smaller then a previously generated failure date (due to the random input), so it receives a lower rank and will be processed earlier on the MAINTENANCE slide. This cycle continues until the designated length.

I'm thankful for your code but unfortunatelty it doesnt speed up my actual file (at least not in a small test run). I still think a rank macro could improve performance. Do you think its possible to write such a macro?

EDIT: the line might not be improving performance because I have set calculations to manual by default using the Excel functionality beforehand.

[Bernie Deitrick]

So, basically, you have 20K rows of formulas that need to be calculated, even though you build up row by row. So try this version, which builds up the formulas as they are needed, and expands the RANK function as the values expand.

Please Login or Register  to view this content.

[Ronnet2]

Wonderful piece of code! It is indeed much faster, 7 seconds for 200 lines instead of 38! But I'm running into two issues:

1. In the actual model the amount of starting machines is variable. So in this example its 4, but it could be 20 or 56 as well. Is it possible to alter the "+4" in your code into say "+j" and have a line that says that the value of j can, for example, be found in sheet OPERATIONS cell L1? If thats possible then I can test the code in my actual model.

2. For some reason it's skipping some calculations in the OPERATIONS sheet when I use the code. In the MAINTENANCE sheet you can notice that sometimes a double line appears (same machine + failure time). So a certain event was first designated as say event 5, calculated in row 5 of the maintenance sheet, then operations was recalculated, and the same event became event 6 and is again processed in the maintenance sheet but now as event 6. This causes all sorts of incorrect calculations in all proceeding events of the same machine.

Out of curiousity I added an additional ''.calculate'' line to your new piece of code. Effectively forcing Excel to double calculate before going to the maintence calculations. This seems to solve the problem of doubles appearing in maintenance. However, it slows down the macro from 7 to 13 seconds. This could be significant in my actual file.

[Bernie Deitrick]

Try this - the offset goes into cell K2 - that can be changed in the code if you want a different cell. I have assumed it is never less than 4 or else the "A6:A" will need to change. Also, let's try to calculate the cells using .Cells.Calculate instead of just .Calculate

Please Login or Register  to view this content.

[Ronnet2]

Unfortunately this code takes much longer to calculate, 58 for 200 lines. I think constantly looking up the content of cell K1 slows it down significantly.

But your code got me thinking. Even though generating rows with reference to cell K1 is slow, you're on to something with your comment about not needing to calculate each row in the OPERATIONS sheet. I'm running this code now on my original file (incl. the hardcopied ''20K'' rows):

Sub Run_EBS()

Sheets("MAINTENANCE").Range("I1") = 1
Sheets("MAINTENANCE").Calculate
ThisWorkbook.Sheets("OPERATIONS").Calculate

Application.Calculation = xlCalculationManual

j = Sheets("OPERATIONS").Range("K2").Value

For i = 2 To Sheets("OPERATIONS").Range("K1").Value

Sheets("OPERATIONS").Rows(i).range("A2:A" & i+j).Calculate
Sheets("MAINTENANCE").Rows(i).Calculate
Sheets("MAINTENANCE").Range("I1").Value = i

Next i

ThisWorkbook.Sheets("OPERATIONS").Calculate
End Sub

Even though the original formula's are present, not having to calculate each row from the get-go saves a lot of time. In the test file the speed was down to 8 seconds again! I'm testing it now in my actual file.

[StuCram]

Hi Ronnet2,

Here is my proposed solution using a few macros to process your data.

It works by determining the top and bottom rows of each group (separated by blank lines), inserting formulas to calculate the ranks and then changing the formula results to just values. An extra hidden column (J) is used to assist getting the combined ranks. In addition, any ties are flagged and the group is sorted.

Details are in the attached Excel file.
Reward test with Macros by Stu.xlsm

Hope this is some help.
- Stu

PS Like you, I like to make macros for such tasks and enjoyed doing this.