Excel Formulas for Averaging Energy Consumption Excluding Peak Hours

Energy managers often struggle to isolate true baseline usage from highly volatile peak-hour consumption spikes. While standard utility funding sources and capital budgets can offset efficiency upgrades, securing these approvals requires rigorous data validation. Federal efficiency grants specifically reward organizations that can prove precise off-peak savings. However, a key stipulation of frameworks like the ASHRAE Level II guidelines is that peak hours-typically 12 PM to 6 PM-must be strictly filtered out of your baseline average.

Below, we will demonstrate the exact Excel AVERAGEIFS formula to exclude these peak hours and accurately calculate your baseline energy consumption.

Managing and analyzing energy consumption data is a critical task for facilities managers, sustainability officers, and financial analysts alike. With utility companies increasingly charging premium rates during high-demand periods-known as "peak hours"-organizations must separate their baseline (off-peak) energy use from peak consumption. This process, often called peak-shaving analysis or off-peak baseline calculation, allows businesses to identify cost-saving opportunities and measure the effectiveness of energy efficiency initiatives.

However, raw smart meter data typically arrives in massive interval logs (such as 15-minute, 30-minute, or hourly intervals) containing combined date and time stamps. Filtering out specific peak-hour windows to calculate a true off-peak average can be challenging in Microsoft Excel if you don't know the right formulas. This comprehensive guide will walk you through several highly effective Excel formulas to average your energy consumption while filtering out peak hours, ranging from modern dynamic array formulas to classic backwards-compatible solutions.

Understanding the Data Structure

Before diving into the formulas, it is essential to understand how Excel handles temporal data. In Excel, dates are stored as whole integers (e.g., January 1, 1900, is 1, and January 1, 2024, is 45292), while times are stored as fractional decimals of a 24-hour day. For example, 12:00 PM (noon) is stored exactly as 0.5, and 6:00 PM is 0.75.

In a typical energy log export, you will have two primary columns:

• Column A (Timestamp): Date and time combined (e.g., 2024-10-24 14:30:00).
• Column B (Energy Consumption): Measured in kilowatts (kW) or kilowatt-hours (kWh).

Our objective in this guide is to calculate the average consumption for all records excluding peak hours. For demonstration purposes, we will define peak hours as 12:00 PM (12:00) to 6:00 PM (18:00).

Method 1: The Modern Excel Solution (AVERAGE & FILTER)

If you are using Microsoft 365, Excel 2021, or Excel for the Web, you have access to dynamic array functions. The combination of AVERAGE and FILTER is the cleanest, most elegant way to solve this problem without modifying your raw dataset.

Because your timestamp column contains both date and time, we must isolate the time portion. We do this mathematically using the MOD function with a divisor of 1 (e.g., MOD(Timestamp, 1)), which strips away the whole number (date) and leaves only the fractional remainder (time).

The Formula:

=AVERAGE(FILTER(B2:B1000, (MOD(A2:A1000, 1) < TIME(12,0,0)) + (MOD(A2:A1000, 1) > TIME(18,0,0))))

How It Works:

• MOD(A2:A1000, 1): Extracts only the time component from the datetime range.
• TIME(12,0,0) and TIME(18,0,0): Safely represents 12:00 PM and 6:00 PM in Excel's native time format.
• The Addition Operator (+): In Boolean logic within array formulas, the + symbol acts as an OR operator. This tells Excel to keep rows where the time is either earlier than 12:00 PM OR later than 6:00 PM (effectively filtering out the 12:00–18:00 peak block).
• FILTER: Extracts only the kWh values from Column B that meet the Boolean condition defined above.
• AVERAGE: Computes the arithmetic mean of the filtered, off-peak values.

Method 2: The Helper Column Approach (Best for All Excel Versions)

If you are working with older versions of Excel (such as Excel 2016 or 2019) or need to share your workbook with external stakeholders who might use older software, the Helper Column method is the most reliable and transparent solution.

This method breaks the logic into a simple step-by-step process: identify peak hours in a new column, and then use a standard AVERAGEIF formula.

Step 1: Create the Helper Column

In Column C, next to your energy data, label the header as "Is Peak?". In cell C2, enter the following formula and drag it down to the bottom of your dataset:

