If the group is changed, the formulas should calculate new results.
This means we need to create our own logic to apply criteria.
There are two basic ways to approach this problem.
In older versions of Excel, you’re able to use the IF function in an array formula.
Both approaches are explained below.
Excel Table
For convenience, all data is in anExcel Tablenameddatain the range B5:D16.
If you are new to Excel Tables,this article provides an overview.
One way to apply criteria is with the FILTER function, as described in the next section.
This array is used by FILTER to retrieve matching data.
The values in group B become FALSE when they fail the logical test.
This array is returned directly to the LARGE function as thearrayargument.
Multiple criteria
To apply multiple criteria, you’re free to extend the formula withboolean logic.
With FILTER, the generic formula looks like this:
Wherecriteria1andcriteria2are expressions to test specific conditions.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
More than one condition can be tested by nesting IF functions.
