In Advanced Excel PPT, work on the scenario manager as presented in slide 57. Then follow the instructions in the following slides to complete this exercise. 

QUESTION

1

1. Maria, a 66-year-old female, visits her physician complaining of sudden onset abdominal pain, nausea, and diarrhea. She does not take any prescription medications, but does take aspirin each day to lower her risk of heart disease. She has no notable history of digestive system issues. Maria has a family history of pancreatitis. She is slightly obese and has been mostly sedentary since her recent retirement. Her doctor performs multiple tests and diagnoses Maria with diverticulitis.
 
Diverticulitis affects which digestive organ?

Large intestine

Small intestine

Stomach

Liver

QUESTION 2

1. Briefly explain, in your own words, the anatomy of diverticula. Include information about the layers of the alimentary canal.

QUESTION 3

1. Diverticula may be asymptomatic for years or for a lifetime. Describe, in your own words, why diverticula may cause sudden pain.

QUESTION 4

1. In addition to the symptoms Maria reported, which of these symptoms is commonly related to diverticulitis?

Pain in the upper right quadrant

Pain in the upper left quadrant

Constipation

Vomiting

QUESTION 5

1. Which part of Maria’s history is NOT a risk factor for diverticulitis?

Aspirin regimen

Obesity

Sedentary lifestyle

Family history of pancreatitis

QUESTION 6

1. Maria’s doctor asks about her diet. Which of these diets is most closely related to diverticulosis/diverticulitis?

Low fiber diet

Low carbohydrate diet

High fat diet

Vegetarian diet

QUESTION 7

1. One of the tests Maria’s doctor orders is a fecal occult blood test. It was positive. Briefly describe why this test was positive in Maria’s case.
 

QUESTION 8

1. One of the diagnostic tests that Maria’s doctor ordered was a visual exam of the inside of the large intestine with a camera that is inserted through the anus and rectum. What is this procedure called?

Endoscopy
	Colonoscopy
Computerized tomography
Angiogram

QUESTION 9

1. If Maria’s case is severe, she may choose to undergo a colon resection. Which of these statements describes that procedure?

A portion of the small intestine is used to replace the affected portion of the large intestine.

The affected portion of the large intestine is removed and the remaining end of the large intestine are joined together.

Feces is redirected from the colon to a pouch on the outside of the body.

Diverticula are removed by lasers.

QUESTION 10

1. Briefly describe the difference between diverticulosis and diverticulitis. Include information about the prevalence of each condition.

QUESTION 11

1. Jim is a truck driver who is on the road for long hours. He admits to eating “unhealthy” foods and often stops at fast food restaurants after his late shifts, choosing cheeseburgers, French fries, and pizza most often. He does not drink alcohol because his job requires him to drive at a moment’s notice, but he does drink coffee throughout the day to keep awake during his odd hours. He has a history of intermittent heartburn, but the chest pain is getting more frequent and more severe, so he decides to seek treatment.
 
What is the medical name for heartburn?

QUESTION 12

1. Briefly describe, in your own words, why chest pain occurs in patients who have heartburn.

QUESTION 13

1. Which of the following anatomical structures is weakened, causing Jim’s heartburn?

Pyloric sphincter

Lower esophageal sphincter

Fundus of the stomach

Muscularis layer of the stomach

QUESTION 14

1. From what you know about Jim, do you think his recent chest pain could be related to more than just heartburn?

QUESTION 15

1. Jim’s doctor would like him to try OTC medication before prescribing something stronger. Which of these medications would likely help Jim? (Select all that apply.)

Antacids

H-2 receptor blockers

Proton pump inhibitors

Antihistamines

Diuretics

Aspirin

QUESTION 16

1. List and describe 3 risk factors that Jim has for GERD.

QUESTION 17

1. Jim is at risk for developing a complication known as Barrett’s esophagus. Briefly describe the pathophysiology of this condition. 

QUESTION 18

1. After two weeks, the OTC medication Jim tried isn’t helping, so his doctor wants to perform a diagnostic imaging procedure that uses a light and camera to look for inflammation inside the esophagus and test for Barrett’s esophagus. What is this procedure called?

Colonoscopy

Ambulatory acid probe test

Esophageal manometry

Upper endoscopy

QUESTION 19

