# STAT 3010 Lecture Notes - Lecture 11: Pivot Table, Group Selection

30 views3 pages

To complete Table 6.1, displayed above, I first had to determine how I wanted to divide the data

up. I knew from the instruction of question six that my data had to be broken up into low,

medium and high categories. I knew that the data’s minimum income was $743.30 and the

maximum income was $4556.40, so I decided to subtract my minimum from my maximum

which gave me $3813.30. I then divided $3813.30 by three because I needed the data to be

broken up into three data categories. $3813.30 divided by three gave me $1271.03. Therefore, I

rounded up to $1300. This means my low, medium and high categories will all be separated by

$1300. My IF statement gave me the data to fill in my INCOMECAT column. The equation used

for the data set for INCOMECAT reads as:

“=IF(E2<2043.3,"LOW",IF(E2<3343.3,"MEDIUM","HIGH"))”. E2 is the INCOME column

applied to the IF statement that will change as it corresponds with the current row to which the IF

statement is applying. Once I completed the IF statement for all 75 data points, and each row in

the INCOMECAT had a low, medium or high, I was then able to create a pivot table. For the

pivot table, I first highlighted all my data in the INCOMECAT column. Then on the top ribbon I

clicked insert and chose to add a pivot table in a new worksheet. I dragged INCOMECAT into

the rows and values boxes within the pivot table field to create my pivot table. Based on how I

broke up the boxes, the data stated that there were 20 data points in the low category, 46 data

Table 6.1: Frequency Table of

Income Category (n=75)

INCOMECAT

Frequency

LOW

20

MEDIUM

46

HIGH

9

75