How to Calculate Daily Maximum, Minimum, and Average Temperatures in Excel
Question details
The user needs to extract daily summary statistics (maximum, minimum, and average) from a list of hourly temperature readings.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Summarizing a large dataset of hourly time-series temperature readings by day to find specific highs, lows, and averages.
- Observed behavior
- Calculating specific maximum, minimum, and average daily values from a consolidated list containing dates, times, and temperature values.
Before summarizing your temperature records, ensure that your date column is formatted as valid dates and your temperature column contains numerical values rather than text to prevent calculation errors.
Use a PivotTable to Summarize Daily Temperatures
A PivotTable is the quickest and most efficient way to summarize large datasets without writing complex formulas.
PivotTables automatically group identical dates and allow you to apply multiple types of calculations to the same numerical field simultaneously.
Highlight your entire data range, including the column headers for Date, Time, and Temperature.
Navigate to the 'Insert' tab on the ribbon, click 'PivotTable', and choose to place it on a New Worksheet.
In the PivotTable Fields pane, drag the 'Date' field down into the 'Rows' area.
Drag the 'Temperature' field into the 'Values' area three separate times.
Click the dropdown arrow on each Temperature field in the Values area, select 'Value Field Settings', and change the calculation types to 'Max', 'Min', and 'Average' respectively.

Calculate Daily Temperatures Using Conditional Formulas
If you prefer a dynamic template or need to reference the summarized data elsewhere in your workbook, use the MAXIFS, MINIFS, and AVERAGEIFS functions.
Effortlessly Analyze Temperature Data in WPS Spreadsheet
WPS Spreadsheet offers full support for advanced PivotTables and statistical formulas like MAXIFS, MINIFS, and AVERAGEIFS, making data analysis incredibly smooth and efficient.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your hourly temperature records.
- 2. Insert a PivotTable: Navigate to the Insert tab and click 'PivotTable' to quickly summarize your data.
- 3. Configure the fields: Drag Date to Rows, and Temperature to Values. Right-click the values to easily switch between Max, Min, and Average calculations.
- 4. Use advanced formulas: Alternatively, type =MAXIFS, =MINIFS, or =AVERAGEIFS to summarize specific data ranges dynamically.

Frequently Asked Questions
Why do my MAXIFS and MINIFS formulas return an error or zero?
This usually happens if your dates or temperatures are accidentally formatted as text. Ensure your temperature column is strictly formatted as numbers, and your dates are recognized by Excel as valid date formats. Also, ensure your data ranges in the formula are exactly the same size.
Can I use MAXIFS and MINIFS in older versions of Excel?
MAXIFS and MINIFS are available in Excel 2019, Microsoft 365, and WPS Office. If you are using Excel 2016 or older, you must use array formulas instead, such as =MAX(IF(A2:A1000=E2, C2:C1000)), and confirm the formula by pressing Ctrl+Shift+Enter.
How do I remove long decimal places from the average temperature calculation?
You can format the cells by selecting them, going to the Home tab, and clicking the 'Decrease Decimal' button. Alternatively, wrap your AVERAGEIFS function in a ROUND function, like this: =ROUND(AVERAGEIFS($C$2:$C$1000,$A$2:$A$1000,E2), 1) to round it to one decimal place.
Does AVERAGEIFS include blank cells or zero values?
AVERAGEIFS automatically ignores completely blank cells in its calculation. However, if a cell contains a zero (0), it will be included in the average. If you need to exclude zeros, you can add another criteria to the formula: =AVERAGEIFS($C$2:$C$1000,$A$2:$A$1000,E2,$C$2:$C$1000,">0").