1. Which of the following lifestyle changes is NOT typically recommended to patient’s with GERD?

Avoid eating several hours before sleeping

Avoid spicy and acidic foods

Weight loss

Monitor blood glucose levels

QUESTION 20

1. In addition to Jim’s risk factors, which of these conditions may increase the likelihood of a patient developing GERD?

Anorexia nervosa

Hiatal hernia

Laryngitis

Crohn’s disease

Applied Statistics

Advanced Excel

1

1

Advanced Excel
The purpose of this module is to either provide you with new Excel skills or to refresh your memory of these skills. In particular, we will focus on sorting, filtering, logic statements, pivot tables, scenario manager, and a few “tricks of the trade”– all of which can save you a huge amount of time and reduce the chance for human error. Pivot tables can significantly reduce the memory requirements for your file, also. You will need this material for many of the case studies.
Please download the file “Basic Data Set” from Canvas. This is data from a small hypothetical company. We will used this basic data set throughout the module. It is a very small file. Although most of the examples and exercises in this module using this data set could be answered by just counting people or some other simple brute force method, keep in the back of your mind that you may be analyzing Excel files of perhaps 80,000 rows and 25 columns. So these techniques can be applied to these huge Excel files. That is the main goal of this module – to help you save a huge amount of time and reduce the chance for human error when analyzing huge Excel files.
2

2

Advanced Excel
3
THE BASIC DATA SET
Here are the top rows of the basic data set. There are 26 rows and 9 columns of data in the complete file.

3

Advanced Excel
Place yourself in the position of a business analyst or some other kind of quantitative analyst for your organization. You are asked to produce a report with all nine columns which just shows the males for the organization. One way to do this is to sort all of the rows by gender (in this case, the field name is “Title” with males being “Mr.” and females being “Ms.”). There can be several ways of doing this, but here is a simple way.
Highlight the entire data set.
Click on Data/Sort. Here is the dialog box that should come up. Change the settings as appropriate.

4
SORTING

4

Advanced Excel

5
Apply or click on the following settings.
On the top right, click on “My data has headers.” This will ensure that the field/column names do not get sorted along with the data.
On the “Sort by” drop down, click the drop down arrow and select “Title.” This will separate all of the Mr. and Ms. Employees.

5

Advanced Excel

6
For the “Sort on” dropdown, click the arrow and select “Values”.
For the “Order” dropdown, click the arrow and select “A to Z.”
Click on “OK.”

6

Advanced Excel

7
Your spreadsheet should now look something like below. The important thing is to notice that all of the male employees are sorted to the top of the spreadsheet, although the order of their names might be different than below.
To produce the report, you can simply delete all of the female rows and what remains is the requirement, which can be printed out.

7

Advanced Excel
8
You may want to list the male employees in alphabetical order by last name.

Click on Data/Sort.
Keep the same settings as before, except click on “Add level” on the top left of the dialog box. The second sorting row will appear as below. In the “Then by” drop down, select “Name.”
In the second row, click on “Sort on” and choose “Values” and click on “Order” and choose “A to Z.”

8

Advanced Excel
9
Here is what the spreadsheet should look like after this second level of sort and with the females removed from the data set.
You are free to add many more levels of sorting as you see fit using this same procedure for each new sort level.
NOTE: ensure that all of the data is highlighted and headers are also highlighted before sorting. We want to make sure that all of the data stays corresponding with the names, etc.

9

Advanced Excel

10
Review: produce a report sorted by job series (from A to Z) and then sorted by salary level (largest to smallest), then sorted by last name (from A to Z). The answer is on the next page.

10

Advanced Excel

11

11

Advanced Excel
12
FILTERING
There is another way to produce a report showing only males in the organization. This is through a process called “filtering.” Sorting is sometimes the best way to support generation of reports, and filtering is sometimes the best way.
Here is a way to produce a report using filtering to show only the males in the organization.
1. Click on Data/Filter. The screen will change to look as below. Notice the new drop down arrows at the top of each column.

12

Advanced Excel

13
After the new drop down arrows are shown, click on the drop down arrow for “Title.” The drop down dialog box will come up, with (at the bottom of the dialog box) “Select All,” “Mr.,” and “Ms.” being displayed. To show only males, click off the “Select All” and “Ms.” so that the only check mark is by the “Mr.”
Although the names may be in a different order below than yours, the important point is that only “Mr.” are shown on the report.

