I am trying to understand why these two scenarios are yielding different results. The aggregate formula performs quicker which performance is preferable, but I don't know if it is filtering correctly. Cell C2 filters the table by the selected alloy, therefore, I figured the aggregate should filter out the hidden rows in the table so the filter function only has one criteria versus two.
=BYROW(gauge_list,
LAMBDA(gauge,
AGGREGATE(16,3,
FILTER(data[Stress],(data[Gauge]=gauge)),
0.99)
))
=BYROW(gauge_list,
LAMBDA(gauge,
PERCENTILE.INC(
FILTER(data[Stress],(data[Gauge]=gauge)*(data[Alloy]=$C$2)),
0.99)
))
=BYROW(gauge_list,
LAMBDA(gauge,
AGGREGATE(16,3,
FILTER(data[Stress],(data[Gauge]=gauge)),
0.99)
))
=BYROW(gauge_list,
LAMBDA(gauge,
PERCENTILE.INC(
FILTER(data[Stress],(data[Gauge]=gauge)*(data[Alloy]=$C$2)),
0.99)
))