Hi @
anshul.malhotra
The limitation to one tech, one day was deliberate. I think I can modify the formula so that it will read over records associated with other techs and deal with multiple days, appended as in the large data sample.
Ultimately though, I believe better solutions can be achieved by importing the data and filtering and partitioning it to facilitate more efficient calculation. That is as much part of any potential solution as is the formula itself.
For example, if we assume a single calculation can be complete within 2ms, by the time one is considering 40 tech over 30 days this will rise to 24s for the 1200 independent calculations. If, instead solving independent calculations, you append datasets and merge the contents you might expect a further factor of 1200 to result from the fact that each calculation on each row [the COUNTIFS and SMALL functions] is now running through 60000 rows rather than 50.
That gives you a target calculation time of 8hrs rather than 24s.
This could be drastically reduced by building a sorted table of finish times, in which case the O(n²) calculations could be replaced by approximate LOOKUP or MATCH, O(
n.ln₂
n). This would, however, require separate manual operations of duplicate and sort to be kept synchronised with the source data. Something you would prefer to avoid?
.