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)
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.
ActorID Valid Cases6M
20 TRUE 217
11 TRUE 215
45 TRUE 155
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),"")
Same as Scenario 1, but in Sheet "Output"
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.