Electric League of the Pacific North West Training Opportunity

This is a quick post to let you know that I will be helping to support a two day technical class for the Electric League of the Pacific Northwest.   

Interstitial Space 03The class will be a two day long technical class that will use the existing building commissioning process as a way to work with the technical skills that commissioning providers will use in virtually any commissioning process.  The class is designed to be interactive and provide some hands-on exposure to the various skills via exercises with SketchUp models and exercises in the Fred Hutchison Cancer Research Center mechanical spaces.

I have added the class to a calendar on the the Training Opportunities page of our Cx resources website and you can find additional information there.  Or you can use this link to go directly to the Electric League’s web page for details and a registration link.


PowerPoint-Generated-White_thumb2_thDavid Sellers
Senior Engineer – Facility Dynamics Engineering     Visit Our Commissioning Resources Website at http://www.av8rdas.com/

Posted in Uncategorized | 2 Comments

Build Your Own Psych Chart–The Spreadsheet

A while back, I started a string of posts in memory of Bill Coad about how to build your own psych chart in Excel based on the basic psychrometric principles and equations.   This is what the completed product looks like.


So far, I have put up the following posts.

  1. Build Your Own Psych Chart – In Memory of Bill Coad
  2. Build Your Own Psych Chart – A Few Fundamental Principles 
  3. Build Your Own Psych Chart – Creating the Data for the Saturation Curve and a Bit About VLOOKUP

I still intend to finish the string and provide guidance on how to complete the chart. 

But in the meantime, folks occasionally ask me for the spreadsheet so they can reverse engineer it on their own.  So, I decided to post it on a support web page that I have on our Commissioning Resources web site at this link so you can download it to work with if you want.

FYI, the page is not a public page so you need to use the link in the preceding sentence to get to it because you will not be able to navigate to it directly on the site.


PowerPoint-Generated-White_thumb2_thDavid Sellers
Senior Engineer – Facility Dynamics Engineering     Visit Our Commissioning Resources Website at http://www.av8rdas.com/

Posted in Excel Techniques, HVAC Calculations, HVAC Fundamentals, Psychrometrics | Leave a comment

The Functional Testing Guide

When I was working at Portland Energy Conservation Incorporated (a.k.a. PECI), one of the things that I helped to develop was the Functional Testing Guide. 

FTGuide Picture

At its heart, the guide was a compilation of functional testing strategies that were donated to the cause by experienced providers.  What we realized after we collected the tests was that they are great descriptions of how to go about performing a functional test on a specific system.

But what was lacking was information regarding why a particular test sequence mattered.  And given that the number of possible HVAC system configurations can be quite large, as can be seen from the following relationship …

Number of Systems v1

… the guide developers realized that it would be desirable to supplement the donated test sequences with information that provided guidance about the systems and components targeted by the test strategies and as well as general information on what constituted a good test strategy.  That way, people using the guide could adapt the test strategies it contained to their unique needs and understand exactly what they were doing.

The result was a tool that, in addition to the test strategies, contained:

  1. System and component specific guidance about how various building systems and their components functioned.
  2. An index of all of the tests and test guidance documents included in the guide with links to each one.
  3. A checklist tool that could be used to help a provider develop a test strategy, including links to the applicable guidance information as well as desirable preparatory steps and precautions to be taken when executing the test.
  4. A control system design guide that provided information regarding the control system design process, the selection and application of sensors, point lists for common HVAC system types, and a point list tool.

All of the tests were in the form of Word or Excel files, which allowed the users of the guide to edit them to their hearts content and make them their own.   And, even though the development of the guide occurred about 12-15 years ago, since the physics of the building systems it targeted for testing has not changed much, the information it contains is somewhat timeless.

Recently, I discovered that the websites that provided access to the guide have been taken offline and retired. And it turns out that I was not the only one, because a number of people contacted me to see if I knew of a way to gain access to it again.  As a member of the development team, it was gratifying to realize that others had found the document useful, but not particularly surprising since, as I mentioned above, the content is somewhat timeless.

It turns out that the answer to that question is “yes”.  More specifically, if you look closely at the image at the beginning of this post, you will discover that the FT Guide website had a download button.   If you clicked that link, a zip file would be downloaded to your computer that contained all of the content in the guide.

If you unzipped the file, it would create a directory on your hard drive that deployed the content in manner that let you run it locally, just as if you were working with it on line.  That was possible because the guide had been deployed using html (Hypertext Markup Language).

Since I have a copy of the download file, and since the guide was developed with public benefit money, I have concluded that I can, with out causing some sort of copyright infringement thing, make it available on FDE’s commissioning resources website. I guess it is possible that someone could tell me to cease and desist with this, but I figure I can cross that bridge when and if I come to it.

So, the primary purpose of this post is to make you aware of that.  In other words, if you go to FDE’s commissioning resources website and pick the Functional Testing Guide topic under the Resources drop down menu, you will find the zip file for download along with some information about how to go about installing the guide on your personal computer and working with it.

But I also thought I would include some content here that highlighted some of the features of the guide for those who are not familiar with it.  The links below will jump you to the indicated topics.  At the end of each topic, a “Back to Contents” link will bring you back here.


Getting Started

When you launch the guide, it should open in Internet Explorer (assuming you either associated it with that application or used “Open with” to select it) and look like like the image at the top of this post.

To use any of the three elements, you need to select the “View Online” button, even though you actually are not working with it on line.

(Back to Contents)

Exploring the Functional Testing Guide

The primary element of the tool is the Functional Testing Guide.  When you click on that “”View Online” button, you should get a screen that looks like this.


In my system, I get the little warning message that you see in the box with the gold header.   For me, simply ignoring it seems to work and if I select “Allow blocked content” the HTML actually does not work as well as if I just ignore it.

(Back to Contents)

Understanding the FT Guide

The first time you open the guide, you may want to review the section titled Understanding the FT Guide.  The figure below is extracted from that section and is a pretty good summary of the structure of the guide, which is related to its evolution and funding levels.


The original edition was published in May of 2013 and included the Functional Testing Basics module, the Air Handling Systems module and the Air Handling Systems Reference module, all packaged as one element.

When the guide was expanded:

  • The functional testing basics portion of  the original guide was extracted and made a stand-alone module because the concepts it contained generally applied to any testing process, irrespective of the system the test was focused on.
  • System modules were created for Air Handlers, Chillers, Condensers, Boilers, and Pumping as a methodology for organizing the applicable tests and related, system specific functional testing tips and benefits.
  • An Integrated Operation and Control Module was created to expand and emphasize the integration focus associated with commissioning and operations.
  • The remaining elements of the original guide (the Why We Test part) were retained as a reference to supplement the air handling system tests in the Air Handling system module (the How to Test part).

There was insufficient funding available to develop reference guides for the other system modules but at the time, the vision was that this might materialize and allow further development.  This never happened.

(Back to Contents)

Functional Testing Basics

As a first time user of the guide and/or someone new to functional testing, you may want to review the Functional testing basics section of the guide because it covers quite a few important functional testing concepts as can be seen from the table of contents listed below.

  • Introduction
  • Understanding the Fundamentals
  • The Commissioning Process
  • The System Concept
    • The System Diagram
    • Detailed Sequence of Operations
  • Testing Hierarchy
  • Documentation
    • As Built Submittals and Shop Drawings
    • Installation Inspection Report Forms
    • Performance Sheets
    • Installation and O&M Manuals
    • Balance Information
    • Operating Sequence
    • As Found and As Left Conditions
  • Training
    • When to Start
    • Supplemental Information
    • Control System Training
    • Factory Training
  • Verification Checks
    • Verification Checks Development
    • Verification Checks Checklist
    • Factory Inspections and Tests
    • Factory Supervision, Assembly, and Start-up
    • System Readiness
  • Warranties
  • Temporary Operation
  • Elements of a Functional Test
  • Basic Tools, Instrumentation, and Equipment
  • General Precautions and Preparations
  • Observing Tests
  • Returning To Normal
  • Getting To Team-based Solutions
  • Trend Analysis as a Functional Testing Tool

(Back to Contents)

