CODEWITHSUNDEEP
excelpivot-table
Mark
Mark

Reputation: 1

How do i make a calculated column in pivot table?

I have created a pivot table from this original dataset. Original Dataset the pivot table: Pivot Table

how do i add a calculated column of "Crime not Cleared" which is Actual Incidents-(Cleared by Charge+ Cleared Otherwise) .

Any help will be greatly appreciated. I don't have much experience with nested columns in pivot table

Tried to watch YouTube and ChatGPT couldn't find a suitable result

Upvotes: 0

Views: 89

Answers (1)

Chrimle
Chrimle

Reputation: 612

Yes, you can!

In Google Sheets

When editing the Pivot-table, click the "Add value". (Where you currently have 'Actual Incidents', 'Cleared by Charge' and 'Cleared Otherwise'.

Pick the option at the top named 'Calculate field'.

There, enter the following formula:

='Actual Incidents' - ('Cleared by Charge'-'Cleared Otherwise')

This should create a new column with values based on the aforementioned formula.

Official Google Sheets documentation for 'Create & use pivot tables'

In Excel

Select the Pivot-table. In the top ribbon, go to 'PivotTable Analyze', then to the item 'Fields, Items & Sets'. When clicking this, select 'Calculated Field...'.

There, you will have a list of all available fields that you can pick from. You can also type the formula directly. Remember to surround columns containing spaces with single-quotes ('). Like so:

='Actual Incidents' - ('Cleared by Charge'-'Cleared Otherwise')

This should create a new column with values based on the aforementioned formula.

Official Excel documentation for 'Calculate fields in a PivotTable'

Upvotes: 0

Related Questions