Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Colchester, Essex, England
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table & Averages in Calculated Fields (Excel 2003)

    I have a datalist that contains trainee names and their evaluation markings. I need to extract metrics for the client that shows the trainer ratings for Overall ; Knowledge ; Management etc. Each rating is in its own column. At the moment when you use the Summarize by Average of Overall for example in a Pivot table in the data area, if I have 4 delegates and 3 give evaluation rating of 5, and 1 delegate doesnt give a rating, then the pivot table calculates it as 5+5+5+0 = 15 and then divides by 4 and I want the pivot table to ignore blank cells. I have tried using calculated fields but cant get it to work.... Any help greatfully appreciated !!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table & Averages in Calculated Fields (Excel 2003)

    The default behavior for aggregate functions (SUM, AVERAGE, STDEV) is to ignore text and blank values, so I'm surprised that you report the opposite. Could you attach a small sample workbook (with names changed to protect the innocent)?

  3. #3
    Lounger
    Join Date
    Jul 2002
    Location
    Colchester, Essex, England
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table & Averages in Calculated Fields (Excel 2003)

    Hi Hans... I agree...it should be by default. The data is being linked from another workbook. Would that make a difference ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table & Averages in Calculated Fields (Excel 2003)

    Depends on how it is being linked. Can you provide more info?

  5. #5
    Lounger
    Join Date
    Jul 2002
    Location
    Colchester, Essex, England
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table & Averages in Calculated Fields (Excel 2003)

    Just created exactly same Pivot table in the source workbook and data in Pivot table was correct... so it does seem as though there is something in the link that causes the data in the pivot to error.

  6. #6
    Lounger
    Join Date
    Jul 2002
    Location
    Colchester, Essex, England
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table & Averages in Calculated Fields (Excel 2003)

    I am using an Array formula

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table & Averages in Calculated Fields (Excel 2003)

    As you have probably noted, blank cells from the original become zero values in the target. Excel uses the zeros, not the blanks.

    There is no need to link the data, you can base the pivot table directly on a table in another workbook. Excel willl then use the original values and calculate the average correctly.

  8. #8
    Lounger
    Join Date
    Jul 2002
    Location
    Colchester, Essex, England
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table & Averages in Calculated Fields (Excel 2003)

    Thanks Hans...thats exactly what I am doing now.... just needed to thrash my frustration out...thanks for clearing my thoughts !!

Posting Permissions

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