If the group is changed, the formulas should calculate new results.
There are two basic ways to approach this problem.
In older versions of Excel, you could use the IF function in a traditional 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.
A good 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 SMALL function as thearrayargument.
With FILTER, the generic formula looks like this:
Wherecriteria1andcriteria2are logical expressions to test specific conditions.
from a set of numeric data.
