Seeing Double:
Charting Two Variables Against A Common Third

(Tip of the Month for August 2004)

One of the best ways to correlate two variables (such as power use and temperature) that both vary over time is to chart them using a secondary Y-axis.

You see such charts all the time (see Figure 1), but many energy practitioners think they need special charting or energy software to achieve this result. In fact, anyone with Microsoft Excel™ 2000 (or later) may do so.

The Trick: First Do It Wrong

Start by setting up an Excel worksheet having three rows (see Figure 2):

the first row is the time increments you wish to chart (e.g., quarter hours)
the next row is your first variable (e.g., quarter-hourly electric demand in kW)
the last row is your second variable (e.g., quarter-hourly temperature in deg F).

Select all three rows and click on the Excel Chart Wizard. Choose your desired Chart type and sub-type (e.g., Line). Follow the steps to create the chart seen in Figure 3.

But where's the second Y-axis showing temperature? Now comes the magic.

Format The Data Series

Right click on the lower curve to see a list of options that starts with "Format Data Series...." Choose that phrase, and a dialog box will appear entitled "Format Data Series" (see Figure 4). Click on the "Axes" tab and see a pair of radio buttons and the words "Plot series on." Choose "secondary axis." The thumbnail chart in the dialog box will change as the temperature curve moves up and a right side Y-axis with a temperature scale appears. Click OK.

A title may be added to the second Y-axis by clicking on the "Chart" command (NOT the Chart Wizard), clicking on the "Titles" tab, and filling in the field marked "Second Value (Y) axis."

Time To Get Fancy

The resulting graph shows how electric demand and temperature vary with time. But we don't see much of a correlation: the sudden rise in kW in the middle of the day occurs even though the temperature rises only gradually. The rise in kW occurred when a second electric chiller was started and ran for about 8 hours. But was that second chiller really needed?

By charting temperature and kW on both the subject weekday and another weekday, Figure 5 was created. To add the secondary Y-axis for temperature, we formatted one of the two temperature data series (i.e., curves) as discussed above, and then repeated that process with the second temperature data series so that it too was following the temperature (not demand) scale.

An Anomaly Is Found

We now see two similar temperature curves, but two rather different kW load profiles. By consulting our legend, we see that the big kW bump occurred on June 5 (dashed blue line). But on June 11 (also a weekday), no such kW bump appears (see solid blue line). Looking at the temperature curves on both days shows that it was cooler on June 5 (dashed red line), so why was a second chiller run when, on a hotter day (June 11, the solid red line) it was not needed? Both days were weekdays, neither was a holiday, and no changes to internal loads (e.g., an event taking place in the building) occurred on either day.

Exposing The Problem

A bit of engineering detective work found that an experienced operator ran the chiller plant on June 11, but a new plant operator was handling the chillers on June 5 (the cooler day). To avoid cooling complaints at his old job, the new operator had developed a habit of simply starting up all chillers at the beginning of his shift and letting them run, instead of dispatching them based on internal building temperatures (which required occasional monitoring of readings on the energy management system). He saw no reason to alter his habits in his new position. This overly simplistic work process cost the building thousands of dollars in extra monthly peak demand charges because the second chiller was not needed any time during June.

When confronted about the big kW bump seen on the June 5 load profile, the operator tried to claim that "that was a really hot day and we needed all the chillers running." But the June 11 data shows that an even hotter day didn't require use of the second chiller. The operator was then told to be more conscious of the cost of his actions - if he wanted to keep his new job.