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 A PC made by IBM. IBM created the PC industry in 1981 when it introduced its first model with 16KB of RAM. However, it was way off in its estimates, projecting that 250,000 units would be sold in the first five years. In fact, about three million IBM PCs were sold in that period. . 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 crept
Past tense and past participle of creep.
the past of creep
crept creep 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 Following is a list of DOS versions since its inception in 1981:
Version Major new features 1.0 1981 8-sector 160KB floppy (SS). 1.05 Bug fix. 1.1 1982 8-sector 320KB floppy (DS). 2.0 1983 10M hard disk, 9-sector 360KB floppy, directories, more batch commands. 2. 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 chloride (klōr`īd, klôr`–), chemical compound containing chlorine. Most chlorides are salts that are formed either by direct union of chlorine with a metal or by reaction of hydrochloric acid (a water solution of hydrogen chloride) at four points, the make-up, the feedwater, the boiler blowdown line and the condensate condensate, matter in the form of a gas of atoms, molecules, or elementary particles that have been so chilled that their motion is virtually halted and as a consequence they lose their separate identities and merge into a single entity. . 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 es·o·ter·ic
a. Intended for or understood by only a particular group: an esoteric cult. See Synonyms at mysterious.
b. 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 scratch pad
1. A pad of paper for preliminary or hasty writing, notes, or sketches.
2. also scratch·pad Computer Science An internal register used for temporary storage of preliminary data or notes. with all kinds of capacity to interlink INTERLINK - A commercial product comprising hardware and software for file transfer between IBM and VAX computers. 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 (1) The symbol used to point to some element on screen. On Windows, Mac and other graphics-based screens, it is also called a "pointer," and it changes shape as it is moved with the mouse into different areas of the application. 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
= 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 (1) A preprinted, precut form placed over a screen, key or tablet for identification purposes. See keyboard template.
(2) A program segment called into memory when required. 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 phosphate, salt or ester of phosphoric acid, H3PO4. Because phosphoric acid is tribasic (having three replaceable hydrogen atoms), it forms monophosphate, diphosphate, and triphosphate salts in which one, two, or three of the hydrogens of the 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 (1) See monochrome and monophonic.
(2) (Mono) An open source implementation of the .NET environment for Linux, Unix and Windows platforms, sponsored by Novell. Mono includes a C# compiler and a Common Language Infrastructure (CLI) runtime engine. , di and trisodium tri·so·di·um
Containing three sodium atoms. 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.