Does the name Visicalc sound familiar to any of you? It was the original spreadsheet program for personal computers that first appeared back in the early 1980s. Accountants, who were the prime users of paper and pencil spreadsheets back then, hailed the program as a near miracle. As the pace of development of personal computers accelerated, more and more spreadsheet programs appeared. Number-crunchers like engineers and scientists began to see the power and versatility of spreadsheet software.

Today, just about every personal computer that ships with bundled software includes a spreadsheet program. Even the simpler ones are more than capable of serving as a design tool for many routine tasks involved in hydronic system design.

Last month we discussed an improved method for sizing hydronic fin-tube baseboards connected in a series piping circuit. The key concept was to account for the temperature drop of the water as it passed from one baseboard to the next. This prevents oversized baseboards near the beginning of the circuit and, even worse, underheated rooms at the end of the circuit.

The manual calculations for this method involve setting up a table and using an assortment of formulas to calculate values across each row. The number in the last column of a given row is then copied down to the second column of the next row, and the process was repeated. This means that all calculated values at a given location in the table depend on the previously calculated values. Any mistake(s) early on invalidates the remaining calculations. It’s tremendously frustrating to find after you’ve spent half an hour warming the keys on your calculator that a mistake way back in the first or second row has messed up all the calculations that followed.

Enter the spreadsheet. Think of it as a tireless customized calculator that can do in a second what would take you over an hour. And if properly set up it won’t make the same math mistakes we humans are prone to. It also doesn’t care how many times you want to recalculate the entire design based on possible changes to loads, flow rates, etc.

I’ve been using a spreadsheet that comes as part of an integrated office program called ClarisWorks®. I’ve shown the layout I’ve developed to size up to 10 series-connected baseboards in Figure 1. The spreadsheet closely resembles the baseboard sizing table from last month’s column, only it has more rows to handle more baseboards if necessary.

The spreadsheet was formatted to show information in two colors. All cells that contain blue information can be changed by the user. These cells typically contain input information that’s needed by the formulas, such as room heating loads, circuit flow rate, and supply temperature to the first baseboard.

All cells that contain red information are not to be changed by the user. These cells contain either calculated results or headings. Incidentally, most spreadsheets allow you to easily “lock” such cells to prevent accidentally changing information or formulas used in the calculations.

To use the spreadsheet, enter the rating information for the baseboard and the circuit flow rate in the “SYSTEM INPUTS” frame in the upper left corner. Then type in the room names, entering air temperatures, and room heating loads in column B, C and D, respectively.

Most spreadsheets are set by default to “autocalculate” the results. This means that when you change the information in any cell, and then hit the return key, or move to another cell, all formulas are executed, and the results shown in all other cells are updated to reflect the effect of the change.

You can also disable the autocalculate function if you want. This allows you to enter all the inputs, and then — and only then — have the program calculate the results. This approach has the advantage that you don’t have to look at sometimes meaningless results as you make all the entries that properly describe the system. Take your choice.

Because the design process is sequential in nature, it’s extremely important that the room information is entered in the same order as the baseboards will be connected into the circuit. The top row is for the first baseboard on the circuit, the second row for the second baseboard, and so forth. This is not to say, however, that various rearrangements of the baseboards on the circuit cannot be evaluated. They can. Just remember the results will be based on the same sequence in which the room information appears.

The formulas used to create the spreadsheet are shown in Figure 2. Each formula appears within the cell that it calculates the value of when the program runs. Keep in mind that not all spreadsheets use exactly the same “syntax” for writing formulas. For example, ClarisWorks® uses the formula = SUM (D11..D20) to tell the program to add up the values in column D, rows 11 through 20. Check your own spreadsheet manual or help system to confirm the proper syntax for your program.

This spreadsheet is not meant to be the “ultimate layout” for designing a baseboard system. It has been kept simple to show the basic concept and not rely on specialized logic functions that might not be available in some spreadsheets. Here are some things that could be done to jazz it up a bit:

Set up limits on the input cells that would prevent erroneous or out-of-range inputs. Remember, the often cited computer axiom “garbage in equals garbage out” still applies. Incorporate columns that total up a bill of materials including lengths of baseboard, number of end caps, baseboard tees, etc. Another column could total up material prices on the fly as the system is designed. Set up a calculation that would allow you to specify the piping, fittings, and valves in the circuit, as well as the pump curve for a candidate circulator, and then solve for the resulting flow rate. This one will take a bit of mathematical finesse, but is entirely possible for serious spreadsheet connoisseurs. Feel The Power: Once the spreadsheet has been set up it’s time to see how easy design possibilities can be tested. For example, suppose we use the results of Figure 1 as a reference, but want to see the effect of lowering the system flow rate from 4.5 to 1 gpm. Just click on the cell to the right of CIRCUIT FLOW RATE (gpm), enter 1, and press return. If you’ve got the spreadsheet set for autocalculate all columns are immediately updated. The previous results of 90 feet of baseboard with an overall circuit temperature drop of 18.9 change to indicate 162 feet of baseboard are now required, and the circuit temperature drop would be a whopping 83 degrees F. Although this design is possible, take a look at the linear footage of baseboard required in the last couple of rooms. Such a design would clearly be impractical from both a cost and available wall space standpoint. It does, however, demonstrate the kind of “what if” questions that can be quickly answered with the spreadsheet.

How about if we go back to 4.5 gpm flow rate, but this time lower the supply temperature to the first baseboard to 150 degrees F? Change one number, hit return and see that a total of 141 feet of baseboard is now needed, but the circuit temperature drop is back down to 18.5 degrees. F (see Figure 3). Of course you can also examine the changes on a room-by-room basis.

Finally, suppose the last room on the baseboard circuit is a garage with a design heating load of 15,000 Btu/hour, when its inside air temperature is 45 degrees F. Assuming the air entering the baseboard at floor level is about 40 degrees F, we change the room information, hit return and find the garage will need 27 feet of baseboard. We’ve only touched on a few of the possibilities this type of simple spreadsheet offers. The time you spend learning how to use it could pay itself back on a single job, especially if it helps you avoid a poorly performing system. Try it, you’ll be convinced.