Forum Discussion

torpidwanderer's avatar
torpidwanderer
Copper Contributor
Jul 22, 2022

How to dynamically set the data range for the LARGE function (or any function really)?

My data looks like this:

 

 

I am trying to have the data range or "array" dynamically set for the LARGE function (returns the kth largest value in a data set) as I add new column data to the right 

 

Right now, I have a group of cells referencing the 1st, 2nd, 3rd largest values in column C via the large function:

  • =LARGE(Sheet2!$C$4:$C$1048576,1) -> returns the largest value in column E -> 1,096
  • =LARGE(Sheet2!$C$4:$C$1048576,2) -> returns the second largest value in column E -> 732
  • =LARGE(Sheet2!$C$4:$C$1048576,3) -> returns the third largest value in column E -> 619

Column C data is employment data for a specific month and year (for June 2022). I will be adding more recent employment data after column C for July 2022 (column D), August 2022 (column E), etc.

 

It would be great if the 3 LARGE functions listed above could reference the latest month/year data rather than being static. So if I added new July 2022 data, it would stop referencing column C and reference column D instead and so on...

 

Any advice?





 

excel
Formulas and Functions

2 Replies

  • flexyourdata's avatar
    flexyourdata
    Iron Contributor
    Jul 22, 2022

    Consider formatting your data as a Table. 

     

    Place your cursor in your current range and use Ctrl+T to convert it to a table. 

     

    Use the Table Design Tab to select or create a Table Style that is to your liking. 

     

    Give your table a name in the Table Name box in the Table Design tab of the ribbon. 

     

    As you add new columns to the right of this sheet, it will automatically expand the table to include the new columns.

     

    Suppose your table is called product_sales:

     

     

    You can now refer to the table and columns by name and as such always retrieve the right-most column with this formula:

     

    =INDEX(product_sales,,COLUMNS(product_sales))

     

    Further, if you want to get the top 3 largest values and their products from the latest month, you can use this:

     

    =LET(
  t,product_sales[#All],
  header,INDEX(t,1,),
  latest,FILTER(t,(header="Product name")+(COLUMN(header)=MAX(COLUMN(header)))),
  best_latest,FILTER(latest,INDEX(latest,,2)>=LARGE(INDEX(latest,,2),3)),
  best_latest
)

     

    To break this down:

     

    1. We assign the shorter name "t" to the table containing your data. 

    2. We take the first row from "t" and give it the name "header".

    3. We FILTER "t" for those columns where either the first row contains "Product name" or (indicated by the +) where the column number of the header is equal to the maximum column number in the whole header. This is equivalent to saying it is the right-most column. We call the result of this first filter "latest". It is a two-column array.

    4. We apply a second FILTER to filter "latest" for those rows where the value in the second column is greater than or equal to the 3rd largest value in that column. The result is a two-column, four-row array where the first column contains the product name, the second column contains the value for that month and the first row contains the original column header. 

     

    It looks like this:

     

     

    This formula will always get the 3 rows with the largest values in the right-most column of the table. 

Resources