Thread: Calculated Fields and Pivots (Excel 2000)

1. 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. 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. 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. 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. 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.