# Pivot table, IF function, calculated item versus calculated field

Guest
Posts: n/a

 9th Mar 2010
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!

Roger Govier
Guest
Posts: n/a

 9th Mar 2010
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

> 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!

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Stijn Microsoft Excel Misc 1 25th Aug 2008 05:30 PM SG Microsoft Excel Worksheet Functions 2 27th May 2007 12:57 PM Fred Smith Microsoft Excel Misc 0 4th Mar 2007 09:15 PM =?Utf-8?B?VmlrcmFtIERoZW1hcmU=?= Microsoft Excel Programming 2 10th Oct 2006 08:45 AM =?Utf-8?B?Vmlja3k=?= Microsoft Excel Misc 3 6th Jun 2006 05:06 AM

Features