Scatter plotting of one variable against another (e.g., daily or monthly fuel use vs. average temperature or degree-days) is one of the mainstays of heating/cooling energy analysis. One of its limitations - finding the day/date when apparent anomalies occurred - has been overcome through the use of an Excel™ macro.

Heating/cooling energy use is generally proportional to the difference between indoor and outdoor temperatures. To describe this relationship, spreadsheets such as Excel will calculate a "best fit" equation and display a linear curve (i.e., a "trendline") conforming to it. Figure 1 shows the relationship between daily fuel use and average outdoor temperature for a two-month period. Note that many points are far from the trendline. Which indicate a problem, and which are due to acceptable factors, such as weekend closures or holidays?

To ascertain possible reasons for a point landing far from the trendline, one often needs the day and/or date at which that consumption occurred. Determining that date, however, may require scrolling through data to match up temperature and usage data with a date, a tedious and time wasting step, especially when there are many points in a chart.

Enter the 3-D Scatter Plot

An Excel-based technique now exists that embeds day and date information into each scatter plot point, eliminating the need to refer back to raw data. Called "date-embedded scatter plotting," this technique (developed by Energywiz Inc. in 2002) takes advantage of Excel's ability to create 3-D charts.

When displayed on a computer monitor, touching a chart's point with the cursor causes a text block to appear, containing not only temperature and usage data (as occurs with standard 2-D plots), but also the day of the week and date when that usage occurred. Variations due to weekend or holiday occupancy are revealed, focusing attention on days when usage is unusually high (or low) relative to the trendline.

Having immediate access to such information allows checking of workorders and/or equipment logs for that day/time to find any known changes or problems that occurred (e.g., operation/testing of an extra chiller, errant opening of an outside air damper, failure, or disconnection of controls).

This technique was used, for example, to find and reveal times when control of a heating system was occasionally lost due to misprogramming of an EMS. In Figure 2, we see that most of the weekend days (when the example facility is shut down) are encompassed by the dotted oval, but several days (in dashed circles) are weekend days, using significantly more fuel than other weekend days at the same average daily outside air temperature (OAT). Reviewing EMS logs for those days revealed the problem. To access the macro (and learn more about its pros and cons), go to, and click on the "March 2004 Tip of the Month." ES