hw

1

Analyze Data in Excel Convert words to numerical values

1. Select the column ‘Gender.’ Convert a text categorical variable

to a numerical variable. Click “Find & Select” – “Replace” in the

“Home” tab.

2. Replace “Female” with “1” (the person ‘is’ female) – Click

“Replace All.”

2

3. Replace “Male” with “0” (the person ‘is not’ female) – Click

“Replace All.”

You have a numeric categorical variable for ‘Gender’ – 1 =

Female and 0 = Male.

3

Calculate the “mean (average)”

1. Tab on the empty cell at the bottom of the column ‘Gender.’

Get the average of gender (1 = female) – the percentage of female

respondents.

2. Type “=AVERAGE(C1:C105)” to get the average value of cells

C1 ~ C105. Click the ‘Enter key.’ You get an average value of

‘0.625.’ 62.5% of the survey respondents are female.

4

Create pivot charts and tables

1. Click ‘Insert’ – ‘Pivot Chart’ – ‘Pivot Chart & Pivot Table.’

2. Excel selects the whole data range automatically. Make sure

not to tap on the column when you perform step 1. Click “Ok.”

5

3. Excel generates an application you can use to create pivot

tables and charts on a new tab. You can see the list of variables

on your right.

4. Select the input variable first, then the output variable. For

example, to get an average seat comfort rating (output) by flight

class (input), select “Class” and then select “Seat comfort.”

6

Initially, the output variables are the sum of values by default

setting. To change the output variables to the average value –

Click the output variable “Sum of Seat comfort” in the “Values”

tab on your bottom right. Click “Value Field Settings.”

Click on “Average” to change the setting from “Sum” to

“Average.” Click “Ok.”

7

You have the pivot table and chart of average seat comfort

ratings by flight class.

Business class customers give an average of 4.04 out of 5 for

seat comfort ratings. Economy class customers give an average

of 3.41. Economy plus customers give an average of 3.57.

Are business class seats more comfortable than economy class

seats?