MIN Function Without Using Array

Hi Neyme,

Since you use excel 2010 you can take advantage of AGGREGATE function. Although this is an array formula, it does not require CTRL+SHIFT+ENTER confirmation

=IF(D2="","",AGGREGATE(15,6,B$2:B$20000/(($D$2:D$20000=D2)),1))

There many thing should be considered when it comes to performance improvement so here a link that you might find useful.

https://msdn.microsoft.com/en-us/lib...or=-2147217396

The AGGREGATE function is actually can be viewed as Swiss Knife and it was first introduced in Excel 2010. It's more like a collection of functions which include 19 different functions.
Here is more info

https://support.office.com/en-sg/art...f-04346623d79d



Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

One thing that I frequently see in these kind of questions is "duplicated effort" (see discussion in AlKey's first link to performance improvement. First rule under "speeding up calculations and reducing obstructions" about 3/4 of the way down). In your sample file, for example, there are 120 instances of "retlatency_rz0402015". For each instance, Excel must repeat the same calculation. One key step to speeding up the calculation will be to eliminate this redundancy. I would probably suggest something like:

Build a lookup table:

1) Identify each unique entry in column D. I don't off hand know how to best do this. A quick look down the list suggests that there are three unique values in this list. Enter these unique entries in the left column of the lookup table.
2) For each entry in the lookup table, use your MIN((IF(...)) array formula.

Now, For each entry in column C in the main table, you can enter a simpler lookup function (=VLOOKUP(D4,lookup_table,2,false)) that will return the previously calculated time stamp.

Expanding your sample down to ~4700 rows, the original functions took 13 to 15 seconds to calculate. This lookup table approach was near instantaneous.