Build Your Own Psych Chart – Creating the Data for the Saturation Curve and a Bit About VLOOKUP

So here I am, over a year later after starting this string, getting back to it.  All I can say is life happens and sometimes, because of that, you find that what you set out to do takes a bit longer than you thought it would when you set out to do it.   But thankfully, several folks reminded me about this and I mean to complete it, just like Bill (Coad), the inspiration for this, would have.

Having discussed some of the basic principles behind the psychrometric chart in the previous post, in this post, we will get started on the process of actually drawing the chart.  By the time we are finished (which I am guessing will take a couple of posts to accomplish), you will hopefully have everything you need to know to develop your own version of the graph below.

image

And you may even have an idea bout how to go the next step, which involves adding the relative humidity lines.

image

Contents

This post will focus on doing the calculations you need to do in order to have a data table to plot as your chart and configuring the chart axes.  The links below will take you to the indicated topics.  The (Return to Contents) link at the end of each section will bring you back here.

Setting Up the Axes

At its most fundamental level, a psych chart is a graph.  And in general, a graph is a picture you create by plotting points as a function of the parameters associated with at least two of the axes.  Typically, the axes are set at a 90° angle to each other and frequently, but not always, the points are connected with a line.

So, the first question you will likely ask yourself as you start down the path to making your on psych chart is what two parameters should I use for the axes?  There are a couple of ways to approach answering that question.  One would be to simply look at someone else’s chart and use what they used.

And while there is nothing particularly wrong with that (unless you are taking a test), since one of our goals in doing this is to understand the fundamentals behind the chart, it might be desirable to consider the question from a more fundamental perspective.  So, you might ask yourself, why am I interested in making this chart in the first place (self education goals aside).

(Return to Contents)

Charts and Their Axes

In the engineering and scientific world, most charts are built as tools to facilitate some sort of analysis or development process.  For example, when sizing a pipe, rather than having to calculate or otherwise assess a number of parameters and then use that information in a subsequent calculation for each flow rate, piping material and line size encountered, a designer may simply reference a pipe friction chart which presents this information graphically based on some fixed assumptions like the type of pipe.

image

Since flow and pressure drop due to flow are the key items of interest, those parameters are used for the axes.  Having established that, the relationship between the two can be plotted for different line sizes.   In addition, velocity, another parameter of interest can also be added by plotting constant velocity lines; i.e. lines that are based on the relationship between pressure and flow for a fixed velocity.

Or, a refrigeration mechanic or engineer may plot out a refrigeration cycle on a p-h (pressure-enthalpy) diagram and gain insight into how well the system they are working on is performing, how much power it will use, etc. even though they may not be familiar with the equations of state behind the chart.  Pressure and enthalpy are the axes of choice because they allow the cycle to be visualized easily.  For instance, in the ideal cycle below, you can quickly see the superheat caused by the compression process.

image

Another example of a reason for choosing axis parameters is in the context of a diagnostic plot like this one comparing energy consumption before and after a repair to an economizer control system

image

By comparing preheat energy consumption before (red) and after repairs were made (green) you can clearly see that there was an improvement.  Meaning that for any given outdoor air temperature, the system used  less heat once the control system had been fixed.  The trend line feature of Excel even allows you to develop the mathematical relationship between consumption and outdoor temperature and use it to extrapolate the results of the improvement beyond the data set.

Folks other than engineers select their axes based on what they are trying to visualize or understand too.  The Skew T/Log P chart meteorologists use to understand the atmosphere is an example I am learning about these days, given my interest in meteorology.  On this chart, the primary axes are temperature and atmospheric pressure/altitude which allow the changes in temperature and humidity with altitude measured by a radiosonde to be compared to the saturation conditions at altitude.

RAOB - 2014-10-01 12Z CURRENT.RAWINS ... 72694 - KSLE - SALEMMcNARY, OR US at 011200Z 1012014 14139 PM

The chart I used in the illustration is a software tool I have called RAOB that allows field data (the red and purple lines) be overlaid on the basic SkewT/LogP plot (the tan and green lines).  Its a tool very similar to the electronic psych chart I described in the previous post.  But back in the olden days, the field data was plotted manually on a paper version of the chart, just like we did with paper psych charts.

