Pivot Table Formula help
 

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

Pivot Table Formula help
 

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?

"xrbbaker" wrote:

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

Pivot Table Formula help
 

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

"xrbbaker" wrote:

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?

"xrbbaker" wrote:

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

Pivot Table Formula help
 

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.

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

"xrbbaker" wrote:


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?

"xrbbaker" wrote:


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



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Pivot Table Formula help
 

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" wrote:

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.

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

"xrbbaker" wrote:


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?

"xrbbaker" wrote:


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



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Pivot Table Formula help
 

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

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.

xrbbaker wrote:
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" wrote:


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.

xrbbaker wrote:

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

"xrbbaker" wrote:



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?

"xrbbaker" wrote:



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


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html