Excel 1

PROBLEMS

1. *Using the information from Problem 5 in Chapter 4, make a chart

similar to that shown in Figure 7.33. To ensure that the shape with

text is part of the chart and will move with the chart, select the chart

before using Insert / Illustrations / Shapes.

2. Make XY plots to (i) show that sin(x)�cosh(x)+1¼0 has roots at

0 and 1.3 (see Figure 7.34) and (ii) find the approximate roots of exp

(�(x�2)2 cos(πx))¼4 cos(x�2). In each case, make a plot with two

data series.

3. *The volume V of liquid in a hollow horizontal cylinder of radius

r and length L is given by the equation below where h is the depth of

the water. Using named cells and values r¼6 m, L¼5 m, make a

plot of V against h:

V¼ r2cos�1 r�h

r

� � � r�hð Þ

ffiffiffiffiffiffiffiffiffiffiffiffiffiffiffiffi 2rh�h2

p� � L

Can you find a way to avoid problems with the chart if you now give

the radius a value of 3 m?

4. It can be shown that in a body of mass m falling under the influence

of gravity g and subject to a drag, which is proportional to the square

Height (inches)

Trough gauge

V o

lu m

e (g

a ll o

n s )

0 0

50

100

150

200

250

300

3

Half-full point

6 9 12 15 18

n FIGURE 7.33

148 CHAPTER 7 Charts

of the body’s velocity (v), the velocity varies with time (t) according

to the equation v tð Þ¼ ffiffiffiffiffiffi gm

cd

r tanh

ffiffiffiffiffiffiffi gcd m

r t

� � where cd is the drag

coefficient. Make a worksheet to enable you to chart the velocity

from 0 to 25 s for a body starting from rest. The worksheet should

allow the user to vary both m and cd. Show that for m¼75 kg

and cd¼0.25 kg/m, the body reaches a terminal velocity of

approximately 54 m/s. Estimate the time to reach a velocity

that is within 5% of the terminal velocity.

5. Make an XY plot with two data series to demonstrate that x3�x

+2¼0 can have only one real root and that this is approximately

x¼�1.5.

6. Make the line chart as shown in Figure 7.35. The technique used in

Exercise 5will not workwith a line chart. The simplest way is tomake a

column with the same number of points as in the main data series for

each line to be added. Other methods include using secondary axes or

using error bars. To explore these, do an Internet search using peltier excel "line chart" horizontal line to find Jon Peltier’s advice on this topic.

7. Acme Tank Inc. will supply a customer with a storage tank in one of

two ways: (i) the tank is constructed in their factory and transported

to the site, or (ii) the materials are transported and the tank is

constructed in situ. The formulas (albeit, rather complex formulas)

for each method are

20 0

0.5

1

1.5

2

−2 −0.5

−1

−1.5

−2

−4 4

n FIGURE 7.34

149URLs for Chart Websites