Calculation Issues

How to Calculate Daily Maximum, Minimum, and Average Temperatures in Excel

Natalie TaylorNatalie Taylor Nov 2, 2026 869 views

Question details

The user needs to extract daily summary statistics (maximum, minimum, and average) from a list of hourly temperature readings.

How to Calculate Daily Maximum, Minimum, and Average Temperatures in Excel
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 you start

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.

Solution 1Recommended

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.

1
Select your dataset

Highlight your entire data range, including the column headers for Date, Time, and Temperature.

2
Insert a PivotTable

Navigate to the 'Insert' tab on the ribbon, click 'PivotTable', and choose to place it on a New Worksheet.

3
Group by Date

In the PivotTable Fields pane, drag the 'Date' field down into the 'Rows' area.

4
Add Temperature fields

Drag the 'Temperature' field into the 'Values' area three separate times.

5
Change summary calculations

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.

Use a PivotTable to Summarize Daily Temperatures
Instant Summary: Your PivotTable will instantly group the hourly records into daily rows, displaying the highest, lowest, and average temperatures side by side.

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. 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your hourly temperature records.
  2. 2. Insert a PivotTable: Navigate to the Insert tab and click 'PivotTable' to quickly summarize your data.
  3. 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. 4. Use advanced formulas: Alternatively, type =MAXIFS, =MINIFS, or =AVERAGEIFS to summarize specific data ranges dynamically.
100% compatible with Microsoft Excel (.xlsx) formatsRobust PivotTable features for instant multi-layered data summariesBuilt-in function wizard to easily construct complex conditional formulasLightweight, fast-loading, and completely free to use
microsoft office alternative - wps office

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").