13

Advanced Excel
14
Several layers of filtering are possible. For example, filter showing only the females from New York.
Click on Data/Filter to clear the current filtering.
Click on Data/Filter to start the new filtering.
From the “Title” drop down, click so that only “Ms.” is shown in the dialog box.
From the “Home State” drop down, click off all states other than New York.
The report is below, although the order of employees may be different than yours.

14

Advanced Excel
15
Review: filter the data set to show all employees from either New York or Virginia, who are female, and in the organization’s software development area.

15

Advanced Excel
16
LOGIC STATEMENTS
Below is a report showing all employees from New York. This was produced using filtering.
There is another way to generate a report showing all employees from New York, which can be very powerful in certain circumstances. It is by using a logic formula.

16

Advanced Excel
17
Here is the top part of the basic data set, sorted by “Name.” In cell J2, put in this formula.
=IF(G2=”New York”,1,””)
Read this formula as “if the cell value in G2 is “New York,” then place a “1” in cell J2, else place a blank in J2. We are looking here for certain character strings, which are always placed between quotation marks. “New York” is a character string. The “” at then end means “place nothing in the cell J2.”

17

Advanced Excel

18
Here is what the first row of the report looks like. Note that since employee is from New York, a “1” appears in cell J2.
Next, copy and paste the J2 formula down all of the cells in column J corresponding to the basic data set. The top of the spreadsheet now looks like this.

18

Advanced Excel

19
Now put a label in Column J’s header, and filter by “1” in column J. Here is the top of the report, although names may be in a different order.
“Trick of the trade.” Many people do a standard copy/paste or a drag procedure to copy the formula from J2 down to J27. That works. However, picture a situation where that formula needs to be copied down, say, 80,000 rows. That can take time and is open to human error. Instead, click on J2 and place the mouse cursor over the lower right cornet of J2. The cursor should become a “+” sign. While the “+” is showing, double left click the mouse. The formula will automatically copy down the entire column as long as the cell in column I has a value in it. See the next slide.

19

Advanced Excel

20
Notice that there is no “Rank” value in Ms. Golf’s cell. The automatic copying of cell J2 stops at Ms. Foxtrot’s row. Manually, the formula I J2 had to be recopied/pasted into Ms. Hotel’s cell in column J and then copied down the rest of the spreadsheet. So do the automat copying, go to the end of the spreadsheet to make sure that the copying was successful, and if not, then find the error and correct it. Here is the top of the report, although names may be in a different order.

20

Advanced Excel

21
Produce a report showing all company members who are from either Delaware or Virginia. Place a “1” in column J for all those who fit the criteria.
Here is the formula to use. Note that this is an “OR” statement. First click on the cell J2.
=IF(OR(G2=”Delaware”,G2 = “Virginia”), 1,””)
This formula is read as “if G2 = “Delaware” or if G2 = “Virginia”, then place a “1” into the cell J2, else place nothing into J2. Here is the top of the report, although names may be in a different order.

21

Advanced Excel

22
Produce a report showing all company members who are both from Virginia and have a Bachelor’s degree. Place a “1” in column J for all those who fit the criteria.
Here is the formula to use. Note that this is an “AND” statement. First click on the cell J2.
=IF(AND (G2=“Virginia”,F2 = “Bachelor’s”), 1,””)
This formula is read as “if G2 = “Virginia” and if F2 = “Bachelor’s”, then place a “1” into the cell J2, else place nothing into J2. Here is the top of the report, although names may be in a different order.

22

Advanced Excel

23
Produce a report showing all company members who are either both from Virginia and have a Bachelor’s degree, or have a High School degree Place a “1” in column J for all those who fit the criteria.
Here is the formula to use. Note that this is an “AND” statement. First click on the cell J2.
=IF(OR(AND(G2=”Virginia”, F2=”Bachelor’s”),(F2 = “High School”)),1,””)
This formula is read as “if G2 = “Virginia” and if F2 = “Bachelor’s”, or if F2 = “High School,” then place a “1” into the cell J2, else place nothing into J2. Here is the top of the report, although names may be in a different order.

23

