SORTBY and TAKE for dynamic top N
IntermediateTop N reports are a common way to focus attention on the products that matter most. With dynamic array formulas, you can build a ranking that updates automatically when the source data changes or when the requested result count changes.
SORTBY sorts one array by another array, which helps when the report you want to return is narrower than the table you want to rank. TAKE then trims that sorted result to a specific number of rows, which is useful for dashboards that need a flexible top-performers view.
Here, the sales table contains product revenue by item. Cell B1 stores the requested result count. Build the result so it returns only Product and Revenue, stays sorted from highest revenue to lowest revenue, and resizes automatically when the value in B1 changes.
Your task:
Enter one spill formula in A18 that produces the top N product list from the source data. You will need to decide how to reduce the table to the two output columns before sorting it, and then limit the final result with the parameter in B1.
Need some help?
Hint 1
Start by using CHOOSECOLS to keep only the Product and Revenue columns from the source table.
Hint 2
Use SORTBY to sort those selected columns by the original Revenue column in descending order. The sort by range can come from the source table even if the returned array has only two columns.
Hint 3
Wrap the sorted result in TAKE, and use B1 as the rows argument so the output grows or shrinks with the Top N setting.
SORTBY and TAKE for dynamic top N
IntermediateTop N reports are a common way to focus attention on the products that matter most. With dynamic array formulas, you can build a ranking that updates automatically when the source data changes or when the requested result count changes.
SORTBY sorts one array by another array, which helps when the report you want to return is narrower than the table you want to rank. TAKE then trims that sorted result to a specific number of rows, which is useful for dashboards that need a flexible top-performers view.
Here, the sales table contains product revenue by item. Cell B1 stores the requested result count. Build the result so it returns only Product and Revenue, stays sorted from highest revenue to lowest revenue, and resizes automatically when the value in B1 changes.
Your task:
Enter one spill formula in A18 that produces the top N product list from the source data. You will need to decide how to reduce the table to the two output columns before sorting it, and then limit the final result with the parameter in B1.
Need some help?
Hint 1
Start by using CHOOSECOLS to keep only the Product and Revenue columns from the source table.
Hint 2
Use SORTBY to sort those selected columns by the original Revenue column in descending order. The sort by range can come from the source table even if the returned array has only two columns.
Hint 3
Wrap the sorted result in TAKE, and use B1 as the rows argument so the output grows or shrinks with the Top N setting.