Excel 1

PROBLEMS

1. In the hydraulic jump1, a liquid stream of depth D1 flowing at

velocity v1 suddenly increases its depth to D2. Figure 5.24 shows the

equation that governs this effect. What formula will you use in E5

that can be dragged across to H5?

2. *Refer to Figure 5.10 of Exercise 7. We saw that ¼MATCH(B4,

frame,0)+1 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 height range to match the client’s height entered in B3. With the

existing data in B3: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 A2:A11 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. *With the same numbers as in Problem 3 above, find the sum of the

squares of only the positive ones. Give a formula that will

accomplish this. Hint: try either

i. SUMPRODUCT or

ii. IF nested inside an SUMSQ as an array formula.

5. *With the same numbers as in Problem 3 above, find the average of

the squares of the positive values.

6. Construct a worksheet similar to that in Figure 5.25 to make a

simple molar mass calculator. Cell C10 uses a SUMPRODUCT

n FIGURE 5.24

1Carnahan et al., Applied Numerical Methods, Wiley, New York, 1969 (page 203).

99Exercise 11: Conditional Formatting