Excel using pivot table count in another formula

1
votes

Anybody has an idea why i cant use pivot table count value that is a number for a calculated field in that same pivot table? Here is the picture of what i mean:

Count field in the pivot table counts how many of the certain Models are in a table and i would need to use the following formula: Count divided by Sum of IN in this pivot table.

Every time i try to use the value from "Count" column it returns the #VALUE.

1
excelexcel-formulapivotexcel-2010pivot-table

1 Answers

0
votes

It might be a formatting issue where excel is not understanding that one of the numbers is a number. There are a couple of ways to address the issue.

  1. Highlight the cells, Home>Number, then apply the number format. Home>Number
  2. Select the pivot table,
    1. in the pivot table fields box, select the dropdown arrow in the values section and select Value Field Settings Value Field Settings
    2. Then select Number Format and then choose the desired formatting (likely Number) Number Format
  3. Force it as a value in your formula.
    1. I assume your formula looks something like
      1. =D4/C4 or
      2. =GETPIVOTDATA("Sum of Sum",$A$3,"Top","Top","Name","Bottom ")/GETPIVOTDATA("Count of Count",$A$3,"Top","Top","Name","Bottom ")
    2. you can use the VALUE() function to create
      1. =VALUE(D4)/VALUE(C4) or
      2. =VALUE(GETPIVOTDATA("Sum of Sum",$A$3,"Top","Top","Name","Bottom "))/VALUE(GETPIVOTDATA("Count of Count",$A$3,"Top","Top","Name","Bottom "))
  4. Create a new 'Calculated Field' in your pivot table that does the function for you.
    1. Select the pivot table, then Analyze>Calculations>Fields Items & Sets>Calculated Field Analyze>Calculations>Fields Items & Sets>Calculated Field
    2. Give your field a name, then for your formula, enter something along the lines of Count/Sum of IN. Use the Fields box to make sure you get the Field names right Calculated Field Options

Hopefully one of these options will help you get what you need.