Calculated Fields/Items in Pivot Tables
Posted by Michael on November 05, 2001 3:07 PM
Hi. Can someone please tell me what I'm doing wrong? I have created a pivot table based on the following data for each day over a six-month period: Date, Station, Cost, Orders, Cost per Order, Grade. (Most dates have orders on multiple stations for that date. Cost per order is simply the cost column divided by the number of orders received from that station that day.)
I would like to display (in a separate row of data for each station) the cost for each date and station -- as well as a daily total for all stations -- BUT ONLY IF the Cost per Order for that station on that date was less than, say, $35. I have thus added a calculated field entitled "Payable" that has the following formula:
=IF('Cost per Order'<35,Cost,0)
This "Payable" field works well for showing the Cost total for each individual station and date when the Cost per Order is less than $35. My problem is that, at the bottom of the Pivot Table, the "Total Sum of Payable" row that the Pivot Table generates automatically is not properly calculating the sum of the values in the "Payable" field but, rather, appears to be applying the condition to the total.
Any suggestions? Also, is there any way to display the value in an additional Cost field (called, say, "Payable Cost if Grade A") only if: (1) the Cost per Order is less than $35 AND (2) the Grade field equals A?
Any help would be greatly appreciated!
Also, if it's easier to explain by telephone, I'd be happy to call you!
Michael
Posted by Mark W. on November 06, 2001 8:42 AM
Care to provide some sample data? (nt)
Michael, PivotTables always apply the summary
function to any field used in the calculated
item. So effectively, your calculated field
is evaluating =IF('Cost per Order'<35,SUM(Cost),0).
Since the Grand Total of Cost of not less
than 35 it will return 0. Grand Total always
operate on the data list contents not what's
displayed in the PivotTable. You'll need to
create a new field in your data list that
tests your condition and returns either Cost
or 0, and then use that new field in your
PivotTable.
Posted by Michael on November 06, 2001 9:16 AM
Re: Care to provide some sample data? (nt)
Thank you! Two quick follow-up questions: (1) what does "(nt)" mean in the title of your follow-up message? and (2) is it possible to create a field in a pivot table that sums/displays data from another field (e.g., Cost) only when two other fields (e.g., Cost per Order and Grade) meet specific criteria?
I'm so grateful that there are people out there like you who know this stuff and are willing to help out. (As a lawyer, if I can ever be of similar help with a legal question in California, I'd be happy to do that.)
Michael
Posted by Mark W. on November 06, 2001 10:03 AM
Re: Care to provide some sample data? (nt)
The "(nt)" is commonly used on this bulletin
board to mean "no new text" in the posted followup...
just what's typed in the subject line. I started
to ask for some data... then was distracted by
a co-worker... then decided I had enough info to
provide an answer and failed to change the subject
line. Sorry for the confusion.
Your 2nd question seems to be describing a
Calculated Item. You may want to consult the
Excel Help Index topic for "calculated items in
a PivotTable".
: Michael, PivotTables always apply the summary : function to any field used in the calculated : item. So effectively, your calculated field : is evaluating =IF('Cost per Order'<35,SUM(Cost),0). : Since the Grand Total of Cost of not less : than 35 it will return 0. Grand Total always : operate on the data list contents not what's : displayed in the PivotTable. You'll need to : create a new field in your data list that : tests your condition and returns either Cost : or 0, and then use that new field in your : PivotTable.
