Energy Auditor Spreadsheet

Back in the 80’s, a giant Department of Energy study developed a series of graphical “nomographs” (a graph with several inputs, that outputs a single result, kind of like a computer-on-paper) for architects and engineers to use for energy audits of large, commercial buildings. This was before the PC-era. Now they have hugely complex computer programs to do energy audits that take weeks to input all the data, and an engineer with a master’s degree to understand it all.

You can download the Excel spreadsheetYou can study a few case studies using this application to see just how powerful it is. You can use my program with practically no experience, and it takes only a day or so to input all the numbers for a large building. The difference in the result cranked out by the monster computer programs is fractional.

” See how easy that was ? “

Even after the computer methods came out, many engineers continued to use the nomographs, well into the new millennium. They’re accurate, easy to understand, easy to defend in court in a liability case – so you can sleep better at night (when you plug in a thousand variables into a huge computer monster, you never can really be totally confident of the result); and, most important, very easy to show a client to authenticate your calculations for projects.

Energy auditors, engineers who evaluate existing buildings in an effort to reduce their utility bills, are perceived in the industry like everybody else views the IRS. Facility managers and building operators hate energy auditors, and always suspect our calculations (because there are a million scam artists out there trying to sell them crap to make a fast buck), so if you have some nice graphics they can eyeball and see where the savings come from, it’s that much easier to convince people to do your project and save lots of energy.

I worked for a consulting firm in Austin, Texas that had a contract with the State of Texas to do energy audits of small hospitals around the state in the Small Hospital Energy Management Program. We built a series of spreadsheets to do all the energy-audit calculations. I took each of the nomographs and converted it into a mathematical equation, which another engineer built into a spreadsheet to automate the calculations.

A year or so into grad school, I re-did the equations, making them a lot more accurate; and then combined all of these calculations into a one-page excel spreadsheet. I waited for the right moment to propose modifying the constants in this method so that it could be used to design structures on Mars. (Or Antarctica.)

British Antarctic Survey’s Haley IV Station

You can download that Excel spreadsheet here. (These equations were also used in my one of my Bar X Software Windows programs.)

All the engineering reference data you need to input into the spreadsheet is right there on extra pages, linked on the bottom tab. These calculations are for the building “envelope” so you can estimate the annual energy savings from different projects. Some common projects that you can evaluate for your home or business include:

  1. changing the roof color (reflectance)
  2. internal or external shading of windows
  3. higher-insulation windows or doors
  4. changing the color of the exterior walls
  5. adjusting the heating and cooling thermostat settings

I did a talk on these nomographs and the method I developed at a big engineering conference in Dallas one year. I’ve summarized my talk in this PDF document here so you can see how it works. I’ve also attached all the nomographs I used, along with a page showing how I derived the equations used in the spreadsheet. You can either use the canned version or build your own spreadsheet using the equations I developed. If you build your own, you can then check the accuracy of the equations versus the nomographs, so you can be fully confident in the result (and defend yourself in court, if it comes to that).

Futuristic Antarctic Station

The spreadsheet has a worldwide database, although most of the locations are in North America; just select the city nearest to you and plug the city’s number into the calculations page. The results are quite accurate; if they’re good enough for funding projects at a small hospital, they’re plenty good enough for your home or small business.

If you’re going to do any projects based on the calculations using the spreadsheet, please be sure to have an engineer or contractor review your calculations to be sure you did everything right. They’ll usually do this for free, and might even thank you for turning them onto this cool little spreadsheet program.

This program has only three weather inputs: langleys (a measure of solar insolation), and heating/cooling degree days (the number of degree-hours converted to degree-days that a building needs to be heated or cooled). So the method is customized to your location and the annual weather at that location. If your city isn’t in the database, you can get the constants you need from a local weather station or architectural engineering firm.

Princess Elisabeth Station