This preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
Unformatted text preview: Decision Functions 89 *With the same numbers as above, find the average of the
squares of the positive values. Construct a worksheet similar to that in Figure 5.20 to make
a simple molar mass calculator. Cell C10 uses a
SUMPRODUCT formula. Each cell in row 7 uses two IF
formulas joined with the concatenation operator 61. The first
lF gets the symbol, and the next gets the number if it is
greater than 1. Then the row 7 cells are themselves
concatenated in A10. Hiding rows 3 and 7 would make the
worksheet more interesting! A] A a c j o E F 'G H l I J
LMO'eW'ar Masssialwlator, z : 3 l 2 i i 5 I i .1  3 At. Wt 12.011 1.003 15.999 14.007 30.974 32.055 35.453 79.904 125.904
4 Element C H O N P 5 Cl Br I 5 Number 2 6 1 7 Formula c2 H6_J I I s  I I .9_ Compound MolarMass I» I} ‘ V p ‘ I 10 c2H63 62.135 ; Figure 5.20 Refer back to Problem 6 in Chapter 4. This time we will solve
the problem without the helper columns. Construct a
worksheet similar to Figure 5.21. The cells 111 and 112 each
contain formulas that use SUMIF. Alternatively, you may wish
to use SUMPRODUCT in your formulas. AF A B c i o E F G H E l .l
1 lResistor Founcolor Code 2 3 ’ R Color Bari BarZ Bar3 Tolerance Ban:
,4 l 0 Black 5% Gold x 5 i 1 Brown 10% Silver 0 6 2 Red 20% None 0 7 3 Orange 8 4 Vellow x 9 5 Green x 10 6 Blue x 11 l 7 Violet I Resistance 560E+3
12 8 Grey Tolerance 5%
13 9 White Figure 5.21 Again we need to protect against having more than one X in a column. Use the same approach as in Chapter 4's problem
but with COUNTA rather than COUNT. Rev. Dawn is a recycler; she finds 49 candle stubs and makes
exactly seven new candles. These in turn yield seven stubs, 88 A Guide to Microsoft Excel 2007for Scientists and Engineers While COUNTIFS and SUMIFS in F7:F8 can replace the
SUMPRODUCT in G7:G8, there is no way SUMIFS can sum the cubes of numbers that pass the test. For a detailed discussion on SUMPRODUCT, see these websites: Bob Phillips at
http: / / www.xldynamic.com / source / de.SUMPRODUCT.html.
.E McGimpsey at
http: / / mcgimpsey.com / excel / formulae / doubleneg.htm1. probIems In the hydraulicjumpf a liquid stream of depth D1 ﬂowing at
velocity v], suddenly increases its depth to D2. Figure 5.19 shows the equation that governs this effect. What formula ‘ ' ' t ?
IB. Camahan a all, App/fed w1ll you use in E5 that can be copied 0 H5 Numerical MthOds, A A B C D E p G a H
New York, 1969 (page 203). “’i Hydrauliojump " i 2 ’ ‘
2 E : _ D1 a. D2 — 1+ ——— m if v1 > ng
i. 2 * 8’01
4 z 5, ‘2 e t, .
5 01 l g H [ v1 5 } 10 [ 15 20
6 10 32 l D; No Jump No Jump No jump 15.8 I
Figure 5.19 2. *Refer to Figure 5.9 of Exercise 7. We saw that
=MATCH(B4,fr'ame,O) tells us which column in the range
frame matches the frame type entered in B4. Write a formula
to find the row position in the range height to match the
client's height entered in B3. With the existing data in BB:B4,
our client’s height and type place him in row 7 and column 3
of the table F2:H16. Write a formula beginning =INDEX that
will locate the optimal weight within this. Finally, combine
the INDEX formula and the two MATCH formulas into one. 3. *The range A1:A10 in a worksheet contains both positive and
negative values, and you wish to sum only the positive ones.
Give a formula that will accomplish this. 4. *The range A1:A10 in a worksheet contains both positive and
negative values, and you wish find the sum of the squares of
only the positive ones. Give a formula that will accomplish this. Hint: try either of these: (i) SUMPRODUCT, or
(ii) IF nested inside a SUMSQ as an array formula. ...
View
Full Document
 Fall '08
 Mc,F

Click to edit the document details