+ Reply to Thread
Results 1 to 1 of 1

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

  1. #1
    Registered User
    Join Date
    03-29-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    27

    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.

    Many thanks in advance
    Last edited by ddgacic; 06-26-2012 at 09:21 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1