Place “all other values” in a different column
You are currently viewing a sample of the Cram Kit. Click here to unlock everything.
Let's say that Coding for Crammers Academy had renovations done to improve the student experience, including a brand new pool, basketball court, zip-line, and mega quantum-computer (#nerdy). The renovations were completed right before the 2020 school year started, with the primary goal of making students happier, therefore increasing GPAs.
What if we wanted to create a PivotTable that assesses whether or not the renovations accomplished their purpose of improving the student experience (assessing whether or not GPAs increased)?
Essentially what we're asking here is... how did GPAs in 2020 compare to all other years?
To visualize this insight, we need to modify our table slightly by adding a column. This column will be called "Renovated?" and will have a value of "2020" or "Pre-2020" for each record, determining whether or not the student's information correlates to a year when the school was renovated or not.
Without creating a new column, this is the best PivotTable we could create to determine the average GPA of students during 2020 and all years before the renovations that happened before the 2020 school year.
This is decent, but not exactly what the question is asking for. The question wants us to find the average GPA of all years before the renovations happened. This is not doable with PivotTables and requires us to create a new column.
The formula for this column will be an IF function. To start, here is the formula for the IF function:
=IF(logical_test, [value_if_true], [value_if_false])
If the year is 2020 (meaning that the school was renovated)...
=IF(D2 = 2020, [value_if_true], [value_if_false])
...then the function should output "2020".
=IF(D2 = 2020, "2020", [value_if_false])
If the year is not 2020 (meaning that it's all other years), then the function should output "Pre-2020".
=IF(D2 = 2020, "2020", "Pre-2020")
Let's plug this into the first row of our table and copy it down through all cells.
Now, let's created a PivotTable including this new "Renovated?" column.
In this PivotTable, we'll place the Renovated? field in the "Rows" section and the GPA field in the "Values" section, selecting the Average calculation for GPA.
To use different calculations functions in the "Values" section, click the information button on the respective field!
Now we've successfully grouped all years before 2020 in the "Pre-2020" row! Too bad for Coding for Crammers Academy, it looks like the renovations didn't really work all that well... the GPAs actually decreased a little. At least students can now go hang by the pool in between classes now though!