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