Advanced Excel
24
REVIEW
Write the formula corresponding to –
If the value in cell A3 is greater than 5, then place “OK” in cell J2, else place “Error” onto J2.
Click on cell J2. Enter the following formula in it.
=if(A3>5,”OK”,”Error”)

24

Advanced Excel
25
Write the formula corresponding to –
If the value in cell A3 is either than greater than 5 or less than 0, then place “OK” in cell J2, else place “Error” onto J2.
Click on cell J2. Enter the following formula in it.
=IF(OR(A3>5, A3<0),”OK”, “Error”) 25 Advanced Excel 26 Review: write the formula corresponding to – If the value in cell E2 is greater than 50, and the person is male, then place “OK” in cell J2, else place “Error” onto J2. Click on cell J2. Enter the following formula in it. =IF(AND(E2>50,B2 = “Mr.”),”OK”, “Error”)

26

Advanced Excel
27
Write the formula corresponding to –
If the value in cell E2 is greater than 50 and the person is from New York, or if the person’s Rank is Analyst, then place “OK” in cell J2, else place “Error” into J2.
Click on cell J2. Enter the following formula in it.
=IF(OR(AND(E2>50,G2=”New York”),(I2 = “Analyst”)),”OK”,”Error”)

27

Advanced Excel Including Pivot Tables
28
ANCHORING
Please open the file of the Basic Data Set.
Suppose that we want to experiment with giving everyone in the company a percentage salary increase. One way to do this is to add two column headers – “New” Salary and Salary Increase. We want to see how the new salaries change with the proposed increases. Cell L2 shows an increase of 5%. Cell K2 is the “new” salary, a possible formula being =+L2*E2.

28

Advanced Excel Including Pivot Tables
29
There may be several ways to finish the calculations, but let’s try this one. COPY and PASTE the formula in K2 down the column to K27. Below shows the result.
What happened??? All new salaries below K2 are now $0. The formula for K2, copied down, says to multiple the value to the right of the K column cell by the corresponding cell in column E. Any cell below L2 basically carries a value of 0 for this example.

29

Advanced Excel Including Pivot Tables
30
What is really needed is to, instead of the K column formulas referring to the cell to their right in L, to refer only to the cell L2. This may make it easy in huge spreadsheets to change a value in only one cell which will affect one or more long columns.
Instead of using the formula =+L2*E2, change it to =+$L$2*E2. What this does, when copying and pasting the formula down column K, to refer not to the cell at the left of the K cell, but only to cell L2. The first part, $L, means “only refer to column L values.” The second part, $2, means “only refer to row 2 values.” Therefore, only the cell L2 will be referred to. This method has the advantage that only the value in cell Ls needs to change if one wants to experiment with different values.

30

Advanced Excel Including Pivot Tables
31
This procedure is called “anchoring” — in this case the cell L2 is anchored in the copying and pasting.
It is also possible to just anchor a row, such as =+L$2*E2, or just anchor a column, such as =+$L2*E2 – depending on the situation.
Anchoring can be extremely helpful in certain situations, so it is certainly recommended when possible.

31

Advanced Excel
32
PIVOT TABLES
There are times when it is necessary to produce reports from a large Excel file in the format of a table. For example, build a table from the basic data set with the rows being gender, and the columns being job series. The cells in the table represent the count of employees by gender in each job series. Such a table might be as below. From the table, we see that both the CEO and CFO are male, and the CIO is female.

32

Advanced Excel
33
Although building such a table for this company and its 26 people should be relatively easy, go back and imagine building a table from a data set of 80,000 rows and 25 columns. Such a task can be undaunting.
Excel has a feature which builds such a table in a user-friendly manner. This is called the “pivot table” feature. This feature can save huge amounts of time and significantly reduce the chance for human error.
Please return to the basic data set. Start by sketching how the final table will look. One such sketch is below. Note that the table has four sections: a blank, a gender, a Job Series, and the Counts.
Table rows
Table columns
Table cells

33

Advanced Excel
34
Now go into the Basic Data Set and build such a table semi-automatically.
Bring up the Basic Data Set.
Click on Insert from the ribbon.
Click anywhere inside the Basic Data Set.
Click on the upper left corner of the ribbon “PivotTable.”
A dialog box as below will appear.

