Hi,
I am trying to reduce the calculation speed of my growing workbook and would like to know which of the below formulae would be more efficient speedwise (if I were to replace all the source data with new data)
Note that while the table below is 3x3, in reality it is closer to 100x3.
The arrays in Sheet 1 that are being referred to are usually 4000-5000 rows long but may later on grow to 10000 rows.
Scenario1:
Sheet "Rank"
Where:
ActorID is a simple value
Valid is =COUNTIFS(Sheet1!$AC:$AC,$T2,Sheet1!$AI:$AI,">"&Output!$B$2+11)>0
Cases6M is =IF($U2=TRUE,COUNTIFS(Sheet1!$AC:$AC,$T2,Sheet1!$AI:$AI,">"&Output!$B$2+11),"")
Sheet "Output"
=VLOOKUP($J$2,Rank!$T:$V,3,FALSE)
OR
Scenario 2:
Same as Scenario 1, but in Sheet "Output"
=COUNTIFS(Sheet1!$AC:$AC,$J$2,Sheet1!$AI:$AI,">"&$B$2+11)
My creeping suspicion is that Scenario 2 would be more efficient, though I was hoping to maximize efficency by not having to calculate the same thing over and over again and instead have it calculate once and everything else draw from it.
Bookmarks