You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Calculated field in Pivot table - Numbers

I have a main sheet where all my account transactions are listed. There is a pivot table with the sum of all my spending and earnings. I want to create an expense ratio to understand the data better.


The data shown is not real.


After adding the expense ratio (with the formula Withdrawal amt/Deposit Amt), it should look like


Please guide me on adding a calculated field to a pivot table or the preferred ways to achieve the expense ratio.


Thanks in advance :)

Posted on Apr 3, 2023 8:45 AM

Reply
Question marked as Top-ranking reply

Posted on Apr 3, 2023 10:11 AM

If you really don't like having to do the copy-paste then you can also add extra columns in the data tables, something like this:






The formula in D2, filled down:


=MONTH(A2)


The formula in E2, filled down:


=SUMIF($D,$D2,C)÷SUMIF($D,$D2,B)÷COUNTIF($D,$D2)


The Pivot Table then looks something like this:




I find it easier to just copy-paste, though.


SG

2 replies
Question marked as Top-ranking reply

Apr 3, 2023 10:11 AM in response to Shanthosh05

If you really don't like having to do the copy-paste then you can also add extra columns in the data tables, something like this:






The formula in D2, filled down:


=MONTH(A2)


The formula in E2, filled down:


=SUMIF($D,$D2,C)÷SUMIF($D,$D2,B)÷COUNTIF($D,$D2)


The Pivot Table then looks something like this:




I find it easier to just copy-paste, though.


SG

Apr 3, 2023 9:44 AM in response to Shanthosh05

I don't see a way of doing calculated fields in a Numbers Pivot Table.


One thing you can do, of cours, is copy-paste the cells from the Pivot Table into a regular table and apply a formula there. Not fully automatic, but quite easy, especially after the Pivot Table has done the heavy lifting of calculating subtotals by month.


SG

Calculated field in Pivot table - Numbers

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.