Apple Intelligence now features Image Playground, Genmoji, Writing Tools enhancements, seamless support for ChatGPT, and visual intelligence.

Apple Intelligence has also begun language expansion with localized English support for Australia, Canada, Ireland, New Zealand, South Africa, and the U.K. Learn more >

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.

Shanthosh05 Author

Level 1

4 points

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

Top-ranking reply

Level 8

36,140 points

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

View in context

Similar questions

2 replies

Top-ranking reply

Level 8

36,140 points

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

Reply

Level 8

36,140 points

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

Reply

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.

Learn more Sign up