# Chemical spreadsheets.

It has often been said that one program, above all others, is
responsible for the growth of the personal computer industry. That
program is the spreadsheet. I know people will argue about which
spreadsheet is the best and all that, but the general feeling is that
VisiCalc made the Apple system flourish and Lotus 1-2-3 did the same
with the IBM PC. The choice of spreadsheet is often dictated by fashion
and the choices made by business often move with the wind. Today, there
seems to be a big move from Lotus toward Excel. I've used several
versions of both Lotus and Excel and can't understand why people
would make such a switch. We may (and frequently do) argue about the two
products on a feature-by-feature comparison, but this usually ends up as
a personal choice. To me there is one dominant factor that seems to be
ignored. When you look in terms of the number of viruses that can attack
the two programs, I'm not sure that I would consider Excel as an
ideal choice. I remember reading an article a few months ago that said
the count of viruses specific to Excel and Word had passed the
two-thousand mark. I know of no Lotus-specific viruses.

It really doesn't matter which one you use to get results; just be aware that with the exception of the simplest of spreadsheets, there are numerous incompatibilities between them and believe it or not, between individual versions of the same spreadsheet. These incompatibilities really show when they contain anything of a graphical nature. I recently upgraded to the latest Millennium edition of SmartSuite and I believe that makes this version 9.0. Somehow, either Lotus or I lost count between 5.0 and this latest version. I sure don't remember all of 6.0, 7.0 and 8.0 ever making the scene. I find this version reasonably well put together and without some of the booboos that crept in while they were making the transition to Windows 95. Some of those booboos were annoying and I had actually started an effort to improve my knowledge of Excel with the thought of converting things over to it. With the improvements in version 9.0 of Lotus, I decided to stop beating my head against the wall and go back home to Lotus.

There are a few things I must say about spreadsheets. We should recognize that they are not really aimed at the scientific community, partly because we take what we get and never complain, other than to ourselves. With the DOS versions of Lotus, we were the brains who set up a graph. We told it what was the x-axis and what was the y-axis and what was plotted against what. Today, we can't do that any more as we need one of those wizards to tell us what to do and then it goes ahead and does it. We're then stuck with the job of making that graph into something that meets our needs. Think how many years they've been making spreadsheets and they still can't give you anything better than a 1.0E+006 for a simple 1.0 x [10.sup.6].

Having thrown out a few frustrations, let's look at some interesting features available in a spreadsheet. Most of us have used spreadsheets to store and manipulate data. One feature that must never be overlooked is the fact that you can have 256 columns in a sheet. This means that you can always do something more with the data and there are very few limits as to what can be added ill the way of extra calculations. Take as an example, the collection of data to monitor the chemistry of a major boiler. It is common to measure chloride at four points, the make-up, the feedwater, the boiler blowdown line and the condensate. Most boiler operators are given specifications for the chloride at these points. They will monitor these values religiously and open or close the blowdown valve to maintain the chloride within the required specifications. I have visited the physical plants within a number of industries and universities and that's exactly what they are doing ... and they do use spreadsheets to follow these numbers.

The concentration of chloride is a rather esoteric term with little or no meaning to most people. The job of a university and college president is to complain about shortages of money, not high or low concentrations of chloride. Why then, should anyone set up a spreadsheet just to monitor chloride when we have all these extra columns? A spreadsheet is an almost infinite electronic scratch pad with all kinds of capacity to interlink calculations. It only takes a few simple calculations to convert those chloride values into a mass balance that yields the percentages of the steam flow that goes to the sewer via the blowdown valve or disappears somewhere on the campus because condensate is lost and never returned. Add those few extra columns and the spreadsheet is starting to tell us something. As those losses contain energy, add a few more calculations to include the fuel and water-treatment costs and you suddenly have a number that is in the basic unit that a university president can understand. Instead of chloride, we can have the number of dollars needed to run the system. You know what it costs; go out and find ways to save some of that money.

Must a Spreadsheet Look Like a Spreadsheet?

There is no standard format for a spreadsheet. We get used to them as tables of numbers. We can set them up in almost any format we need. Figure 1 shows a Lotus spreadsheet set up as a system flowsheet. The various boxes and lines are prepared using the formatting options available in the spreadsheet program. In the Figure, the cursor is sitting on the value for the %Losses which is in cell M3. The formula subtracts the calculated value for the % Blowdown in cell 126 from the calculated value for % Make-up in cell B20.

