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!