Pivot Table Formula help

[Guest]

Do "if" statements work in a Pivot Table formula? If so can I nest them like
in "regular" excel? I have the following formula and I don't get any syntax
error, but it doesn't work. I need this behavior in the pivot report, not in
the underlying Range.


=IF('Time Type'="actual",May*184,IF('Employee or
Source'="external",May*160,May*120))

Thanks - Russ

 

[Guest]

To be clear, 'Time Type' and 'Employee or Source' are in the ROW part of the
pivot table. Maybe one can only use an if statement on data values in the
COLUMN part of the PT?

 

[Guest]

I should have said Maybe on can only use an if statement on data values in
the DATA part of the PT?

 

[Debra Dalgleish]

You can use an IF formula that checks the sums of fields in the data
area. Maybe you could add a calculation in the source data, to check the
TimeType and other fields. Then, add that calculated source field to
the pivot table.

 

[Guest]

Thanks Debra. I know I can accomplish this by modifying the base range.
It's a political thing that is such that I'm only allowed to change things in
the pivot report and must not disturb the base range. One more quick
question if I may.

Why is it that when I go to pivot table, table options, Grand Totals for
Columns works fine. However when I check Grand Totals for Rows, it does not
work? I've tried turning off Auto Format and that doesn't work either.

Thanks - and thanks for your wonderful web site. It is always the first
thing I check!

Russ

 

[Debra Dalgleish]

As a workaround, you could create calculated items, and separate the
Actuals from other time types in two pivot tables. There's a sample here:

http://www.contextures.com/excelfiles.html

Under PivotTables, look for 'PT0017 - Create Calculated Items and Fields'

To show a grand total for rows, you'd need a field in the columns area.
Maybe you have multiple data fields, but no column fields, and those
won't be summed.

And thanks for letting me know that you like the web site.