{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

bvl chapter 5 HW problems

# bvl chapter 5 HW problems - Decision Functions 89*With the...

This preview shows pages 1–2. Sign up to view the full content.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the 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

{[ snackBarMessage ]}