Spreadsheet relative reference

The formula = A 8 B5 refers to cells A8 and B5 in different manners. A 8 is an absolute reference to the contents of cell A8. No matter where cell A 8 is called from in the spreadsheet, the computer goes to cell A8 to look for a number. "B5 is a relative reference in the formula in cell C5. When called from cell C5, the computer goes to cell B5 to find a number. When called from cell C6, the computer goes to cell B6 to look for a number. If called from cell Cl9, the computer would look in cell B19. This is why the cell written without dollar signs is called a relative reference. If you want the computer to always look only in cell B5, then you should write B 5 ... 

Use Apply Names (Figure 3-10) if you have created a spreadsheet with formulas using cell references and now want to replace the cell references with names. First, use Define Name or Create Names to assign names to the references. Then choose Apply Names. The names that you have assigned will be shown in the list box. The Ignore Relative/Absolute box should usually be checked. Select a name from the list and press OK. All cells containing the reference will be replaced by the name. 

As an example of a weak acid problem that can readily be solved by circular reference and iteration, consider the calculation of the pH of a 0.1000 M solution of sodium bisulfate, NaHS04. The bisulfate ion is a relatively strong weak acid with Ka = 1.2 X 10 . (Roughly speaking, weak acids with values greater than 10 will require the quadratic or successive approximations approach.) The spreadsheet fragment in Figure 10-13 illustrates a case of a cell that contains a circular reference. Cell C5 contains the formula =SQRT( C 3 (A5-C5)). 

D9. A distillation column is separating a feed that is 20 mol% methanol, 50 mol% n-propanol, and 30 mol% n-butanol. Feed is 750 kmol/h. We want a 92 mol% recovery of methanol in the distillate and 95% recovery of n-propanol in the bottoms. Use L/D = 7. Feed is a saturated liquid. The column has a total condenser and a partial reboiler. As a guess, assume that n-butanol does not distribute (it all exits in the bottoms). Do not do iterations to inprove this guess. If we choose n-propanol as the reference, the relative volatilities are methanol = 3.58, n-propanol = 1.0, and n-butanol = 0.412. Assume relative volatilities are constant. Do not use Aspen Plus. This calculation is single enough that it can be done by hand or with a spreadsheet (with or without Visual Basic for Applications [VBA]) or with MATLAB. 

The spreadsheet results and the VBA program for ternary distillation calculations with constant relative volatility fSection 5.2.1 are shown in Figure 5-Al and Table 5-Al. If you are not familiar with VBA look at Appendix B of Chapter 4. The problem solved is to determine the number of stages and the optimum feed stage for the distillation of 100 mol/h of a saturated liquid feed that is 30 mol% A, 20 mol% B, and 50 mol% C. L/D = 1, and the desired fractional recoveries are B in distillate = 0.99 and C in bottoms = 0.97. Component A = benzene, component B = toluene, and conponent C = cumene. The constant relative volatilities with respect to toluene as the reference are = 2.25, Qbb = 1.0, and a B . 21. By trial and error, the optimum feed stage was determined to be the second stage from the top (the total condenser is not counted as a stage). 

C2 will use the contents of cell B2. Cell C6 will use the contents of cell B6. In general, references to constants in column A will be absolute (with dollar signs). References to numbers in the remainder of a spreadsheet will usually be relative (without dollar signs). 

---