Trying to make my pivot tables more useful by customizing my own formulas.

I would prefer to use the following formula within a pivot table but can't
seem to get the result I want. I'll add it to the source data if I have to
but suspect my lack of pivot table knowledge is the problem.

Assume Source data is
Description Title Period Amount
apples Miss 12 1000
pears Miss 12 500
geoff Mr 12 6000
able Mr 36 600

Formula I would add a column Yearly to the source data
=if(period=36,0,Amount)

and then take the total by Title

ie I would like a pivot table giving the outcome (yes I need the sum of
Amount so can't just filter by period)

Title Amount Yearly
Miss 1500 1500
Mr 6600 6000

I can't seem to insert an if statement in a calculate field and I need it to
evaluate each piece of source data separately which (per excel help) seems to
suggest I need a calculated item.

It just can't be this complicated!

Hi

If you drag title to the Row area
Drag Period to the Column area
Drag Amount to the data area you get what you want

12 36 Grand total
Miss 1500 1500
Mr 6000 600 6600

If you don't want the 36 amount to show, just Hide
the column.
You can rename the heading in the PT from 12 to Yearly
You can rename the heading Grand Total to Amount (provided you make a
space after Amount, so it is not the same as the field heading in the
source table)



Regards
Roger Govier

NomadPurple wrote:
Trying to make my pivot tables more useful by customizing my own formulas.

I would prefer to use the following formula within a pivot table but can't
seem to get the result I want. I'll add it to the source data if I have to
but suspect my lack of pivot table knowledge is the problem.

Assume Source data is
Description Title Period Amount
apples Miss 12 1000
pears Miss 12 500
geoff Mr 12 6000
able Mr 36 600

Formula I would add a column Yearly to the source data
=if(period=36,0,Amount)

and then take the total by Title

ie I would like a pivot table giving the outcome (yes I need the sum of
Amount so can't just filter by period)

Title Amount Yearly
Miss 1500 1500
Mr 6600 6000

I can't seem to insert an if statement in a calculate field and I need it to
evaluate each piece of source data separately which (per excel help) seems to
suggest I need a calculated item.

It just can't be this complicated!