The bottom line, at least in my perspective, is that the decision regarding what to use for an axis on a chart is driven by what you can readily measure, what you want to know, and the ability to visualize data in a meaningful manner.

(Return to Contents)

The Psych Chart Axes

Two of the key things we try to understand in HVAC processes and systems are often related to the temperature and moisture content of the air they are handling.  Since a psych chart is a tool to facilitate that understanding, we might conclude that using temperature and some parameter that is representative of moisture for our axes would be desirable.

Temperature is something we can measure fairly easily in the field.  And, in the previous post in this string, we touched on the fact that there is a psychrometric parameter termed specific humidity that is the ratio of pounds of water per pound of air in a given sample.

You will also recall that if you knew the temperature of a sample of air and had a copy of Keenan and Keyes or some other version of a steam table, then you could calculate the specific humidity associated with a saturated condition via the following relationship.

image

Given that our current goal is to plot the saturation curve on a psych chart, it would seem that if we selected temperature and specific humidity as the axes, we might have a way to do that.   Specifically, we could:

  1. Select a number of arbitrary temperatures, maybe 40 – 100°F in 10°F increments since a lot of our HVAC systems operate in that range.
  2. Assume a fixed atmospheric pressure.
  3. Use Keenan and Keys to determine the saturation pressure for water vapor at those temperatures.
  4. Calculate the partial pressure of the air at saturation by subtracting the vapor pressure at saturation that you looked up in your steam tables from the total pressure you have assumed (atmospheric pressure).
  5. Calculate the specific humidity at saturation for each of the temperatures.
  6. Plot the points and connect them with a line.

(Return to Contents)

Doing the Math to Create the Data Table

The Data Table

Excel charts are typically plots of data from a table.   So to make our chart, we will need a table that has a pair of data points in it for each point we want to plot on the chart.  Usually, at least for me, it is helpful to have the table include any intermediate data used to develop the data point of interest.  This is the table I developed to make the saturation curve on my chart.  (Remember, if you click on an image in the blog, it will open up in a separate window at a larger scale).

image

The columns follow the steps I outlined in the previous section.  If you don’t have a copy of Keenan and Keyes or some other version of the steam tables, fear not.  I used my copy of REFPROP to create one that you can download from the public space on my Google Drive.

(Return to Contents)

VLOOKUP;  A Handy Excel Function

Personally, I think its worth having a copy of Keenan and Keyes in your engineering library,  and I take a certain nostalgic comfort in using mine.  But the truth is, that if you are working in a spreadsheet, having the data electronically is handy.  Here’s why.

It turns out that there are a number of functions in Excel that let you look up information in a different table and insert it into your table.  If you only had to look up a couple of things, its not much of a time saver.

But, when the number of things you need to look up gets larger, for example:

  • The 10-15 times you have to consult a steam table to get data to draw your saturation line, or
  • The hundreds or thousands of times you might need to look something up based on parameters in a set of logger data,

the Excel lookup functions start to save you time and/or make something that is impractical on a manual basis totally possible.

The function I used to help build the table in the spreadsheet above is VLOOKUP.  Specifically, I use VLOOKUP to go to a different tab in the spreadsheet where there is a steam table that I created with REFPROP and look up a value from the steam table and insert it into my table.  (The steam table is the one I put on my Google Drive so you could download it.)

This slide shows a zoomed in view of my table so you can see things a little more clearly.  The formula that is enlarged is the formula in cell D:243 (the one that has the black outline around it).

image

Basically, what the formula is doing is looking for the number that is in cell B:243 (highlighted in blue;  a different cell in the same row as the cell with the formula). It looks for that number in a table that is on the workbook tab ‘Water at Saturation 35-220F .01” (highlighted in green). 

Once it gets to that tab and table, it looks at the range of cells starting at K8 and going all the way to Q18806 (I’ll explain the dollar signs in a minute).  The the first 25 or so rows of the range are highlighted in orange below;  I won’t try to illustrate all 18,000 plus rows and will figure that you will get the idea from the illustration. 

image

Note that the first row of the range you specify needs to have the parameter you are using as a reference in it. Meaning that since I am going to look up a pressure that I don’t know based on a temperature I do know (the temperature is the value in cell B243/the values in column B of the table I am making), then temperature column needs to be the first column in the range I select for the VLOOKUP to use.