34

Advanced Excel

35
Notice that “Select a table or range” is automatically entered. This is the entire data set.
Now tell Excel where you want the completed table to appear. I suggest for this exercise clicking on “Existing Worksheet” and cell K7.

35

Advanced Excel

36
This is what the dialog box looks like at this stage.
If your dialog box is the same as this one, click on “OK.” The Excel screen will change with the blank table on the right.

36

Advanced Excel
37
Note that the bottom part of this dialog box has four sections. These correspond to the four sections of the sketch from slide 29.
This is called the “field list” dialog box.

37

Advanced Excel
38
Reproduce the sketch here. For example, first go to “Title” on the top half. Left click on “Title” and drag it down to the “Row Labels” section. Your table should look as it is here.

Then left click on “Job Series” on the top half, and then drag it down to “Column Labels.” Finally. Left click on “Title” on the top half and drag it down to “Values.”

38

Advanced Excel

39
Here is what the dialog box should look like now.
If yours matches this, then on the Excel spreadsheet, slide over to K7 area. The pivot table has been built and all calculations are finished. See next slide.

39

Advanced Excel
40
This is what might be called a “raw table.” It needs some formatting to make it presentable, but all of the heavy lifting has been done.
Foe example, it might be easier to read if all of the columns are centered. Highlight the entire table, and click on the centering button on the ribbon as you would center any columns.

40

Advanced Excel

41
Notice that there are two filtering arrows. Click on the filter for Row Labels. This allows changing the table to show the results of the filtering. For example, suppose we wanted to show only females in the company. Filter by “Ms.” and the table will automatically change. On the column labels, filter by “Software Development.” The result is a count of all of the females in the Software Development area.

41

Advanced Excel
42
There are other format changes that can be made. Go back to the pivot table, go to each filter, and then include all items (“Select All”) for each filter.
Note that the column labels are in alphabetical order. Suppose the table needed to be completed with the CEO, CFO, and CIO occupying the first three columns. We need to move each of those one column to the left.

42

Advanced Excel

43
Go to the CEO cell and left click. Then right click. A drop down will appear. Find “Move.” Left click on “Move,” then left click on “Move CEO Left.” This is what the pivot table now looks like.

43

Advanced Excel

44
Go to the CFO cell and move it to the left. Then go to the CIO cell and move it to the left. This is what the pivot table now looks like.

44

Advanced Excel
45
Review. Build a pivot table, with centered columns, showing “Home State” in the rows and “Title” in the columns. Have the cells of the table show the count of employees by Home State.

45

Advanced Excel

46
So far the exercises have asked for “counts” of employees. It is also possible to do other mathematical calculations such as sums, (such as sums of money), averages, etc.
Recall the field list dialog box from slide 35. Go to “Count of Title” in the bottom right section and left click the down arrow. In the pop up box, look at the last entry “Value Field Settings.” Left click that, and you will see several math calculations which are possible.

46

Advanced Excel
47
Produce a report of all employees, by gender, and the total salaries paid to that gender (do not account for the fact that there are more females than males – just the total salaries).
After completing the field list dialog box, and clicking on the Value Field Settings and clicking on “Sum” the field list dialog box should look like this.

47

Advanced Excel
48
The pivot table should look like this.
If an abbreviated form of the table is wanted, giving just the grand totals, build the field list dialog box, but do not put anything in the “Column Labels” section.

48

Advanced Excel
49
Review. Build a pivot table having the Job Series in the rows, and total salary paid in the columns.

49

Advanced Excel

50
The last section of the table is the upper left section, Report Filter.”
Suppose the requirement included the need to filter by gender. Drag “Title” into the upper left section.
The pivot table report will change in format as on the next slide.

50

Advanced Excel
51
Notice the top line “Title” and “(All)” above the report. From the down arrow one can filter for gender.
Suppose we needed to filter by female. Click the down arrow from the top line and select “Ms.”
Notice that the values in the “Sum of Salary in $1000” have changed to reflect only female salaries.

51

Advanced Excel

52
Review. From the Basic Data Set, build a pivot table showing the employee degrees in the rows, and the average salary per degree in the columns, with the cells providing the total average per degree type. Arrange the report so that “Master’s” is in the first row, “Bachelor’s” is in the second row, and “High School” is in the third row.

