# Calculated Field in Pivot Table Based on One Summed Field and one Counted Field

1. ## Calculated Field in Pivot Table Based on One Summed Field and one Counted Field

In Sheet 1 I have a data table with 4 columns and some 2000 rows. Column headings are as follow:
A1: Account Name
B1: Account Code
C1: Order Value
D1: Order Code
In Sheet 2, I created a pivot table to summarize values and number of orders per account. It looks like this:

Row 2 A2 (Account Name) B2 (Sum of Order Value) C2 (Count of Order Code)
Row 3 A3 (Account 1) B3 ( \$2,000,000) C3 (10)
Row 4 A4 (Account 2) B4 (\$3,000,000) C4 (15)
Row 5 A5 (Account 3) B5 (\$5,000,000) C5 (18)

Now I want to find out the average order value per order code (and therefore a fourth column) which should be organized as (Sum of Order Value/Count of Order Code).
One way (which I currently use) is to insert a formula in cell D2 like B3(\$2,000,000/C3 (10) and copy it down.
However I need to adjust this formula every time I refresh pivot table have a number of accounts being added or dropped from the list on a weekly basis (the above example is for illustration purposes only).

I've tried using formulas and adding a calculated field but because I'm looking to create this field on one summed field and one calcluated field I could not figure out how to do that.

To summarize, can a calculated field be arranged in pivot table using summed/calculated fields :
a) If “yes” how it can be done ( in addition to the problem outlined above I frequently face similar problems where summed or calculated fields need to be multiplied or divided);
b) If “no” is there any other way-around to at least solve the above problem.  Register To Reply