=IF(AND(MOD(A2, 1) >= TIME(12,0,0), MOD(A2, 1) <= TIME(18,0,0)), "Peak", "Off-Peak")

This formula checks if the time in column A falls between noon and 6:00 PM inclusive. If it does, it marks the row as "Peak"; otherwise, it marks it as "Off-Peak".

Step 2: Calculate the Off-Peak Average

Now, in your summary dashboard or a separate cell, you can easily average your data using the basic AVERAGEIF function:

=AVERAGEIF(C2:C1000, "Off-Peak", B2:B1000)

This approach is highly visual, allowing you to easily sort, filter, or build PivotTables based on the "Peak" vs "Off-Peak" categorization.

Method 3: The Legacy Array Formula (For Complex, Multi-Condition Filtering)

If you are using an older version of Excel but cannot add a helper column due to sheet design restrictions, you can fall back on a classic CSE (Ctrl+Shift+Enter) array formula. This formula uses mathematical logic to bypass the limitations of AVERAGEIFS, which cannot natively handle split-range "OR" conditions.

The Formula:

=AVERAGE(IF((MOD(A2:A1000, 1) < TIME(12,0,0)) + (MOD(A2:A1000, 1) > TIME(18,0,0)), B2:B1000))

Note: If you are using Excel 2019 or older, you must press Ctrl + Shift + Enter instead of just Enter after typing this formula. When done correctly, Excel will wrap the formula in curly braces { }.

Advanced Scenario: Filtering Weekends Out of Peak Calculations

In the real world, commercial utility tariffs are rarely simple. Many commercial energy contracts only apply peak demand pricing on weekdays (Monday through Friday). Saturday and Sunday are often treated entirely as off-peak, regardless of the time of day.

To calculate a highly accurate off-peak average under these terms, we must incorporate the WEEKDAY function into our calculations. In Excel, WEEKDAY(Date, 2) returns 1 for Monday through 7 for Sunday.

The Advanced Office 365 Formula:

=AVERAGE(FILTER(B2:B1000, (WEEKDAY(A2:A1000, 2) > 5) + ((WEEKDAY(A2:A1000, 2) <= 5) * ((MOD(A2:A1000, 1) < TIME(12,0,0)) + (MOD(A2:A1000, 1) > TIME(18,0,0))))))

Deconstructing the Advanced Logic:

This formula relies on strict mathematical rules of Boolean operators where multiplication (*) means AND, and addition (+) means OR:

• (WEEKDAY(A2:A1000, 2) > 5): Evaluates to TRUE for Saturdays and Sundays. Since they are always off-peak, these rows are kept unconditionally.
• (WEEKDAY(A2:A1000, 2) <= 5) * (...): If it is a weekday, Excel evaluates the inner bracket to check if the time is outside of peak hours (before 12:00 PM or after 6:00 PM).

Summary Comparison of Methods

Best Practices for Energy Data Analysis in Excel

To ensure your energy averages remain accurate and your workbooks perform efficiently, keep these best practices in mind:

• Avoid Hardcoding Values: Instead of typing TIME(12,0,0) directly inside your formulas, reference dedicated parameter cells (e.g., $E$2 for Peak Start, and $F$2 for Peak End). This allows you to update your analysis instantly if your utility tariff structure changes.
• Handle Null and Zero Values: Smart meter dropouts can sometimes register as blank cells or exactly zero. To prevent zeroes from dragging down your average, you can append a non-zero criteria to your filter: *(B2:B1000 > 0).
• Optimize Performance: If your dataset exceeds 100,000 rows (common for 15-minute intervals across several years), array-based calculations like FILTER and MOD can slow down your workbook. In such cases, converting your raw data into an Excel Table (Ctrl + T) or using Power Query to parse times and filter rows before loading is highly recommended.

Conclusion

Filtering out peak hours to obtain an accurate off-peak energy average doesn't have to be a manual, tedious chore. By utilizing the MOD function to strip time components from timestamps, and leveraging dynamic functions like FILTER or clean helper columns, you can build dynamic, audit-ready energy models. Implement these formulas in your next utility analysis to unlock deeper insights into your organizational carbon footprint and power bill optimization.