52

Advanced Excel
53
Review. From the Basic Data Set, build a pivot table showing gender in the rows, “Rank” in the columns, and average salary in the cells. Put in a filter and filter by “Degree.” Show in the report only Master’s degree employees.

53

Advanced Excel
54
EXCEL’S “SCENARIO MANAGER”
The purpose of this set of slides is to introduce you to Excel’s Scenario Manager feature. You will be needing this material for several of the case studies, and it would be good to be familiar with it for work.

54

Advanced Excel
55
A company is forecasting sales and gross income. It is looking at the possibility of three monthly sales levels forecasts.
Optimistic: sales of 3500 items sold.
Realistic: sales of 3000 items sold.
Pessimistic: sales of 2500 items sold.
The company is also considering increasing its staffing by one person. The company is currently paying $12,000 per month in salaries, and adding another person will increase the salary cost to $15,000.
There are therefore six possible scenarios for which the company would like to calculate gross income:
Optimistic, $12,000 per month Optimistic, $15,000 per month
Realistic, $12,000 per month Realistic, $15,000 per month
Pessimistic, $12,000 per month Pessimistic, $15,000 per month

55

Advanced Excel
56
With Excel , one can produce the following table very quickly showing the results of the six scenario options.

56

Advanced Excel
57
Open Excel, and enter in the following table exactly as shown.

57

Advanced Excel
58
Now perform the following click sequence:
Data/What-If Analysis/Scenario Manager.
The Scenario Manager dialog box should appear and be basically blank.

58

Advanced Excel
59
Start to populate this dialog box. Start by clicking “Add.” This next box will appear. Populate the “Scenario name:” with the first scenario, “Optimistic, Current Employee Level” and in the “Changing Cells” block click on the cells in green on the spreadsheet, G6 and G14 (representing the optimistic option and current employee salary level). Click “OK.”

59

Advanced Excel
60
Enter in the value for the optimistic, which Excel will automatically calculate in G6, as 3500; this is from spreadsheet cell C6. Enter in the salary value for current employee level, which is in G14, as 12000.
Since there are five more scenarios to go, click on “Add.”

60

Advanced Excel
61
For the next scenario, the left figure below shows the dialog box as it first appears. Type in to the “Scenario name:” Optimistic, Hire New Employee” and then click on “OK.”

61

Advanced Excel
62
For the next scenario, type into the “Scenario name:” Optimistic, Hire New Employee” and then click on “OK.” When this dialog box appears, change the value for G6 to 3500, which is the optimistic sales value (see spreadsheet cell C7). Change the value for G14 to 15,000. Then again click on “Add” for the next scenario.

62

Advanced Excel
63
For the next scenario, the left figure below shows the dialog box as it first appears. Type in to the “Scenario name:” Realistic, Current Employee Level” and then click on “OK.”

63

Advanced Excel
64
When this dialog box appears, change the value for G6 to 3000, which is the realistic sales value (see spreadsheet cell C7), and put in 12,000 for G14 (current employee salary level). Then again click on “OK” for the next scenario

64

Advanced Excel
65
Continue on until each of the six scenarios is populated. After the last one is populated, then click on “OK.” The Scenario Manager dialog box should appear as this does (except for the location of the blue highlighted area). If so, then click on “Summary.” If not, “Edit” as necessary.

65

Advanced Excel
66
After clicking on “Summary,” ensure that the “Result cells:” block has H22 in it. This is the result cell of the calculations in the spreadsheet when each option is entered into the spreadsheet. Then click on “OK.”

66

dvanced Excel
67
Excel now builds the Scenario Summary table, part of which is shown below.
If you are going to use this as a presentation graphic, you may want to edit it. I put in the names of the cells in the “Changing Cells:” and “Result Cells” blocks, centered the columns, and decreased the width of the columns while using the Excel ribbon’s “Home/Wrap Text” feature. One editing of the final table is on the next slide, although there are many ways to edit should you so choose.

67

Advanced Excel
68
This table can now be copied and pasted into an Excel graphic or a PowerPoint graphic (or a Word file).

68

Advanced Excel
69
Ensure that you understand the review questions and can answer conceptually identical questions for the Quiz You will be using this material for many of the case studies.

69