The System Modules

The system modules provide the following information for each system type.

  • Key Commissioning Test Requirements, which focuses on basic items that should be considered in the context of testing a particular system type including safeties, sensor checks, actuator sequencing checks, etc.
  • Key Preparations and Cautions, which pretty much what it sounds like.
  • Time Required to Test, which also kind of what it sounds like.  But it does not give specific information in the form of it will take 30 minutes to test the freezestat.  Rather it provides information in the form of general guidelines that take system complexity and size into consideration.
  • Testing Guidance and Sample Test Forms;  the screen shot below will give you a sense of what the information in this section is like.


The links will take you to the actual test forms, which are in Excel or Word format.  That means you can download them and edit them to your heart’s content, add your company logo and print it out in your company’s colors.

(Back to Contents)

The Reference Guide

As I mentioned above, the reference guide only exists for air handling systems.   As you can see from the screen shot below, there is a chapter in it for each component in an air handling unit.


If you open up a chapter, you will find a lot of detailed information about the component, as can be seen from the screen shot below, which is the table of contents for the economizer section.


(Back to Contents)

The Integrated Operation and Control Module

This module was added in the second addition of the functional test guide.  It is a case study of the commissioning of a hypothetical building that goes through a renovation process.  The intention was to tie all of the functional testing concepts together in a realistic representation of what the process might look like out in the field.  So basically, it is a nerd novel about commissioning.

The module includes all of  the basic information about the building such as equipment schedules, system diagrams, etc. in an appendix.   The problem is that the link to the appendix was never added to the table of contents.  So it is really hard to find it.  But fear not, there is a workaround, which is described on the FT Guide web page on our Commissioning Resources website.

(Back to Contents)

The Appendices

The FT Guide contains a number of appendices including

  • Appendix A – On Overview of the Commissioning Test Protocol Library (CTPL)
  • Appendix B – Functional Testing Guide Resources (links to other resources like ASHRAE, AMCA, etc.)
  • Appendix C – Calculations
  • Appendix D – Description of Tests
  • Appendix E – Test Sources

While all of the appendices have useful information, I wanted to highlight Appendix C and D in particular.  Appendix C provides quite a bit of information regarding how to do energy calculations for air handling systems, as can be seen from the screen shot of its table of contents below.


And, while the calculations discussed are in the context of air handling systems, the concepts can be applied to other system types.  For instance the formulas used to calculate fan and pump bhp are very similar.

Fan bhp

Pump bhp

As a result, the techniques outlined in FT Guide Appendix C for calculating savings due to a reduction in static losses or assessing a load profile for a fan system also apply for assessing savings due to a reduction in pump head or developing a load profile for a pumping system.

(Back to Contents)

Resolving a Viewing Problem

If you look closely at the screen shots, you will notice that there is a little tool bar at the bottom of them that gives you a tip for searching the page and also sets up a printable view of the page.   The problem is that for some of the pages, the tool bar prevents you from reading the last line or two as illustrated below.


I have not been able to figure out how to fix this bug, but I did find a work around.   Specifically, if you select “Print Preview” from the tools menu …

Print Preview

… and then pick “Only the selected frame” …

Only Selected Frame… and then page to the end, you should be able to read the last line or two.

Last page

(Back to Contents)

Exploring the Checklist Tool and Test Directory

The second edition of the FT Guide added a checklist tool and test directory that was intended to make it easier to identify the test targets and find a test.  When you launch that portion of the tool by clicking on the “View Online” button, you will find two other options, as illustrated below.


If you select the View Checklist Tool option by clicking on it, you will be take to a page that has links to a number of checklists.

imageIf you follow one of the links, it will take you to a list of items you should be considering as you develop a functional test for that element of the system.  Here is what that looks like for the economizer and mixed air section.


The little icons link you up with pertinent information in other parts of the tool to help you understand why a particular item is on the list.  A list of precautions to be considered when executing a test on a given system element is also included.

If you select the “Test Directory” button from the Checklist Tool and Test Directory home page, you are taken to a page that provides access via links to all of the tests contained in the tool.


If you over over a link and right click it and pick the “Save target as” option …

Save Target

