Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Toronto, ON, Cayman Brac, Canada
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 2002 Pivot Table Non-Zero Averages (2002 SP2)

    Is it possible to get "non-zero" averages in an Excel Pivot Table? The attached file will probably explain it better than I can! The desired result would be for the pivot table to return the average values of 2.5, 3 and 3.5 (by excluding and zero values from the calculation). This can be obtained using the "=AVERAGE(IF(Range<>0,Range,FALSE))" array formula as shown on the Data Tab, but ideally I'd like to be able to do this calculation within the pivot table.

    Any thoughts would be appreciated!
    Thanks,
    TJ

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel 2002 Pivot Table Non-Zero Averages (2002 SP2)

    I do not think directly with a pivot table. If you use text instead of a zero to indicate ignore (a space, a null string, or even the text "0" instead of the number zero, 0) it will automatically ignore them in calcs.

    Steve

Posting Permissions

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