HFIT-565

Assessment & Evaluation of Health Fitness Parameters

Fall Semester 2008

Dr. Marc Schaeffer

mschaef@american.edu

Go to Course Syllabus

EXAMPLE PROBLEMS SOLUTION

A few students have brought it to my attention that this page refers to problem numbers in the first edition rather than the second edition that most of you are using. I apologize for this confusion and my error in not updating this important page. The references below to chapter 2 problems # 2, 8 are identical in the two editions. However, in chapter 3 the problem #13(first edition) is problem #15 (auditing reform) in the second edition. Again, sorry if this mis-identification has caused undue consternation.

If you try to glean much information from the following solutions without having read the book (through Chapter 4 as well as through Lecture #2), you will probably feel that this page is a total waste. In contrast, if you know how to go about creating a paper and pencil solution for these sample problems from having read the book and notes, you will definitely gain some insights about using Excel to produce solutions. Please remember, it is an extremely wise idea to draft a cartoon - that is, a sketch using paper and pencil of your solution before turning to Excel.

Words in blackened bold on this page represent words or phrases used to describe various components of Excel.

There are a number of details to note in the following frames. However, I highly suggest that you download the linked Excel workbook so that you can closely examine some specifics that are invisible by simply looking at the images below. Your download will have the title bar name ex1.XLS as seen below.

• The following image is a picture of an Excel window for a workbook entitled "ex1.XLS."
• There are four worksheets in this workbook represented by the four tabs (i.e., "Answer Sheet Ch 2 Probs Ch 3 Probs Add'l Probs") that you will see displayed at the bottom of the window image.
  • it will probably be to your advantage to use at least two worksheets on some assignments, but use common sense in how you divide your assignment into separate worksheets
  • you will probably find that you have between 3 and 9 worksheets in your default workbook settings which can be found under the tab for General on the "Tools/Options..."menu
  • it is quite simple to modify your default settings to have a desired number of worksheets in each new workbook
  • please do not submit workbooks with blank worksheets or you will be penalized
• The first worksheet has been named "Answer Sheet" - note this tab at the bottom is white.
• The second worksheet has been named "Ch 2 Probs" - note this tab is gray, as are the other two worksheet tabs.
• Just below, we are looking at the "Answer Sheet" page and we know this because the tab is white (rather than gray).

• after you have downloaded this workbook and while you have it open on your computer, click on some of the cells in the worksheet
• for example, click on cell A2 and note that you see in the formula bar, "Assignment #1 Chapter 2, ...."
• are you still trying to figure out what the formula bar is? Perhaps an Excel reference manual would help.
• now click on cell B2 and note that there is no content in the formula bar
• please click on several other cells to examine the relative similarities or differences between the cell and what is displayed in the formula bar
• please notice that the content of this answer sheet summarizes the most important aspects of the solutions for the sample problems
• however, also note that for Chapter 3, problem 13a, there is reference to a tab given in order to see the solution; because this solution requires several lines of data and a graph, it makes good sense to use the reference (see Tab Ch 3 Probs) rather than put a bulky graph on the answer sheet
• please spend a few moments studying the similarities and differences between the content of the answer sheet and the worksheet containing the detailed problem solutions

Back to top

• Next we click on the second worksheet tab (in your downloaded Excel document, "ex1.XLS," NOT the image below reflecting "Ch 2 Probs"
• Note in your Excel workbook at the bottom of the worksheet that the tab named "Ch 2 Probs" has turned white and the "Answer Sheet" tab has turned gray.
• this worksheet has been used simply as though it were a blank sheet of paper in that it contains only text - - that is, no calculations or formulae
• there is no difference in this answer, as shown on the worksheet relative to that displayed on the answer sheet

Back to top

• now move to the next tab entitled "Ch 3 Probs" as displayed below
• please notice how solution 13a contains an Excel graph using the data in rows 5 - 9 as the ordered pairs
• answers to 13b and 13c are only text, similar to the answers above for Ch 2 probs 2, 8

• the last two problem solutions are displayed on the last tab entitled Add'l Probs
• please pay close attention to the content of cell C24 in your downloaded Excel file
• notice that cell C24 does not contain a value, but a formula

• please determine the content of cell C59 and cell C60
• how do we get the percentage signs to display for the contents of these two cells?
• this Add'l Prob 2 is an example of a graphic plot

• it will be helpful if you can easily determine which variable is the IV and which is the DV
• almost without exception, the IV is our X variable and the DV is our Y variable
• on your downloaded Excel copy, click on the graph so that the eight handles appear and
• please note that by selecting the graph, the X and Y ordered pair data have been circumscribed - the X data are within a purple box and Y data are within a blue box
• whenever we click on one of our graphs, we must be able to locate our XY ordered pairs within the purple and blue boxes
• also please note that this graph has some important features that you must learn to include if you wish to obtain full credit on your plots
  • this graph has a title
  • this graph has an x-axis label and units of measure
  • this graph has a y-axis label and units of measure
  • this graph does not require a legend and correspondingly does not have one

Extremely important observations to make about this workbook

• the first page of the workbook is the answer sheet
• the problems are solved in the exact order listed
• there are not any problems solved other than than the ones listed
• there are only four tabs at the bottom; there are NO tabs for sheets other than these
• each of the tabs has been give a descriptive name; there are NOT ANY tabs named Sheet1, Sheet2, etc.
• the corresponding worksheets for each tab have content; i.e., there are NOT ANY blank worksheets

Another very important example (Avoiding page overflow)

• Let's say that we have a problem number 25 that has two parts, including a and b
• The data are as follows:

• For part a, the problem asks to plot the data for sleep as a function of age
• For part b, the problem asks to plot the data age as a function of sleep
• the first example below should be avoided
• the second example makes it much easier to read and follow
• the first exhibit below is an example of right margin overflow
• there are two dashed lines in this exhibit
• at the limit of column G is a vertical dashed line
• at the limit of row 55 is a horizontal dashed line
• these dashed lines represent the 8.5 x 11.0 inch margins of this page
• it is inconvenient to try to read the part of problem 25b that has been clipped
• further, notice in row 38 that there is some text streaming beyond the right margin
• assuming that this page were part of class assignment or paper you were expected to submit for a grade, you probably would not elect to have a page breaking the content at such an awkward point as we see below
• the same principle should apply to soft copy

• the following exhibit demonstrates one way to avoid margin overflow

• the dashed lines still reflect the 8.5 x 11.0 inch margins of the page
• however, no content integral to this solution overflows page bounds across either the vertical or horizontal margins
• by simply re-arranging the page content everything still fits on the page
• also note that the text beginning in line 53 does not stream off the page
• you must become facile enough to avoid page overflows or you will find your assignments being returned un-graded
• some problems may require more than a single page to solve, but using the same methods as displayed in this last example, you can extend your solution beyond the horizontal boundary at the bottom of the page - but remember not to have content straddle the horizontal boundary
• however, a common problem resulting from rearrangement is that students reduce the size of graph to be too small to read - try to avoid this.
• another advantage to complying with 8.5 x 11.0 inch margins is that printed versions of your assignments can actually be used without trying to trace where the overflow has been hidden (I highly recommend keeping a hard copy record of your assignments with your lecture notes)
• the only permissible circumstance for 8.5 x 11.0 inch overflow will be in the presentation of large tables incapable of fitting to a standard page
• you will probably observe that these dashed page boundary lines are NOT displayed by default; to display them
  • in Excel go to the File menu
  • select the choice Print Preview
  • if your Excel page is completely empty of content and depending on your version of Excel, you may get an alert box indicating: "Excel did not find anything to print." go ahead and click OK and the dashed lines should now appear.
  • alternately, make sure that your Excel page has at least one key entry of content before selecting Print Preview
  • You will need to manually display the boundary lines on each page of your workbook
• if you have had the slightest problems trying to follow Excel instructions on this page, you can probably benefit from using an Excel reference manual
• if in the past, you have gained knowledge using any computer software reference manual, you probably recognize that using reference manuals is not a guaranteed, simple process - it often takes some work, but the more experience you gain, the easier using a reference manual becomes

Back to top

 

 

 

 

Back to Lecture 1

 

Go to Course Syllabus

Go to Assignment Preparation Checklist page

Go to Assignment Submissions page

 

 

 

 

 

 

 

 

send email to Dr. Schaeffer

 

 

 

this page last modified M Schaeffer

on April 21, 2008