Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table formula help | Excel Worksheet Functions | |||
Pivot table for formula | Excel Worksheet Functions | |||
Pivot Table Formula | Excel Discussion (Misc queries) | |||
PIVOT TABLE FORMULA | Excel Discussion (Misc queries) | |||
Pivot Table - if Formula | Excel Worksheet Functions |