Spreadsheet Assignment #4 - Calculating and Plotting Body Mass Index
[Back to Group 1 Assignments] [Back to Chem3020 home]
The "Body Mass Index" or "BMI" is defined as your body weight in pounds times 703, divided by your height in inches squared. In 1998, the federal government stated that a BMI of 25-27 is a good guideline. (Chemical and Engineering News, Sept. 7, 1998, page 68)
A- Calculate the BMI for three people with heights of 60", 66",
and 72" for body weights between 100 and 250 pounds, in 10-pound
increments. (Example titles and layout shown:) Create a chart that
plots all three lines, with weight on the x-axis and BMI on the y-axis.
|
|
Height ("): |
. |
. |
|
Wt:(lbs) |
60 |
66 |
72 |
|
100 |
19.5 |
... |
... |
|
110 |
... |
... |
... |
|
120 |
... |
... |
... |
|
130 |
... |
... |
... |
|
... |
... |
... |
... |
B- Rearrange the equation, such that for for any
given BMI value, the appropriate body weight may be calculated from a person's
height. Then, for BMI = 20, 25, 30, 35, calculate the appropriate weight
for people ranging in height from 50" to 80" in 1-inch
increments. (Example titles and layout shown.) Create a chart with four
lines, corresponding to the four BMI Index values (20, 25, 30 and 35), with
height on the x-axis and body weight on the y-axis.
|
|
BMI Values: |
. |
. |
. |
|
Height(") |
20 |
25 |
30 |
35 |
|
50 |
71 |
... |
... |
... |
|
51 |
... |
... |
... |
... |
|
52 |
... |
... |
... |
... |
|
53 |
... |
... |
... |
... |
|
... |
... |
... |
... |
... |
|
... |
... |
... |
... |
... |
|
80 |
... |
... |
... |
... |
|
Excel Programming Tips: |
|
Writing a formula for each cell would be very tedious and time consuming. When a cell containing a formula is dragged from the box in the bottom right corner, all the cells referenced in that formula shift in the corresponding direction and amount. Cells that are referenced in a formula can be locked. A reference can be locked by putting dollar sign ($) in front of its column and row references, such as: $B4, B$4, and $B$4. $B4 locks the Column, B$4 locks the Row, and $B$4 locks both. When writing formulas you can use the F4 Function key to toggle all 4 possible locking combinations (including none) for the referenced cell closest to the cursor. Note: It is possible to write only two equations for this entire
assignment by writing a single formula for each section using locked
reference cells, then copying it to the appropriate locations.. |