Function Problems

Find the Two Highest and Lowest Hourly Values per Day in Excel

Emma BrownEmma Brown Nov 18, 2026 869 views

Question details

Find the two highest and two lowest hourly readings for each day in a dataset spanning several months.

Find the Two Highest and Lowest Hourly Values per Day in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Add result headers

In cells Z1:AC1, enter Max 1, Max 2, Min 1, and Min 2, respectively.

2
Find the two highest readings

In Z2, enter =LARGE(B2:Y2,1). In AA2, enter =LARGE(B2:Y2,2).

3
Find the two lowest readings

In AB2, enter =SMALL(B2:Y2,1). In AC2, enter =SMALL(B2:Y2,2).

4
Fill formulas down

Select Z2:AC2 and drag the fill handle down to the last date row. Each row's formulas will use that day's readings.

Use LARGE and SMALL formulas for each day
Repeated values: LARGE and SMALL rank readings, not distinct values. If the highest or lowest reading occurs more than once, it can appear in both results.
Analyze daily readings in WPS Spreadsheets

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. 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. 2. Add headers: Enter Max 1, Max 2, Min 1, and Min 2 in Z1:AC1.
  3. 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. 4. Apply them to every date: Select Z2:AC2 and drag the fill handle down to the final data row.
Find the top two and bottom two readings for each dayOpen and save Microsoft Excel XLSX and XLS filesFill formulas down across months of dataUse familiar spreadsheet formulas and controls
microsoft office alternative - wps office

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.