Also note that the data in your reference column in the range you specify needs to be sorted in ascending order (smallest at the top to largest at the bottom of the range).

The 2 in the formula (highlighted in purple) tells Excel that the data you are looking for is in the second column to the right of the column that has your reference parameter in it.  In other words, in the range I selected, temperature (what I know) is in the first column (I have highlighted the column header in blue below).  I am trying to find out the saturation pressure associated with a given temperature and that information is in the second column (highlighted in purple below).

image

If I wanted to know the latent heat associated with the phase change from liquid to vapor (the number in column Q, which is highlighted in yellow), then I would have put a “7” in the formula instead of a “2” since column Q is the seventh column to the right of the temperature column, which is my reference.

In human terms, what the formula says is to go to the steam table that contained on the Water at Saturation 35-220F .01 tab in the spreadsheet and go down column K until you run into 40°F (the value in cell B:243).  Once you find that, look in column L (the 2nd column in the range of K8 through Q18806  that you defined in the formula) and put the value you find there in the cell with the formula.

One more thing;   when you enter a formula in Excel, it is often convenient to use the Functions Arguments dialog box, which you can open by clicking on the little fx symbol in the formula bar.

image

If you do that for VLOOKUP, you will discover there is one other optional parameter that you can enter after telling Excel which column the data you want is located in (the 2 in our formula).   If you omit this parameter or put the word “True” in as the last parameter in the formula, then Excel will start at the top of your reference row and search down it until it comes to the closest match to the number you are looking for.   If you want Excel to only pick up an exact match, then you need to put the word “False” into the formula as the last parameter.

As far as I can tell, the way Excel determines it has the closest match is that it goes down the column with your reference parameter in it.  If it finds an exact match, it looks in the column where you have told it the data you are interested in is and returns your data.  If it doesn’t find an exact match, Excel keeps going until it finds the first instance of something larger, then goes back one row and used the data associated with that row.

(Return to Contents)

Absolute vs. Relative References

That all sounds more complicated than it actually is once you try it.  But one thing that can become a “gotcha” is related to the dollar signs ($).  The dollar signs ($) in the formula are important and not the default that you will get if you select the range using your mouse.

They make the reference to the array an absolute reference vs. a relative reference.   In other words, if you copy and paste the formula into a bunch of rows below the one where it is entered, the $ signs let Excel know that you always want it always (and absolutely) look at the range K8 through Q18806 in the array on the Water at Saturation 35-220F .01 tab.

If you did not use the $ sign, Excel would consider the reference to be relative to the row and column that the formula was in. Meaning that if you pasted the formula from cell D243 to cell D244, Excel would shift the range that it looked things up in from K8 through Q18806 to K9 through Q18807 (i.e. it would shift it one row down).

That means if you used a relative reference in the formula (no $ signs), if you pasted the formula into 1,000 rows below the one where you entered it, then in the last row, Excel would think the array you wanted it to look in was K1008 through Q19806, not K8 through Q18806.  That could be a problem.

Related to all of this, I have found the books by Mark Moore on Mastering Excel to be pretty helpful.  I have a number of them on my Kindle including the one on VLOOKUP.  For the price, to me, they are really worth it and well written.

(Return to Contents)

The Bottom Line on Doing the Math

I diverged a bit to discuss VLOOKUP and  you certainly don’t need to use it to build your table and draw your chart.  But I tend to learn by doing things.  So my thought was that if the same were  true for you, then trying your hand at using VLOOKUP to bring the 10 or so values  you need into your table automatically may be a good way to learn the function and a lot less intimidating than trying to do it on the hundreds or thousands of rows of data associated with a logger file where you might want to use the function.

But bottom line, you will be fine for making your chart if you manually enter the data for saturation pressure into your table  or us VLOOKUP;  the choice is yours.

Going through the steps to create a table similar to the one I show earlier in the post should set you up to actually start the process of drawing your chart, a topic I will take up in the next post.

(Excitement, Excitement, Excitement)

(Return to Contents)

David-Signature1_thumb1_thumb

David Sellers
Senior Engineer – Facility Dynamics Engineering
PowerPoint-Generated-White_thumb1_th

This entry was posted in Excel Techniques, HVAC Calculations, HVAC Fundamentals, Psychrometrics. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s