If the group is changed, the formulas should calculate new results.
For convenience,group(B5:B16) andvalue(C5:C16) arenamed ranges.
This means we need to create our own logic to apply conditions in a separate step.
There are two basic ways to approach this problem.
Both approaches are explained below.
LARGE function
TheLARGE functioncan be used to return the nth largest value in a set of data.
One way to apply criteria is with the FILTER function, as described in the next section below.
This array is used by FILTER to extract matching values.
Group B values become FALSE since they fail the logical test.
This array is returned directly to the LARGE function as thearrayargument.
With FILTER, the generic formula looks like this:
Wherecriteria1andcriteria2represent 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.
The IF…
Related videos
How to get nth values with SMALL and LARGE
