Find the Two Highest and Lowest Hourly Values per Day in Excel
Question details
Find the two highest and two lowest hourly readings for each day in a dataset spanning several months.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Each row contains one date in column A and 24 hourly readings in columns B through Y.
- Observed behavior
- The user needs four ranked values for each date: the highest, second-highest, lowest, and second-lowest readings.
Check that each date occupies one row, the 24 readings are numeric values in columns B:Y, and columns Z:AC are available for results. Each row needs at least two numeric readings for all four formulas to work.
Use LARGE and SMALL formulas for each day
Use four formulas to rank the hourly readings in each row, then fill them down for all dates.
In cells Z1:AC1, enter Max 1, Max 2, Min 1, and Min 2, respectively.
In Z2, enter =LARGE(B2:Y2,1). In AA2, enter =LARGE(B2:Y2,2).
In AB2, enter =SMALL(B2:Y2,1). In AC2, enter =SMALL(B2:Y2,2).
Select Z2:AC2 and drag the fill handle down to the last date row. Each row's formulas will use that day's readings.

Create a spilling summary with a dynamic-array formula
Use one formula to return dates and all four ranked readings in a separate summary table.
Find Daily Highs and Lows in WPS Spreadsheets
Use LARGE and SMALL formulas in WPS Spreadsheets to rank each day's hourly readings, then fill the formulas down across your dataset.
- 1. Open the workbook: Open the Excel file in WPS Spreadsheets and check that dates are in column A and hourly readings are in columns B:Y.
- 2. Add headers: Enter Max 1, Max 2, Min 1, and Min 2 in Z1:AC1.
- 3. Enter the formulas: Enter =LARGE(B2:Y2,1) in Z2, =LARGE(B2:Y2,2) in AA2, =SMALL(B2:Y2,1) in AB2, and =SMALL(B2:Y2,2) in AC2.
- 4. Apply them to every date: Select Z2:AC2 and drag the fill handle down to the final data row.

Frequently Asked Questions
What if my hourly readings are in different columns?
Replace B2:Y2 in each formula with the cells containing the 24 readings for the first date row, then fill the formulas down.
Why does a formula return #NUM!?
LARGE and SMALL return #NUM! when the requested rank exceeds the number of numeric values. Check that the row contains at least two numeric readings.
Do these formulas return distinct values?
No. They rank individual readings, so equal readings can occupy both the first and second positions.
Why does the dynamic-array formula show #SPILL!?
Clear any occupied cells in the intended output area, then select the formula cell to check whether the results can spill into the available space.
Can I use these formulas if each hour is on a separate row?
The row-based formulas assume all readings for one date are in B:Y on the same row. First arrange each day's readings across one row, or use a method that groups readings by date.




