A valuable energy tool may be sitting unused on your computer desktop.
Microsoft Excel is well suited for energy management and advanced analysis; however, it is underutilized within most businesses. Many people are not aware of a whole raft of functions that could provide greater insight and save a ton of time.
Why does this happen? In many organizations, there is no formal training on Excel. Think about how most of us learned Excel – someone showed us the basics and we learned the rest on our own. Imagine what a little training would do for productivity and the ability to perform more advanced analysis, impressing your boss or client.
Excel and Energy
When it comes to energy analysis, where does Excel fit in? Excel is certainly not a replacement for sophisticated programs such as eQUEST, TRACE, or HAP, which have established their places in energy engineering. However, Excel is a “Swiss Army Knife” that can perform many functions in the depth and breadth that is sufficient for day-to-day energy engineering and financial analysis.
In order to effectively use Excel, you need to pick up some tips, tricks, and techniques. A tip saves you time by performing a given action in less time. A trick allows to you achieve a result that is not normally possible with built-in capability. A technique is a skillful way of applying the basic features, usually by combining them in innovative ways.
The 2013 version of Excel has over 450 functions, which can seem overwhelming, but I have prioritized some of the must-know functions and features. Detailed below are three tips that you can implement today.
Tip #1: Add Units to Numbers without Changing Numbers to Text
To display a unit of measure (kWh, Therms, lb., etc) in a cell without changing the number to text, create a custom number format following the steps below:
- Select the cells containing the numbers you want to format.
- Press the key board shortcut key “ctrl +1” to bring the “format cells” dialog box.
- In the format cells choose the “custom format” option.
- In the type field, select a number format for your cells and type the unit of measure enclosed in quotes (e.g. " kWh" ) to the right of the number format.
You can use this formatted cell in any calculations, and once you have created this format, you can use it anywhere in the workbook. (See first screenshot for an example.)
Tip #2 Combine Chart Types to See Correlations
A combination chart is used to show one chart on top of another. This allows the reader to better study data sets to determine whetherany relationships exist between the two. For example, plotting the electricity consumption and CDD on the same chart helps us compare and see if there is a relationship between weather and electricity consumption. In other words, you can visualize the correlation. Excel allows you to display two different sets of data in the same chart using a secondary axis. The axis on the left is the primary axis and on the right is the secondary axis. A bar and line combination gives the best contrast.
Build a combination chart using the following steps:
- Build a chart with just a single chart type. Select both kWh and CDD data and insert a chart column chart.
- Under chart tools, go to the Format tab, and choose the CDD series in the plot area.
- Go to the design tab and change the chart type to line chart.
- Since the scale of CDD is very small compared to the kWh, all the CDD values are crowded at the bottom and no insight is gained.
- Go to the Format tab, select the series CDD, click on Format selection just beneath it and choose the secondary axis radio button.
We have successfully added a secondary axis and now you can apply some finishing touches such as changing the chart title and secondary axis label to get a professional look.
The secondary axes, or two Y axes, is also very useful when you have the same chart type with two sets of data, but the data sets vary dramatically in scale. (See second screenshot for an example.)
Tip #3 Use Index and Match Functions to Automatically Interpolate Values
Often we have tabulated values where data is tabulated at fixed intervals (for example, properties of R-12 refrigerant tabulated 5 psig intervals), and the property we are interested lies between the tabulated values. We need to interpolate to find the value. The process itself is simple but it will save time if the process is automated. This can be achieved by combining Excel’s index and match functions.
The general formula for interpolation is given by the equation accompanying the article. This is also commonly referred to as “divided difference method”. (See third and fourth screenshots.)
In our example, the data set is shown in cells S14 to T21. Let’s assume we are interested in finding the value of y for x=72 which falls between 70 and 75.
First, you can use the match function to find the position on where our value is located. In the match function, if the match type number equals 1, match returns the position of the largest array value that is less than or equal to lookup value. In our example, when x is 72, Match returns a value of 2, meaning the x1 value is at position 2 in the data set. This is shown in cell N15. Next, use this value returned by the match function in four separate index functions to return the values of x1, x2, y1, and y2. This is shown in cells N16 through N19.
Once you have the values, you can compute x2-x1, y2-y1, x-x1 that are required by our divided difference method and substitute them in the formula to obtain the value of y corresponding to the value of x. Cells N21 through N24 shows to compute these based on the values returned by the index function, and cells N27 to N30 shows to automatically compute these by combining the formulas shown in N16 to N24.
To complete the computation in a single step, all we need to do is combine all the formulas in cells N27 to N30 into one mega formula that is shown in cell N31.
Once you have this construct, you can adjust the input ranges as required and reuse this whenever you have a need for interpolation. PageBreak
Other Tricks and Techniques
Below are many other “pointers” that energy engineers and facility managers can use to speed up their analysis:
- Work with hour-by-hour bin based analysis or electrical kW interval data? Know the key board shortcuts to quickly navigate and select cells or ranges.
- Got the 15-minute kW interval data for one year from the utility that is occupying over 35,000 rows? Control the data with information functions automatically for any missing values, text values, or outliers, then use the offset function to transform the data into a 365-row data. Apply the date and day functions, and analyze the data by month or by day of the week. Generate load duration curves.
- Need to sort values based on a rule, find duplicate values, or highlight the top 20 peak hours in your kW data? Use conditional formatting to quickly unearth that information.
- Want to know the number of motors over 50 hp in one building or the average of all chilled water pumps for a group? Use logical and statistical functions such as SUMIFS, COUNTIFS, and AVERAGEIFS that let you specify multiple conditions and calculate the values you need.
- Want to clean up the data that you get in CSV format from your utility company or the equipment list from the Legacy Maintenance Management System? Use text functions to format the data.
- Want to chart the peak loads by month or weekday for an entire year? Use the date and time functions.
- Creating an automated lighting spreadsheet that can look up hours of operation based on an occupancy code and identify pre- and post-fixture wattages? Combine the lookup and reference functions such as index and match.
- Want to automatically populate the rate information (summer and winter kW and kWh) based on a rate code in various Energy Conservation Measure (ECM) calculations? Use the choose and indirect functions.
- Want to show the kWh vs. Cooling Degree Days (CDD) or Therms consumption vs. Heating Degree Days (HDD)? Want to show temperature on one axis and relative humidity on the other? Want a scatter chart with non-numeric X axis and dynamic titles? Know how to manipulate the standard charts to get the effect you need.
- Want an equation that depicts the chiller input kW with varying chiller load? Use Excel’s regression capabilities.
Microsoft Excel is an unlikely resource to keep in your energy toolbox.
Rama Ayer of Energy Efficiency and Demand Management, Inc. (EE+DM) contributed most of the content for this article. EE+DM is a consulting firm that offers training on using Excel for energy management. She can be reached at [email protected].
