Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculated Fields and Pivots (Excel 2000)

    I am having trouble with calculated fields and If statements.

    I have a column in my data table that has a field with numbers from 0-100 as values. I would like to create a calculated field that will show the following:

    Count of records with values >0/Count of All records

    My knowledge of Excel pivot tables is limited to creating them from data sources. I just have not experimented with calculated fields in a pivot table.

    Personally I would just manipulate the data in Access, but my company is Access illiterate and prefers to do everything in Excel.

    Thanks for any assistance.

  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: Calculated Fields and Pivots (Excel 2000)

    If your numbers are in the range A1:A100 (change as appropriate):
    <pre>=COUNTIF(A1:A100,">0")/COUNT(A1:A100)</pre>


    Should calculate what you want (format as percentage if desired)

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated Fields and Pivots (Excel 2000)

    Thanks for your help, but I am trying to Insert Calculated Field for a pivot table and am still having difficulty. When I enter the formula:

    =COUNTIF(Z1:Z3530,">0")/COUNT(Z1:Z3530)

    nothing happens and it will not accept this. Since the data is on a different sheet, I assume I must change this to:

    =COUNTIF('Sheet1'Z1:Z3530,">0")/COUNT('Sheet1'Z1:Z3530)

    it also will not work. When I click OK, it sends me back and highlights Z1 or 'Sheet1'Z1

    I am new to calculated fields and still having difficulty. Thanks for any additional help.

  4. #4
    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: Calculated Fields and Pivots (Excel 2000)

    The formula does NOT require a pivot table (it is not a calculated field in the pivot) it just gives the total percent in A

    What are you trying to do in your pivot table? Could you give some example data and what you want to pivot table to calculate.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated Fields and Pivots (Excel 2000)

    I appreciate your time in respnding to my post. I figured out how to accomplish this by utilizing the formula and THEN utilizing the pivot table to obtain the information.

    Thanks for your help.

Posting Permissions

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