… a window will open up and allow you to save the selected checklist to your hard drive as a Word or Excel file.   From there, you can open it up and edit it as needed to match your specific system requirements and reflect your company’s logo and other standards.  The screen shot below illustrates what you get if you open the Cooling Tower Prefunctional Checklist (test ID #78) from the table in the previous image, which is in the form of a word document.


(Back to Contents)

Exploring the Control System Design Guide

The control system design guide evolved with the functional testing guide because having a working, well designed control system is essential for delivering a functional building system.  In addition, testing the control system is always going to be the first step in testing the controlled system for any functional testing process .

When you click on the “View online” button for the Control System Design Guide, the following page will open up in your browser which provides access to the content.


If you open the How to Use the Design Guide module, it will give you a good overview of what the different chapters are about.  So rather than repeat that, I will just highlight a some of the features that are incorporated in the guide that I think are really useful.

(Back to Contents)

Valve and Damper Schedule Spreadsheets

If you explore the Control System Design Process module, in addition to learning about some of the challenges associated with designing control systems, you will be presented with ways to address those challenges, including some tools like spreadsheets for sizing control valves and control dampers and developing control valve and control damper schedules as you can see in the screen shot below.


If you hover over the Link to Valve Schedule Spreadsheet button, right click, and then pick the “Save target as …” option, you can save a copy of the spreadsheet on your hard drive.

Once it is there, you can open it up and edit it to your heart’s content to match your personal likes and company standards.  Nothing is locked and everything is totally editable;  why you could even make it pink with purple polka dots if you wanted to.


If you “play” with the spreadsheet a bit, you will discover that the valve sizing equations are built into it.  Meaning that if you fill in things like your targeted flow rate in gpm and your targeted pressure drop in psi, then the spreadsheet calculates the targeted Cv for the valve.  A similar tool is provided for damper schedules.

(Back to Contents)

Sensor Selection and Installation Guidance

As you can see from the screenshot below, Chapter 3 contains a lot of information to help you make decisions regarding the sensors that will feed data into your control system.


The information is presented in both a narrative and tabular format providing guidance regarding the pro’s and con’s of different technologies, and the accuracy to target for different applications along with application specific installation and calibration recommendations.


(Back to Contents)

System Configurations and Recommended Point Lists

The 4th chapter of the CSDG focuses on 15 common air handling system configurations and the recommended points for each one.


To me, the point list tool that is included in this section (and also linked up in Chapter 3) is one of the most valuable tools in the guide.  In my opinion, if you were to provide a point list with the features of included in this point list tool along with an outline narrative control sequence for your projects, you will have taken a huge step forward in terms of improving the quality of your control system design.

Slides 41-47 of a presentation I did for the Oregon ASHRAE Control Trade Show will give you some insight into why I say that.  And you may also find that presentation to be useful as a general introduction to the concept of control system design and commissioning.

In any case, if you save the spreadsheet to your hard drive and open it up, you will find something like this, which is an open, fully editable tool, just like the valve and damper schedule tools I mentioned previously.


As you can see, the spreadsheet has a tab with a recommended point list for each of the system types described in the guide.   The yellow column is something you would not publish with the point list if you used it for a project.  But it provides guidance for you as the designer and specifier regarding if a point is absolutely essential or could be optional if budget was an issue.  This is accomplished by referring you to notes in a supplemental html file that is also included via a link in the guide.


(Back to Contents)

So there you have it, an overview to the Functional Testing and Design Guide along with a link for accessing it from FDEs commissioning resources website.


David SellersPowerPoint-Generated-White_thumb2_th
Senior Engineer – Facility Dynamics Engineering     Visit Our Commissioning Resources Website at http://www.av8rdas.com/

Posted in Air Handling Systems, Boilers, Hot Water Systems, and Steam Systems, Chillers and Chilled Water Systems, Condenser Water Systems, Controls, Design Review, Economizers, HVAC Calculations, HVAC Fundamentals, Operations and Maintenance, Refrigeration | Leave a comment

Creating a Third Axis In Excel

One of the challenges that came up when I was creating the time series graph of a 9,000 ton chiller plant load profile that I show in my previous post was that I wanted to plot data series that had numbers in them with very large differences in the order of magnitude. 


In other words, to get something visually meaningful1, I needed to plot:

  • Temperatures that would all fall into the range 0-100°F against
  • Tonnages and flow rates that would fall in the 0 – 15,000 gpm/ton range against
  • The number of chillers running, which would fall in the 1-10 range

The Issue

As you probably know, Excel lets you add a secondary axis to your charts, but, as far as I know, that is were it stops, at least in terms of being able to do it with the chart design tools.  Prior to the insight that lead to the technique I will show in this post, they way I dealt with the need to plot more than two data series with wildly different orders of magnitude was to scale one or more of them so they would be visually meaningful on one of the two axis I had available, and then include the scaling factor in the name of the series.

For instance, to plot the number of chillers running on the same axis as temperature, I might have multiplied the number of chillers running by 10 and then plotted it as Number of Chillers Running x 10.  That worked, but it was kind of confusing in a way. Having a third axis to dedicate to a third order of magnitude range (or a 4th or 5th or 6th if you needed them) makes it easier for me (and I think others) to intuitively read the chart. 

The “Trick”

My trick for adding an additional axis (or more) to an Excel chart it is create a data series that I plot vertically against the X axis which is scaled to reflect the range I need so that it spans the entire height of the chart. 

Then, I scale my data so it is visually meaningful on one of the two real axes that are available.  But providing the additional axis, tied to the data series via its name,  a person using the chart can read the scaled data against the extra axis.

That is the trick in a “nutshell”.  But I thought it might be useful to walk you through the steps in the process.   Moving forward, I will discuss this in the context of having three data series, each of which will be associated with a different axis.  But:

  • Multiple data series with similar ranges and magnitudes can share an axis, including the third axis we will discuss creating, and
  • If you had four (or five or six, etc.) radically different data sets, you can use the third axis technique I will discuss to create additional supplementary axes.

The Data Set

To demonstrate the concept, I created a little data set with three very different orders of magnitude associated with the three data series.


Incidentally, if you want to “play along”, I put the basic data set along with  my example spreadsheet in a zip file that you can download from the Excel Third Axis tool page on our Commissioning Resources web site.

Overview of the Problem

If you plot Value 3 (orange) in the data set above by itself against an appropriately scaled axis, it has an obvious wave form associated with it.


But, if you plot the Value 3 (orange) data set against an axis ranged appropriately for Value 1 (red), along with Value 2 (green) plotted against a secondary axis that is appropriately scaled for the Value 2 range, Value 3 it looks like a flat line.


Obviously, if you tried plotting Value 3 against the Value 2 axis, it would be an even flatter line given the magnitude of the range on the green axis relative to both the Value 1 axis and the Value 3 data set range.

Adding a third axis dedicated to Value 3 solves the problem as can be seen below.  Note that I plotted the value three axis and the associated line in blue and the actual data behind the line as orange markers on the blue line.


As you can see, you can now read the values for the points in the Value 3 data series directly off of the blue axis.  For instance, as illustrated above, Value 3 = .0075 when X = 7.

Some times, I want to make the series as visually large as possible compared to the other series, which is what I did for the previous image.  In other words, I wanted to provide the broadest visualization of the data relative to the other series that the dimensions of my chart would allow.

But, other times, I might want to scale things so that the data series are visually meaningful, but also so they do not lie on top of each other, like this.


That means that the decisions you will make to set up the third axis are a function of how you want to display your data.  You can make a case for either way depending on the scenario. 

For this example, I will set the axis up to plot the curves so they are not on top of each other, as illustrated above.  

Step 1 – Decide Which Data Series will use the “For Real” Excel Axes and Which will use the Third Axis

In the bigger picture, the first step in my process is to pick a scaling factor for your axes.  But to do this, there are actually a number of things you need to think about. 

  1. Which data sets will use the “for real” Excel axes and which data sets will use the third  axis you are going to create?
  2. How should the two Excel axes be scaled?
  3. How should your third axis be scaled?

The third axis is actually a fake axis in the context of plotting data.  In other words, while it will allow you to read an associated data series as if it was plotted against it, you are actually plotting a scaled version of your third axis data set against one of the other two  axes provided by Excel.  That means that when you are thinking about the different scaling factors, you kind of have to think about them interactively since the third axis will be influenced by the scaling factor associated with what ever axis you use to actually plot the data.

I typically start by looking at the maximum and minimum values for all of the data series and seeing if  any two of them have a numerical range that is similar in terms of the values but different in terms of order of magnitude.  For example a data set with a range of  0 to 2 is numerically similar to a data set with a range of 0 t0 2,000, but different by a factor of 1,000.   Neither of those data sets is similar to a data set with a range of –0.175 t0 .25 numerically or in terms of order of magnitude.

When I looked at the example data set in this manner …


… it struck me that the Value 1 and Value 3 data sets met this criteria.  So, I decided that I would use the “for real” Excel axes for Value 1 and Value 2 and create my third axis for the Value 3 data set, which I would plot as scaled data against the Value 1 axis.

Step 2 – Select Scaling Factors for the “For Real” Excel Axes

Now that you have settled on which data will use the built in Excel axes, you need to come up with a scale for those axes.  What you pick will depend on how you want to present the data, (overlapping or not) as I discussed previously. 

Since, for this example, I targeted non-over lapping data series, I needed to select scaling factors that would do two things.

  1. Allow the data, when plotted to be visually meaningful.
  2. Create a “window for the third axis data series to reside in so that it would not overlap the other data series.

This is somewhat arbitrary and I usually make the decision by starting my chart, adding the a data series for each of the Excel axes and then playing with things.   Here is what I ended up with, which basically created a “window” for my third axis data series between the Value 1 and Value 2 data.

imageI could have also set it up so that the “window was above the Value 1 data series or below the value 2 data series;  like I said, it is kind of arbitrary.

One other point I should make before moving on to the next step is with regard to the large gap between the end of the data on the right side of the chart and the right (green) secondary axis.   That gap is not there due to my bifocals creating a distortion such that it looks like there is no gap there to me.

Rather, I created it intentionally because that is the space where we will place our third axis when we get to that step.  I could have put it on the left side of the chart, made it narrower, made it wider, etc.  Those are all arbitrary decisions.  But my point is that before the we finish, we will need a space for the third axis, so I went ahead and created it at this point in the process.

Step 3 – Select a Scaling Factor for the Third Axis

At this point in my process, my focus is on selecting a scaling factor for the third axis that will make it visually meaningful when I plot it.   In some instances, such a scaling factor will also place that data on top of the other data on that axis if they are numerically very similar.  That was the case for this data set as you can see from this table comparing the scaled Value 3 data with the Value 1 data and the associated graphs.




That would not necessarily be true if the Value 1 and Value 3 data was numerically similarish instead of numerically similar as can be seen from this table and its associated chart.



When I first started messing around with this, after going through an infinite number of variations between charts similar to the first two in this section and still ending up with the lines on top of each other, I realized what I needed was a scaling factor along with an offset. 

Kind of a “Well Duh” moment, but as Jay Santos often says;

Engineers are empirical learners

In any case, by applying that principle,  I was able to achieve the following “look” using a scaling factor of 100 with an offset of –2.


Step 4 – Create the Third Axis Line

At this point, if you are “playing along”, you would not have a chart like the ones I have been using to illustrate the impact of various scaling factors and offsets because we have not discussed how to create that axis yet.  But you probably have a table that looks something like this …


… and maybe even a chart that looks something like this if you have plotted the data.


Our next step will be to start to build are third axis.  

At a fundamental level, the third axis is just a plot of a data set where:

  • The X values are all the same and correspond to the value on the X axis where we want the third axis to appear, and
  • The Y values are selected so the data points are evenly space on the line and fall on the chart’s major grid lines.

For me, the easiest way to do that was to build a little tool that would create the table I needed for plotting the line based on the properties I wanted the axis to have, which are:

  • The minimum value on the Y axis that corresponds to the minimum value on my third axis, and
  • The maximum value on the Y axis that corresponds to the maximum value on my third axis, and
  • The number of even increments I want to have on my third axis, and
  • The X value associated with the location of the third axis.

Here is what my little tool looks like.


Here is the same thing with the formulas made visible.


Note that in my tool, if I change the Value 3 Axis Number of Major Increments (cell F27), I manually need to insert some rows in the dark red area and copy and re-paste the formulas in cells I31 and J31 into the same columns in the rows below.   I suspect you could solve that to happen automatically with some VBA code if you wanted to.

But having set up my table, I now can create my 3rd axis line by plotting the Third Axis X and Third Axis Y Values (Columns I and J, rows 30 – 38) on my chart against the primary Y axis (the red axis on the left).


Step 5 – Adding the Tick Marks

Next, we need to add the “tick” marks (the short lines next to the axis that fall on the grid line and reference the number associated with that point).  We do that by adding a marker to the line we plotted for the X axis. 

More specifically, we select the line we just created and the pick the “Format Data Series” option.  I do that by clicking on the line and then right clicking and selecting it from the little window that opens up.  But you can also do it using the “Format” menu associated with the Chart Tools at the top of the page. (Heck, for all I know, there could be a shortcut key that lets you do it with one click in combination holding down Ctrl, Alt, and 8 other keys concurrently with Revolution Number 9 playing in the background.)

But no matter how you get there, once the Format Data Series window opens the data points will be highlighted (the red arrow points to what I mean).  From there, by selecting “Mark Options” (the blue arrow is pointing to it), you can select the marker type you want (where the orange arrow is pointing) and set all of the properties, like the line weight, the color, etc.  For my chart, I selected the marker that is a short, horizontal bar and made it the same color and width as the line I plotted for the axis. 


In fact, if you wanted to, once you were at this point, you could click on an individual marker and make it different from all of the rest.  I’ll give an example of why you might want to do that at in the next section. 

For the time being, here is my result after adding the markers.


Step 6 – Adding the Axis Tick Mark Labels

Next, we need to put numbers beside the tick marks on the third axis we created.  Excel allows you to put a label with each data point in a data series, and we will use that feature to do it.  You can get to it by hovering over the data series, right clicking, and selecting the “Format Data Labels …” option.


But first, we need to set up the number that will appear in the label.  This is fairly straight forward. 

Specifically, if you were not offsetting the data, you would simply scale the Y axis labels using the same scaling factor you developed for plotting the data.  But if you are offsetting the data in addition to scaling it, then you also need adjust for the scaled offset.  I do this by adding a column to the table in my little tool.  Here is what that looks like along with a view of it with the formulas exposed.



Now, the trick becomes getting the third axis labels to show up next to the tick marks on the blue line we created to represent the third axis.   That is where the “Format Data Labels …” option I mentioned above comes in.

If I hover over my blue third axis line, right click, and select that option, I end up with a formatting window on the side of my screen very similar to any of the other formatting windows I get when I have selected an object and picked the formatting option.


Notice how the location where the labels will appear are now highlighted on the graph (the red arrow points to what I mean) and also that when I selected “Label Options” by clicking on the three little green bars (where the orange arrow is pointing), I have a place where I can select where the label contents come from (where the blue arrow is pointing).

By checking the “Value from Cell” check box, I can select my scaled values and have them appear next to the axis.  Note that when you do this, you may need to uncheck the “Y Value” check box, which is the default because, you can use multiple sources for the labels and we probably don’t want to do that in this case.

You can also format things like the font, the font color, the text box fill, etc. to get the look you want, just like you would for any other Excel object you were formatting.  Here is where I ended up after going through those steps.


You may notice that the most negative value on the third axis is hard to read because it lies on the X axis line.   You can fix that by selecting that particular label and adding fill to the text box (at least that is how I solve that problem).


I also inserted a rectangular shape, formatted it to match the chart background, and located and sized it to cover up the 11 and 12 on the X axis since they are superfluous for the purposes of my chart.

And because the values on my axis transition from positive to negative, but for the scaling factor and number of increments I selected, 0 (zero) is not specifically called out, I may want to change the font, tick, and line color for the negative part of the axis to highlight the transition.


Of course, it would also be possible to play with your scaling factors and offsets, and increments until you ended up with a scale that had zero on one of the major grid lines.  But given all the variables in play, which for this example included:

  • Separating the data series, and
  • Having a set of grid lines that hit even, meaningful divisions on all three axes, and
  • Having all of the data be visually meaningful

that can actually be tricky.

Bear in mind that how far you go with that type of stuff is a matter of personal choice. Just because you can do it doesn’t mean you should  do it.  And what makes sense to you may not make sense to others.  For instance, to a color blind person, the little nuance I just illustrated is not very helpful.

Adding a Few Whistles and Bells

Pointer Lines

At this point, we have successfully (I hope) created our third axis.  If you needed even more axes, you could go through these same steps and add them.  

But no matter how many axes you add, it is good to take a reading or two using them to make sure you didn’t zig when you should have zagged somewhere in the development process.  

To facilitate that, I often add a little feature to my charts that makes it easy to precisely read data from the chart and also to highlight something for someone else viewing the chart.   More specifically, I add little arrows that point to the value on the third axis (or any axis I want) associated with a given value on the Y axis.  You may have already noticed them in some of the charts I was using earlier on in the post to illustrate the effect of different scaling factors.


The lines are generated in a manner very similar to the third axis.  In other words, the vertical line is just a series that is plotted with two points;

  1. One point has an X coordinate equal to the X value of interest and a Y value equal to the minimum value on the Y axis that you are plotting the series against.
  2. The other point also has an X coordinate equal to the X value of interest but the  Y value is the Y value of the data point you are wanting to read.

The horizontal line is generated in a similar manner, but the Y values are constant.  I do all of this by building a little table that lets me enter the X and Y values of choice and then sets up the other points in the table to generate the line. 


In this particular example, I use VLOOKUP to come up with the Y value associated with an X value.  But, I can also manually enter the point, or I could even develop the equation for the line I am looking at using Excel’s trendline feature and then have it calculate the Y value for the X value I entered.

In terms of checking things, the easiest points to check, of course, are ones that by chance happen to lie on or near a grid line.   You really don’t need my little lines to do that , but for illustration purposes, if I have done things correctly, then the value on the third axis associated with X=5 should be just slightly above the .0025 tick mark/grid line on the third axis …


… which seems to check out in the graph above.  And the values associated with X=3 and X=9 should be just slightly above the –.0100 tick mark/grid line.


So things seem to check out.

Using Pointer Lines to Precisely Read the Data from Your Third Axis Series

Obviously, you could also do what I just suggested by simply inserting shapes on the chart where you wanted them.   But if my make them plotted lines, the  other thing you can do with them is use them as a tool to precisely read the chart.  

In other words, by setting up your table so that you can pick an X value and then “play” with the Y value until the to lines meet at exactly the point of interest, you can “read” the Y value because it is the number you have in the cell that you were playing with. 

In this example, I have my little table set up to adjust all of the other values based on what I enter in the yellow X cell and the orange Y cell.  The vertical orange line is the series in the red box plotted against the left axis and the horizontal orange line is the series in the blue boxes plotted against the left axis. 


To use the tool in this manner, I arbitrarily set X to the value of interest.  In this case, I picked an arbitrary value of  4.86 and entered it in the yellow cell. 

I will arrive at the Y value by “tweaking” (which is the technical term for “playing with”) the Y value in the orange cell and zooming in to look closely at where the two orange lines meet on the blue waveform.  I will continue the highly technical process of “tweaking” until the two lines meet exactly in the middle of the blue line.

To start that process for this example, I made an educated guess at about what the Y value  should be when X was 4.86 by considering the fact that the major division for the blue third axis was .0125 and then “eyeballing” (another technical term) that the point where the X axis intercepted the blue wave form was going to fall at least 4/5ths of the way between –0.0100 and +.0025.  I used 4/5ths because .0125 is pretty easy to divide by 5 in your head, even at my age. 


  • One fifth of .0125 is .0025, meaning
  • Four fifths of .0125 would be .01, and
  • -0.0100 plus .01 will be about 0.

So I started there. 


Obviously, I didn’t even need to zoom in to see that I needed to do some additional “tweaking”.  But I had kind of expected that;  my first estimate was just to get me in the “ball park” of where I needed to be.  As a result, I now knew that the number I was looking for was somewhere between 0.0000 and 0.0025. 

Visually, it looked like it would be about half way between the two. So I tried that.


(Since my cell was formatted for 4 decimal places, 0.00125 got rounded to 0.0013)

That looked pretty good, and probably was “good enough for government work”.   But when I zoomed in to see exactly how good it was, I could see that I was just a bit low.


If you look closely, you can see that the two orange lines intersect towards the bottom of the blue line vs. exactly in the middle.  So, I arbitrarily made another “tweak” and adjusted Y to .0015, and as scientific people often say, “Ta-Da”.



How many “tweaks” you make is a function of how anal you are (I can be pretty anal).  My real point is that this approach lets you come up with a pretty exact value by reading your chart.

Obviously, if I knew the equation of the line, I could have just calculated what Y was given a value of X.  But a lot of times, when I am using this approach to read my chart, I am looking at some wild and crazy trend data that I pulled from a logger or control system or utility meter and there is no equation.  In fact, one of the reasons for plotting the data was so I could pick Y values from the data series based on an X value I selected.

Focusing Attention

One last “trick” before I stop. 

When you start trying to show a lot of data on one graph, doing things to help people correlate the information can be helpful.  For instance, I color coded my axes to match  my data series in an effort to help a (non-color blind) person’s “mind’s eye” make the connection.

I potentially could further enhance that by restricting the range of the axes as shown below.


For the third, blue axis, I accomplished this by formatting the text in the data labels I wanted to hide so that it was transparent.   For the red and green axis, I did it by inserting a rectangle shape, formatting it to match the chart background, and then sizing and positioning it so that it covered the part of the axis and related tick marks that were outside the range of the data I was presenting on the axis.

David-Signature1_thumb1_thumb                                                         PowerPoint-Generated-White_thumb2_th

David Sellers
Senior Engineer – Facility Dynamics Engineering                                                                     Visit Our Commissioning Resources Website at http://www.av8rdas.com/

1.     What I mean by the term “visually meaningful” is that the scale of the axis associated with a data series allows its wave form to be seen.    If the peak to peak value of a wave form is small relative to the axis it is plotted against, it will come out looking like a flat line, even though it actually is not at all flat.

Posted in Data Logging, Excel Techniques, HVAC Calculations | Leave a comment

A New Application for Plot Digitizer (Plus a Quick Look at Hydraulic Variable Speed Drives and Chiller Free Cooling Cycles)

Those of you who know me know I am quite enamored with a little freeware application called Plot Digitizer.  If you are unfamiliar with it, the application allows to to create CSV (Comma Separated Value) files from the lines in an image.  Meaning that if you have, for instance, a pump curve as a .pdf or .jpg file, then you can pretty quickly capture the curve shapes and load them into a spreadsheet to create a chart that is an electronic version of the curve. 


Once the curve is in the form of a spreadsheet. you can do math on the lines in it.  For instance, you can use the affinity laws to project a new impeller size from a know impeller size.  or you can plot a system curve from the data you collect in a pump test and the square law.


This link will take you to a page on our commissioning resources web site where I provide more information, and this link will take you to a page where I provide a spreadsheet template that will let you create a formatted pump curve pretty easily from the CSV files you capture with plot digitizer.

My goal in this post is to show you an idea that came to me one day out in the field that saved the day for me and involved using plot digitizer in a way I had not thought of before.  Since it happened while I was working at a really interesting chilled water plant that had some unique features, I thought I would give you a peak at those things also.


There were two cool features in the plant I will be discussing that I wanted to highlight in addition to illustrating my new Plot Digitizer application.  But if you want to jump straight to that, the links below will let you do that (or jump to any of the other topics for that matter).  Each section has a Back to Contents link that brings you back here.

Setting the Scene

Last October, I had the opportunity to support a field class that used the central chilled water plant at the Gaylord Grand Ole’ Opry as a part of the Building Commissioning Association Annual Conference (formerly called the National Conference on Building Commissioning or NCBC).  (And still called that by older folks like me who forget they changed the name).  It was a 9,000 ton plant;  one of the largest I have been around for a while. 


The plant was a variable flow primary/secondary plant.  Unlike current technology chillers, back when this plant was designed, the chiller technology would not deal well with flow variation in the evaporator.  In fact, in the olden days, before we had realized that we needed to pay attention to energy efficiency, the most common chilled water plant design configuration was a constant flow arrangement and a big driver for that was protecting the chiller tube bundles from frosting up and freezing.  But you ended up with large pumps moving the design flow rate at the design head for all of the operating hours.

The variable flow primary secondary design evolved as a way to allow the flow rate to the loads to vary with the load profile, saving a significant amount of pump energy, while maintaining a steady flow rate through the chillers, which protected them.  My point in bringing this up here is simply to let you know about the configuration of the plant I am about to discuss, not to explain variable flow primary/secondary plant theory.  But, if you want to know more about  that, you will find a couple of resources at this link.

Return to Contents

The Quick Look

Aside from the size and quality of the plant, there were two technologies they had in place that provided for some added interest.  So, I wanted to briefly highlight them here so you recognize them if you run into them.

Return to Contents

Hydraulic Variable Speed Couplings

One unique feature was that the distribution pumps had hydraulic variable speed couplings on them.


A hydraulic coupling is very similar to the torque converter in an automatic transmission and is the blue piece of machinery between the motor (the dark gray thing on the left) and the pump on the right (the black thing with the silver pipes attached to it) in the picture above. 

Here is a picture of the drive itself, including the heat exchanger that rejects the heat associated with the efficiency losses.


While fairly efficient at full speed and full load, these drives are much less efficient than a current technology variable frequency drive at part load.  That means they represent a good retrofit target and the plant operating team has that on their list of improvements for this year. 

From a retrocommissioning standpoint, for a project where you might need to document the inefficiency of the drive to support your case for replacing it,  its kind of cool that you could pretty easily document the efficiency of the drive by logging flow and temperature rise across the heat exchanger because that is where the losses show up.

This is the actuator that controls the output speed of the drive by varying how much of the oil that is moved by the impeller in the drive reaches the turbine.


Another interesting thing about this technology when you contrast it with the variable frequency drive most of us are more accustomed to  is that the motor is ahead of the drive.  That means the motor needs to be sized for the brake horsepower the pump needs at its input shaft, plus the losses in the drive.  In contrast, a variable frequency drive serving a motor serving a pump supplies the pump energy plus the motor efficiency losses.

In this case, the pump bhp requirement is probably in the range of 400 – 425 bhp and the motor is a 450 hp motor.  Thus, having the drive losses be part of the load that the motor had to serve probably did not affect the motor selection.  But I suspect there are instances where the hydraulic drive would have kicked up the motor size by one incrament due to its location in the “food chain”.

You may wonder why anyone would use even use a hydraulic drive.  My guess is that at the time they were installed, a variable frequency drive for a 450 hp 4,160 volt motor would have been pretty spendy.  

For example, in 1980, when I specified my first variable speed drive for a 40 hp air handling unit motor, my choices were a variable frequency drive that cost about $50,000 and was the size of two motor control center sections.  Or, I could use an eddy current clutch which cost about $20,000.  The eddy current clutch was significantly less efficient at part load, but given the price difference, it was the better choice at the time.

So my guess is that a similar economic assessment prevailed when they built this plant and these drives probably made sense back then.  Plus, they are basically mechanical devices so a mechanically inclined person can probably fix one in a pinch.

Given that the point of this post is something other than hydraulic couplings, I’m not going to go any further into them for now.  But TMEIC’s brochure titled Selecting Variable Speed Drives for Flow Control provides a lot of good information regarding how they work along with comparing them to variable frequency drives.

Return to Contents

Chiller Based Free Cooling Cycle

Most people in the industry are familiar with water side free cooling cycles that leverage the capacity of cooling towers at low wet bulb temperatures to create chilled water directly with out the need to operate a chiller.  Typically, this involves operating the cooling towers to produce water colder than is required by the chilled water system and using a plate and frame heat exchanger in between the condenser water system and the chilled water system to transfer the energy. 

A picture of a typical plate and frame heat exchanger is shown below along with a little model I have that  shows all of the parts.

DSCN6477_thumb12   DSCN0931_thumb13

Here are a few pictures of some actual plates.

Plate-and-Frame-Hx-Plate-02_thumb3  Plate-and-Frame-Hx-Plate-01_thumb3

My point here is that there is another way to accomplish the cycle with out the cost of the heat exchanger and the pumps it requires and several of the chillers in the Gaylord plant were equipped with this feature.  

More specifically, some chillers can be configured in a way that allows an operating mode to occur where control valves bypass the compressors and expansion device.  The compressor bypass allows  refrigerant vapor to migrate from the evaporator to the condenser due to the vapor pressure difference created if the temperature in the condenser is lower than the temperature in the evaporator.  The expansion device bypass allows liquid refrigerant to circulate by gravity back from the condenser back to the evaporator. 

That means that if you run the condenser water temperature down below the desired chilled water supply temperature (just like you would if you were going to use a plate and frame heat exchanger for a free cooling cycle), then there will be a natural circulation pattern set up inside the chiller that transfers heat from the (relatively) warm evaporator to the (relatively) cool condenser.

Here are a few slides I use when I teach about this feature, including one that shows the parts and pieces on the chillers in the Grand Ole’ Opry plant.  This first slide shows a schematic of a centrifugal chiller with the two control valves added but with the chiller in the normal operating cycle (warmer colors = warmer temperatures).


This next one shows the free cooling cycle triggered with the valves open and the compressors shut down.

image_thumb4This slide highlights the compressor bypass valve on an actual chiller. 


Note that it is in a similar location to where the hot gas bypass connection might be for a chiller of this type.  But, since this cycle needs to work at very small pressure differences, the pipe is much bigger than it would be for a chiller where hot gas bypass was installed. 

Here is a schematic showing the hot gas bypass connection along with a picture of a similar chiller (same manufacturer and product line but about half the tonnage) with a hot gas bypass connection to give you a sense of what that would look like.



The other difference between what a chiller with a free cooling cycle would look like compared to one with hot gas bypass is that the free cooling cycle requires a second valve that bypasses the expansion device.  Hot gas bypass does not require this second valve.  Here is the Gaylord chiller with the second control valve highlighted.


I will probably do a more details blog post about this at some point, but for now, that should give you a sense of what the free cooling option looks like on a chiller. 

All of my exposure to the free cooling feature on a chiller have been on Trane chillers.  But I suspect other vendors can offer it assuming their condenser is higher than their evaporator so you can get the gravity flow back along with some other technical details.   There is a section in this Trane manual that provides a description of  the cycle working on one of their machines.  And this page on their website will give you a few more images to look at.

Granted, this adds to the cost of the chiller.  But assuming you can get the capacity you need from the feature, it means you can provide free cooling with out buying a plate and frame heat exchanger and piping it into the system.  In other words, the pumps and connections serving the chiller also serve the free cooling cycle on the chiller.  If you had to do it with a plate and frame heat exchanger, you would need to provide all of that for the heat exchanger in addition to the heat exchanger itself, which is a pretty expensive piece of hardware.

Return to Contents

Using Plot Digitizer to Generate Trend Data from a Graphic

O.K.;  I will now return to the main reason I put of this post. 

One of the reasons I got to spend time in the Gaylord plant is that the operating team had graciously agreed to allow the commissioning conference to use it for a field exercise in the training class I was supporting for the conference.   Originally, I had hoped to use trend data from the plant to illustrate a few techniques I use.  But unfortunately, they were in the middle of a control upgrade and there was no trend data readily available. 

That changed my plans for the class a bit and time was of the essence since the plan was I would arrive on site the Friday morning before the class, spend Friday exploring the plant, and then develop the class over the weekend in my hotel room.

Back in my hotel room Friday evening, I found myself self studying the pictures I had take of the the chiller graphic displays, longingly wishing the control upgrade was to the point where I could pull the data they contained out of the system. 

The pictures below are of of the motor data and evaporator data for one of the chillers and will give you a sense of what I was staring at (note that the time scales are slightly different, which is why things don’t line up exactly).



I was actually contemplating doing a manual transcription of the data.  In the olden days, before we had trend data at our fingertips and all we had were log sheets with, if we were lucky, three readings a day   with one set of readings taken on each shift, manual transcription and plotting was the approach we were stuck with. 

The process was tedious but possible and provided meaningful insights in terms of general trends as long as the data was not highly variable (like a hunting control loop for instance).  And, it is the underlying concept behind the process I use now to leverage trend data to start to assess a plant.

In any case, as I was about to make a pass at manual transcription,  it hit me;  I realized it would a lot faster to use plot digitizer to trace the lines out and create Comma Separated Value files (CSV files) that I could then import into Excel and manipulate to my heart’s content.

So, I loaded one of the images into the tool and tried it out.   In hindsight, had I thought about doing it at the time I took the pictures, I would have tried to line myself up more directly with the graphic screen to eliminate the impact of parallax.  But I concluded that:

  • Since the angle I used was about the same on all of the shots, and
  • Since I was not as concerned with absolute values as patterns, and
  • Since this was a preliminary analysis and not an exact science

the data I pulled from the photos of the graphics would be good enough for my purposes. 

Return to Contents

Using the Trend Data

The purpose of this post is not to go into the details of my analysis technique (but hopefully in a future post I will).  Rather, it is to illustrate how I got the data into a form where I could use it for analysis.  In general terms, the steps behind what I show towards the end of the post are as follows.

Step 1

For each chiller, I digitized the motor current data as described above using Plot Digitizer.  Specifically, in the first image in the preceding section, I digitized the brownish colored line.

As I clicked across the image in Plot Digitizer to pick up the line, I tried to pick points that would capture the general curve shape, meaning I tried to click on the line anyplace the slope of the line changed significantly, but I didn’t worry too much about ripples that were small relative to the major changes.  The assumptions I would be making in my analysis would make them kind of meaningless. 

This gave me a little table in the form of a CSV file with a date and time in one column and a percent run loaded amps value for that point in time in another column. 


Once I loaded the CSV file into my spreadsheet, I plotted it. 


Step 2

The reason I plotted the chart is that (for me at least), it is a quick way to do data validation.  Things like the negative values and points being “backwards” in time jump out at me in the graph faster than they do in the table, all though you can see them both places if you look closely.  And for me, as I tweak the data to correct for those issues, the chart gives me a quick visual on the validity (or not) of the adjustment I made.

Obviously, the chiller could not pull negative amps and something in the future could not have happen before something in the past;  the reason for the discrepancies was I was slightly off with some of my mouse clicks when I did the digitization. In other words, when I am using Plot Digitizer  I am trying to click on a pixel with  my mouse that the program then correlates with the pixels I told it represented the X and Y axis for my chart.   If I am off  by one or two pixels, I get a value that doesn’t really exist.

So, before using the data, I did a bit of data validation.  Specifically, using the actual image as a reference:

  1. I filtered the data to replace negative values with zero.
  2. I eliminated points that were double clicks on the same point in the line on the image.
  3. I arbitrarily adjusted the data points a second or two either way where their was a sharp drop in the data so that the line was vertical and/or a data point further down the table (which correlates to a point further to the right in the graph image) was slightly later in time than the value ahead of it.

This only took a few minutes and made my data more representative of the actual data in the image I was trying to capture. And it was much faster and more accurate than manually trying to read points from the graph and enter them into a table.  The result of validating the raw data above came out looking like this.



Step 3

Once I had completed the first two steps for each chiller, I needed a way to combine the data.  Given the technique I used to capture the data in the first place, there was not the proverbial “snow ball’s chance in hell” that my time stamps were consistent from chart to chart to chart. 

So, I started a new  table with the first column being a date and time that incremented by one minute per row.  The first date and time value was manually set and simply corresponded to the earliest time I had in my data set. All of the other rows are created by using an Excel formula that added 1 minute to the value in the date and time column I was making relative to the same cell in the row above the cell with the formula.1   

In the image below, the table to the left (orange outline) is what the cells looked like for the first few rows of the spreadsheet.  The table to the right illustrates those same cells with the raw values and formulas made visible.


Since the data set I was working with covered about two days, I ended up creating about 2,880 rows with time stamps (2 days times 24 hours per day times 60 minutes per hour).

Next, I added columns for each chiller and used the VLOOKUP function to go to the table with the digitized data I had created for each chiller in it and fill in the percent run loaded amps value associated with the time stamp in the first column of the row.  Here is what those cells and their formulas looked like for the first few rows.


Certainly, this introduced a bit of an error into my analysis.  For instance, let’s  say I selected a point at 12:51 PM when I was digitizing my data and then the next value I picked was at 1:51 PM on the same day because the machine was off or drew the same amount of current for that entire period.  Because of how the VLOOKUP function works, when it scanned my data table, for all of the times between 12:51 PM and 1:50 PM, it would have reported back the percent run loaded amps value that existed at 12:51 PM. 

That means that if I was not fairly meticulous in making sure I captured any significant change as I did my digitizing, I would have introduced some potential issues.  But, since I was careful to pick up any meaningful change, this approach would provide a reasonable value for the gap in time. 

In other words, if I could visually see a change in the value of the line I was digitizing relative to the previous point I had clicked on, I clicked again.   When I reviewed my images, you could visually pick up changes in the range of 2% (basically, the “ripple” you can see in the motor data image earlier in the post between about 9:21 PM pm the 10th and 1:21 AM on the 11th ).  So, as long as I was rigorous and methodical in my digitizing, I probably had not skewed the results for any given data interval by more that 2 or 3%.

And again, I want to emphasize that I was just doing this as a first pass to get me pointed in the right general direction.  In other words, to quote Pat Murphy (the lead estimator at Murphy Company, where I worked for a while)

It’s an estimate, not an exatamate

Step 4

When I am out in the field, I have gotten into the habit of taking pictures of the nameplates of the machinery I am looking at.  In this case, that included the chillers because the chiller nameplates included a lot of very useful metrics, including the nominal chiller tonnage and the nominal kW at full load. 


As you can see, this can be a bit cryptic;  i.e. it does not say “nominal chiller tons”; rather there is a code with a number beside it;  in this case, NTON. 

I happen to be somewhat familiar with the Trane codes so I could read the information from the pictures I took of the nameplates.  But lacking that, usually, if you search around a bit on the internet, you can find something that explains manufacturer nameplate codes, either as a separate document  or in the form of their installation and operation manual for the equipment in question.  Here is an example for Trane centrifugal chillers.


The reason I needed this information was that I wanted to turn the run loaded amps into a tonnage, which I describe next. 

Step 5

To convert the percent run loaded amps to tons, I assumed the relationship between percent run loaded amps and % full load on the chiller was approximately linear.  In other words, if the chiller is at 50% run loaded amps, then it is at 50% of its nominal full load tonnage.

This is far from a perfect assumption, especially at low load conditions and especially if the chiller has hot gas bypass.  But for the equipment I was looking at, there was not hot gas bypass and the chillers, when running, were typically at 50% load or more. And, I will remind you again of Pat Murphy’s quote.

In any case, using this assumption, I added another column for each chiller and then for each minute in the data set, I estimated the load on the chiller in tons.  Finally, for each minute in the data set, I added up the tonnages of all of the chillers that were running, which gave me the total tons on the plant for each minute and allowed me to project a load profile and draw some preliminary conclusions.

Step 6

Aside from looking at the load profile pattern as a time series, I wanted to look at it in terms of some indicator of a driver behind the load.  For plants serving air handling systems with integrated economizer cycles and/or 100 percent outdoor air systems, up until the point where the economizer high limit kicks in, the load on the cooling coils is a 100% outdoor air load, meaning it is a direct function of the outdoor air enthalpy and a fairly direct function of the outdoor air temperature.

As a result, I wanted to be able to plot tons as a function of outdoor air temperature.  To do that, I needed outdoor air temperature data which I retrieved from a local ASOS site using the Iowa State University website I mention in the blog post titled Hourly Weather Data Website Update.

Once I had the weather data for a period that correlated with my chiller data period, I used the VLOOKUP function to add an outdoor air temperature value for each row (minute) in my chiller data set.

Step 7

I always try to do something to cross-check myself, especially when I am going quickly due to the pressure of time or making some pretty general assumptions, both of which were true in this case. Given the data on the evaporator graphic for the chiller (the second graphic I show above), I could also have digitized the evaporator entering and leaving temperatures and used the water side load equation to calculate the tons on each chiller.


To do that, I would need an evaporator flow rate.  It turns out there is there is a reasonable assumption I could make to provide that piece of information.

Specifically, since the plant is a variable flow primary/secondary plant, by design, the intent is for the flow through the evaporators to be constant no matter what the flow is in the distribution loop.  So assuming a constant flow rate for the evaporators simply reflects the plant design intent and is reasonable.  The question then becomes

What is the magnitude of the constant flow rate I am assuming?

Here is how I answered that question.

Since the discharge valves on the pumps were throttled, it was reasonable to assume that the flow being delivered by the pump was the nameplate (design flow). 

I say that because the reason balancers throttle pumps is that at their testing has demonstrated that the pump is delivering more flow than needed with the discharge valve wide open.  Most balancing specs and good practice require that if the balancer finds this condition, then they should throttle the pump to design since most of the time, this will save some energy (all though not as much as you would save by some other optimization strategy).2  

All of that means that time permitting, I could have digitized the evaporator temperature data, done the math, and compared the result I got using evaporator temperature drop data with the result I got using the percent run loaded amps data.  But, as I mentioned, I was under a pretty tight schedule and that process would have consumed some of my precious time. 

In fact, I initially had considered using the evaporator data but decided on the percent run loaded data instead because it would (in theory) get me similar results.  But to accomplish it, I would only need to digitize one line for each chiller, not two.  And, I would only need to do one VLOOKUP for each chiller, not two.  Given that there were 9 chillers in the plant, saving those steps represented a significant time savings.

However, I did spot check my results by randomly selecting some points in time and then comparing the actual logged evaporator temperature drop with the temperature drop I came up with based on:

  • Tons from my percent run loaded amps analysis, and
  • An assumption of design flow through the evaporator (the valves were in fact throttled as indicated by the white line being approximately in the middle of the window on the valve actuator)


These spot checks tended to validate each other, so I was reasonably comfortable moving forward with the data set.

Return to Contents

The Bottom Line

The bottom line was that my brainstorm about using plot digitizer to turn a photo of data into actual data paid off, allowing me to generate both a time series view of the plant load profile and related parameters …


… and scatter plots and regressions of the load profile patterns relative to outdoor air temperature.


I’m just about done with a post that takes a look at the clues that were revealed by the charts above.  But before I do that, I wanted to show you one other trick that came in handy for my effort working with the data I generated, specifically, how I created the third, number of chillers running axis in the time series chart.  That will be the topic of my next post.

Return to Contents

David-Signature1_thumb1_thumb                                                      PowerPoint-Generated-White_thumb2_th_thumb[1]

David Sellers
Senior Engineer – Facility Dynamics Engineering                                                                     Visit Our Commissioning Resources Website at http://www.av8rdas.com/

1.     For more on how Excel represents date and time, which you need to understand to be able to do this, see the blog post titled Good News about NWS Weather Data, Plus Working with Date and Time in Excel.

2.    For more about optimizing pumps, including case studies illustrating the steps and techniques, you may want to download and read two design briefs that you will find on the Energy Design Resources web site;  Centrifugal Pump Application and Optimization, and Pumping System Troubleshooting.

Posted in Boilers, Hot Water Systems, and Steam Systems, Chillers and Chilled Water Systems, Data Logging, Excel Techniques, HVAC Calculations, Motors, Operations and Maintenance | Leave a comment

A Control Logic Exercise and a Way to Get Comfortable With Navigating SketchUp Models

Plant V31 - Scenes Inside Bypass Answer 3

As many of you know, I have been experimenting with using SketchUp models as a way to teach EBCx techniques.  I frequently use them in my classes and also have started to post self-study exercises that are based on the models on our commissioning resources website.

The purpose of this post is two-fold.  One is to let you know that I just put up a new model and exercise that ties into the Control System Fundamentals slides I posted a while back.  In the exercise, you modify the control logic for the hot water system illustrated below to add a reset schedule, which will solve some comfort problems and also save some energy.

HW System Diagram

The model is also a good starter model for learning how to navigate in SketchUp since it is not as complex as some of the other models I use for existing building commissioning training.  Click here to jump to that part of the post.

Control Logic Exercise

If you are interested in giving that a try, I think you will find everything you need on the Bureaucratic Affairs Building Heating Hot Water System Logic Modification Exercise page, including the model, a description of the problem and the theory behind solving it, a description of the building, and other pertinent information.

SketchUp Scavenger Hunt

Once I posted the model I realized that it is also a good one for you to use to get comfortable navigating around in SketchUp in preparation for attending a class were I will be using models or if you want to try one of the self-study exercises.  The reason that this might be a good model to learn basic navigation in is that it is a relatively simple model.

If  you  use one of the scene tabs that turns off the walls of the building and/or other structural elements, there are not many things for you to collide with as you can see from this scene where the 2nd floor, as well as the walls, columns, and beams on the first floor have been turned off.

Tab 6 01

In SketchUp, you have super human powers and can pass right through a wall.  Once you are inside it, it can be very disorienting because there is nothing to focus your zooming and panning effort.

When that happens, remembering “Control” plus “Shift” plus “E”, which is the keyboard short-cut for “Zoom to Extents” is handy.  But you can totally avoid the problem if there is not much to run into in the first place, which is my point and why I say getting the hang of SketchUp navigation with this model might be a good way to go.

Once you get accustomed to things, you can work with more layers turned on to create a more realistic view of things, like this scene for instance, which is what it might look like if you had gone out to do some construction observation in the facility after the pipe and terminal units were hung but before the ductwork went in.

Construction Observation Scene

To make this all a bit more interesting, at the suggestion of Barry Estes, a friend of mine whom I work with at Marriott to provide technical training, I made an “Easter Egg” hunt out of it.  Meaning, I came up with a string of questions that require you to use basic SketchUp navigation skills like zooming, panning, orbiting and scene tabs to find the answers, along with some outside the box thinking and the ruler tool.

For this exercise, you won’t need all of the information provided on the web page to support the logic diagram exercise, just the model itself and the building description and history.  You can download those files individually at the link provided above, or you can just follow this link to get the files you need.

If you want to give it a try, here are the questions (they also show up on the page that is linked above).

  1. What is the size of the inlet duct on a typical terminal unit?
  2. How many steps are there from the first to the second floor?
  3. What are the hours of operation for the Department of Bureaucratic Affairs?
  4. Who manufactured the ladders that are being used on the project?
  5. Will the ladders float?
  6. Does the hot water system have any balance valves in it and if it does, who is the  manufacturer?
  7. For the finned tube radiation serving the West perimeter zone (Scene 12), what would you estimate that the pressure drop was through the balance valve if the flow is 7.4 gpm?
  8. Can you propose a reason for the issue noted in the header picture on the web page.  In other words, can you find a problem in the piping network that could be causing people at the East end of the 1st floor to complain of being cold when the rest of the building is comfortable?
  9. Are all of the terminal units the same and if not, why do you think there is a difference between them?
  10. Did you find any other “Easter Eggs”?  If so, what did you find?

I will answer the questions for the first time in a class this week, so once I have done that, I will publish them here too so you can see how you did.


PowerPoint Generated White_thumb[2]David Sellers
Senior Engineer – Facility Dynamics Engineering     Visit Our Commissioning Resources Website at http://www.av8rdas.com/

Posted in Controls, Retrocommissioning Findings, SketchUp Model Based Self Study | Leave a comment

A New Resource for Looking at Climate Data

So, some of you are probably thinking Thank God he has finally put something up that gets rid of that picture of him and Kathy on Christmas.  That did stay up for a bit longer than I had planned at the time.  But now that Valentine’s Day is past, I figured I really did need to move on from the romantic thing.

Frequently, when I am going some place I have never been before, I like to get a sense of what the climate might be like.   There are a bunch of ways I do that including the City Data site I mentioned in a previous post and the bin plot feature that you get with the Professional version of electronic psych chart tools like the one Ryan Stroupe has made available via the Pacific Energy Center.

For commissioning projects where I am trying to decide what trend data I want to have the operators pull for me to look at, I really like the nomographs that the National Weather Service has.   You can look at various locations on a month by month basis


… or an annual basis.


For the temperature chart, he red band represents the extreme high on record, the blue band represents the extreme low on record, and the green band represents the normal range.  The dark blue line is what happened for each day of the month, meaning it spans from the low for the day to the high for the day.

Once I find the chart for the area of interest, I look for:

  1. The month with the highest maximum temperature on record and then a day (the dark blue bar) when conditions approached that. 
  2. The month with the lowest minimum temperature on record and then a day when conditions approached that. 
  3. A couple of days during the swing seasons when the dark blue band spans a wide range, meaning the building and its systems saw a huge range of operating conditions.

I then ask for the trend data for those days because those days were probably the ones that challenged the systems in the facility the most, especially the day with the huge wing in temperatures.  Here is an example of that which I use when I teach.









Basically the systems in that building saw every conceivable operating mode in the course of 24 hours.  And they worked!  We went home feeling pretty good that day.   Had they not worked, the day would have been a nightmare.

For a number of the NWS regions, including the Western Region, you can find these charts pretty easily on the Regional forecast office home page.


One way to find the regional office home page for a given location  is to start at the NWS home page and put in a location.


From there you can get to the regional forecast office (it will be linked at the upper left corner of the page under the search box).


But for me at least, the problem has been that not every region seems to have a link to the nomographs off of their home page, an it would take me a while doing random searches to find them once I was there.  And sometimes, I simply could not find them.

But, after going through that recently for a project in Atlanta, Georgia, I discovered a location that seems to be common across the regions that has a very similar product that has some cool features.  So I thought I would take a minute to share that with you.

To find it, you need to get to the local regional climate office home page, just like I illustrated above.  But from there, you use the “Climate” tab, which so far seems to be consistent across regions.


Once you are on that page, you pick the NOWData tab.


NOWData is an acronym for NOAA Online Weather Data  and is the result of a joint project between the National Weather Service and the National and Regional Climate Centers.   Anyway, once you are on that page, you pick a location and the “Temperature Graph” feature and a year.


When you hit “GO” it will take you to the graph for the location you selected.


If you mouse over the graph, you can look at the data for individual days.


You can can also drag a window to zoom in on a certain period.


And you can use a drop-down to save the image in a bunch of different formats.


The next few images illustrate how a team I am working with used the data for Atlanta Georgia in a presentation to an Owner to help them understand the load profile we measured relative to what might happen other years.  This, in turn helped them understand our recommendation regarding a new cooling tower they are contemplating purchasing.




We made a two year graph by saving the charts for the two years we wanted to look at and then cropping the left edge of the right image (2018 data) and lining it up with the left image (2017 data) so that it appeared to be one continuous data stream.  We blocked the heading of the second image by simply placing a white rectangle over it.



We then cropped the image and used the Morph transition to focus in on the area we wanted to discuss .  We made the band outside our focus area look faded by putting white rectangles with the transparency set to 25% over those parts of the graph.




So a pretty cool and useful resource, at least for me.  Hopefully it will be for you too.


David Sellers
Senior Engineer – Facility Dynamics Engineering

Visit Our Commissioning Resources Website at http://www.av8rdas.com/

Posted in PowerPoint Techniques, Weather and Climate Resources | Leave a comment