Function Problems

How to Return the 5 Largest and Smallest Values by Month in Excel

Huda Qurayshi Oct 10, 2026 869 views

Question details

The user needs to extract the top five and bottom five transaction debit values for a specific month, while excluding certain categories and including the transaction date and description in the results.

Return the 5 Largest and Smallest Values by Month in Excel

Product: Excel
Device & OS: not provided
Scenario: Performing monthly financial data analysis where it is necessary to isolate extreme values (highest and lowest debits) for a specific reporting period, while simultaneously omitting internal transfer categories.
Observed behavior: Standard sorting or basic MIN/MAX functions cannot easily handle multi-condition filtering, column combination (cash and online debits), and retaining associated row data (date and description) all at once without manual extraction.

Before you start

Ensure your version of Excel or spreadsheet software supports dynamic array functions like FILTER, LET, SORT, and TAKE. It is highly recommended to convert your data range into a formal Table so formulas update automatically as new transactions are added.

Solution 1Recommended

Use Dynamic Array Functions (LET, FILTER, HSTACK, SORT, TAKE)

This all-in-one formula filters the dataset by month and category, sums the required debit columns, sorts the results, and extracts the top or bottom 5 rows including their dates and descriptions.

Dynamic arrays allow you to process multi-step data manipulation in a single cell. Using the LET function helps assign names to calculation results (like the filtered data), making the formula run faster and easier to read.

Define the target date

Set up a reference cell, for example, H1, with the desired month and year (e.g., entered as a date and formatted as 'mm-yy') to act as the filter criteria.

Construct the formula for the 5 largest values

Select the cell where you want the top 5 results to appear (e.g., H5). Enter the following formula: =TAKE(SORT(LET(ftr,FILTER(A2:F11,((F2:F11<>"TRNA")*(F2:F11<>"CAWD")*(TEXT(A2:A11,"mm-yy")=TEXT(H1,"mm-yy")))),HSTACK(CHOOSECOLS(ftr,1,2),BYROW(ftr,LAMBDA(a,SUM(CHOOSECOLS(a,4,5)))))),3,-1),5)

Understand the formula components

The FILTER function removes unwanted categories ('TRNA', 'CAWD') and matches the month in H1. HSTACK combined with CHOOSECOLS pulls columns 1 and 2 (Date and Description). BYROW and SUM add columns 4 and 5 (the debits) together. Finally, SORT orders them descending by the 3rd column, and TAKE returns exactly 5 rows.

Construct the formula for the 5 smallest values

To get the lowest debits, select a new cell (e.g., H11) and paste the exact same formula, but change the SORT order argument from -1 (descending) to 1 (ascending). The formula is: =TAKE(SORT(LET(ftr,FILTER(A2:F11,((F2:F11<>"TRNA")*(F2:F11<>"CAWD")*(TEXT(A2:A11,"mm-yy")=TEXT(H1,"mm-yy")))),HSTACK(CHOOSECOLS(ftr,1,2),BYROW(ftr,LAMBDA(a,SUM(CHOOSECOLS(a,4,5)))))),3,1),5)

Use Dynamic Array Functions (LET, FILTER, HSTACK, SORT, TAKE)

Array Spilling: Since this is a dynamic array formula, ensure there are enough empty rows and columns below and to the right of the cell where you enter the formula, or you will receive a #SPILL! error.

Solution 2

Use Helper Columns for Legacy Versions

If your spreadsheet software does not support dynamic arrays, use helper columns to combine conditions and rank the values using traditional functions like LARGE and INDEX/MATCH.

Effortlessly Analyze Complex Financial Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER, SORT, BYROW, and TAKE out of the box. You can easily process complex transactional data, automatically extract top and bottom records, and streamline your financial reporting without relying on cumbersome helper columns.

1. Open your data file: Launch WPS Spreadsheet and open your transactional data workbook.
2. Select the reporting cell: Click on the target cell where you want your summary report to begin.
3. Input the dynamic array formula: Type or paste the LET and FILTER formula to automatically extract, sort, and slice your data.
4. Let the results spill: Press Enter to instantly spill the top 5 largest or smallest results across the neighboring cells seamlessly.

Full compatibility with modern Microsoft Excel dynamic array formulas and functionsCompletely free to use with a lightweight and fast installation processAdvanced data manipulation tools perfectly suited for financial and transactional analysisCross-platform availability for Windows, Mac, Linux, and mobile devices

microsoft office alternative - wps office

Frequently Asked Questions

Why does my dynamic array formula return a #CALC! error?

The #CALC! error typically occurs when the FILTER function finds no records matching your specified criteria (for example, if no transactions match the selected month or if all transactions belong to the excluded categories). You can fix this by adding a final 'if_empty' argument to the FILTER function to handle empty results gracefully.

Can I return 10 values instead of 5?

Yes. You can easily modify the number of returned values by changing the last argument in the TAKE function. For example, simply change TAKE(..., 5) at the end of the formula to TAKE(..., 10).

How do I sort the results by date instead of the debit value?

In the SORT function part of the formula, which is structured as SORT(array, sort_index, sort_order), change the 'sort_index' argument. The combined array outputs Date as column 1, Description as column 2, and Debit as column 3. Change the sort_index from 3 to 1 to sort by Date.

Excel & Data Problems