%L = %MU - %BD

= B20-I26

= 21.7-2.17 = 19.5%

The limitation to this format is the requirement that lines and rectangles can run only along the cell walls. There is no method to place a line in the middle of a cell or to produce diagonal lines or curves or circular shapes. The shape limitations can be overcome by cutting and pasting a graphics image over the spreadsheet. While a graphics overlay does produce a better flowsheet, a number of factors must be considered when using it. Before you can make entries on the spreadsheet, the background colour, for the image, must be set to transparent by altering the properties of graphic image within the spreadsheet. The spreadsheet cells that will be used must line up with the graphics image and doing so can be a rather tricky. The cell contents and lines will translate between Lotus and Excel, with little changes other than the line weights. The graphics overlay will be totally lost in any such conversion. It follows that if you can produce a spreadsheet, you can make a variety of tables and forms using the same technique.

If You Have an Equation, a Spreadsheet Can Work With It

The equations needed to calculate pH vs. Temperature or the concentrations of various carbonate, phosphate and other species vs. pH are known and published. These can readily be converted into equations that can be handled by a spreadsheet. Generally, you can start by calculating a single value. Lay out the steps in a row and then you can copy that row many times to cover a range. In Figure 2, the calculation shows the abundance of mono, di and trisodium phospate over the range of pH from 6 to 14 in 0.5 pH steps. The calculations were taken from the 1974 data of Sweeton, Mesmer and Baes, Journal of Solution Chemistry, 3(3): 191-213 and 3(4):307-321. Other equations may be more complex, but all that means is that you need to go through a few more steps within the calculation and there's lots of capability available within the spreadsheet to do that.

What if You Don't Have an Equation?

There's another trick I've tried when I didn't have an equation and I needed a nice and neat graph showing how concentration varied over pH. I had only a photocopy of a graph to deal with. All you need is a crude set of axis and you can enter the points by their x-y coordinates to get a graph. Sometimes you might get a few points that are off a bit. Just adjust their values a bit to smooth the graph. In tile end, you can get a fairly good looking curve. Figures 3a and 3b show a simple photocopy that became a custom graphic. The original Figure was taken from the Permutit Water and Waste Treatment Data Book, published by US Filter Corporation.

How About Some of Your Spreadsheet Tricks?

Do any of you have any good spreadsheet tricks? Send them to Chemputing. We expect that many of our readers have some good ideas and would be glad to print them in a future issue.

It really doesn't matter which one you use to get results; just be aware that with the exception of the simplest of spreadsheets, there are numerous incompatibilities between them and believe it or not, between individual versions of the same spreadsheet. These incompatibilities really show when they contain anything of a graphical nature. I recently upgraded to the latest Millennium edition of SmartSuite and I believe that makes this version 9.0. Somehow, either Lotus or I lost count between 5.0 and this latest version. I sure don't remember all of 6.0, 7.0 and 8.0 ever making the scene. I find this version reasonably well put together and without some of the booboos that crept in while they were making the transition to Windows 95. Some of those booboos were annoying and I had actually started an effort to improve my knowledge of Excel with the thought of converting things over to it. With the improvements in version 9.0 of Lotus, I decided to stop beating my head against the wall and go back home to Lotus.

There are a few things I must say about spreadsheets. We should recognize that they are not really aimed at the scientific community, partly because we take what we get and never complain, other than to ourselves. With the DOS versions of Lotus, we were the brains who set up a graph. We told it what was the x-axis and what was the y-axis and what was plotted against what. Today, we can't do that any more as we need one of those wizards to tell us what to do and then it goes ahead and does it. We're then stuck with the job of making that graph into something that meets our needs. Think how many years they've been making spreadsheets and they still can't give you anything better than a 1.0E+006 for a simple 1.0 x [10.sup.6].

Having thrown out a few frustrations, let's look at some interesting features available in a spreadsheet. Most of us have used spreadsheets to store and manipulate data. One feature that must never be overlooked is the fact that you can have 256 columns in a sheet. This means that you can always do something more with the data and there are very few limits as to what can be added ill the way of extra calculations. Take as an example, the collection of data to monitor the chemistry of a major boiler. It is common to measure chloride at four points, the make-up, the feedwater, the boiler blowdown line and the condensate. Most boiler operators are given specifications for the chloride at these points. They will monitor these values religiously and open or close the blowdown valve to maintain the chloride within the required specifications. I have visited the physical plants within a number of industries and universities and that's exactly what they are doing ... and they do use spreadsheets to follow these numbers.

