Pivot Table - Use Standard Formula

[Andy Meynell]

Hi all

I have added a calculated field to my pivot table:

.CalculatedFields.Add Name:="MyCalField", Formula:="=Col1/Col2"

I would really like to add more complexity to the formula to handle different types of errors. When typing the code I noticed that you had the option to use a standard formula:

Add(Name As String, Formula As String, [Use Standard Formula] As Pivot Field

I would like to use a standard formula but I'm unsure of the syntax

Any Ideas greatly appreciated

A Meynell

 

[Fazza]

Hi, Andy.

I'm unfamiliar with 'standard formulas'. Though I routinely do this sort of work in SQL. There is enormous functionality/flexibility available. If you search there will be many old posts where I've described this sort of thing.

A basic approach is to start by creating the pivot table in a new file. Take the external data choice at the first step of the PT wizard. (Also, simpler if you give the source data a defined name before you start. Not dynamic.) Just follow the wizard through. At the last step, take the option to edit in MS Query. Via the 'SQL' button you can edit the SQL.

HTH, Fazza

'change the SELECT line from something like
SELECT account, dept, year, cost
 
'to whatever you need
'with things like
SELECT LEFT(account,4) AS [Shortcode], dept, year, etc
 
SELECT Iif(cost < 1000,'little','big') as account, dept, etc
 
SELECT Iif(account IN ('ABC', 'ABD', 'ABE'),'one thing','other thing') AS [new field]
 
SELECT Iif(AND(cost < 100, year = 2008), 'this', "that') etc
 
SELECT Iif(account like 'A*', 'A_account', 'other') etc
 
SELECT account, cost/budget AS [ratio]
 
etc, etc
These are just rough ideas. Please be specific if you have a particular question.