The concentration of chloride is a rather esoteric term with little or no meaning to most people. The job of a university and college president is to complain about shortages of money, not high or low concentrations of chloride. Why then, should anyone set up a spreadsheet just to monitor chloride when we have all these extra columns? A spreadsheet is an almost infinite electronic scratch pad with all kinds of capacity to interlink calculations. It only takes a few simple calculations to convert those chloride values into a mass balance that yields the percentages of the steam flow that goes to the sewer via the blowdown valve or disappears somewhere on the campus because condensate is lost and never returned. Add those few extra columns and the spreadsheet is starting to tell us something. As those losses contain energy, add a few more calculations to include the fuel and water-treatment costs and you suddenly have a number that is in the basic unit that a university president can understand. Instead of chloride, we can have the number of dollars needed to run the system. You know what it costs; go out and find ways to save some of that money.

Must a Spreadsheet Look Like a Spreadsheet?

There is no standard format for a spreadsheet. We get used to them as tables of numbers. We can set them up in almost any format we need. Figure 1 shows a Lotus spreadsheet set up as a system flowsheet. The various boxes and lines are prepared using the formatting options available in the spreadsheet program. In the Figure, the cursor is sitting on the value for the %Losses which is in cell M3. The formula subtracts the calculated value for the % Blowdown in cell 126 from the calculated value for % Make-up in cell B20.

%L = %MU - %BD

= B20-I26

= 21.7-2.17 = 19.5%

The limitation to this format is the requirement that lines and rectangles can run only along the cell walls. There is no method to place a line in the middle of a cell or to produce diagonal lines or curves or circular shapes. The shape limitations can be overcome by cutting and pasting a graphics image over the spreadsheet. While a graphics overlay does produce a better flowsheet, a number of factors must be considered when using it. Before you can make entries on the spreadsheet, the background colour, for the image, must be set to transparent by altering the properties of graphic image within the spreadsheet. The spreadsheet cells that will be used must line up with the graphics image and doing so can be a rather tricky. The cell contents and lines will translate between Lotus and Excel, with little changes other than the line weights. The graphics overlay will be totally lost in any such conversion. It follows that if you can produce a spreadsheet, you can make a variety of tables and forms using the same technique.

If You Have an Equation, a Spreadsheet Can Work With It

The equations needed to calculate pH vs. Temperature or the concentrations of various carbonate, phosphate and other species vs. pH are known and published. These can readily be converted into equations that can be handled by a spreadsheet. Generally, you can start by calculating a single value. Lay out the steps in a row and then you can copy that row many times to cover a range. In Figure 2, the calculation shows the abundance of mono, di and trisodium phospate over the range of pH from 6 to 14 in 0.5 pH steps. The calculations were taken from the 1974 data of Sweeton, Mesmer and Baes, Journal of Solution Chemistry, 3(3): 191-213 and 3(4):307-321. Other equations may be more complex, but all that means is that you need to go through a few more steps within the calculation and there's lots of capability available within the spreadsheet to do that.

What if You Don't Have an Equation?

There's another trick I've tried when I didn't have an equation and I needed a nice and neat graph showing how concentration varied over pH. I had only a photocopy of a graph to deal with. All you need is a crude set of axis and you can enter the points by their x-y coordinates to get a graph. Sometimes you might get a few points that are off a bit. Just adjust their values a bit to smooth the graph. In tile end, you can get a fairly good looking curve. Figures 3a and 3b show a simple photocopy that became a custom graphic. The original Figure was taken from the Permutit Water and Waste Treatment Data Book, published by US Filter Corporation.

How About Some of Your Spreadsheet Tricks?

Do any of you have any good spreadsheet tricks? Send them to Chemputing. We expect that many of our readers have some good ideas and would be glad to print them in a future issue.

Printer friendly Cite/link Email Feedback | |

Author: | Silbert, Marvin D. |
---|---|

Publication: | Canadian Chemical News |

Geographic Code: | 1CANA |

Date: | May 1, 1999 |

Words: | 1539 |

Previous Article: | Living in the post-clockwork universe: numeral modelling and perceptions of uncertainty. |

Next Article: | Phero Tech Inc. |

Topics: |

## Reader Opinion