If statements in Pivot Table Calculated Fields
 

Hi,

Is it possible to use an "if" statement in a calculated field if that "if"
statement is referring to a result in another field?

Formula= if(field1="happy", 0, if(
field1="sad",1,if(field1="neutral",2,"field1 was not completed")))

Thanks,
Carmen

Answer: If statements in Pivot Table Calculated Fields
 

Hi Carmen,

Yes, it is definitely possible to use an "if" statement in a calculated field in a Pivot Table. In fact, it can be a very powerful way to manipulate and analyze your data.

Based on the formula you provided, it looks like you want to create a calculated field that checks the values in "field1" and returns a specific result based on those values. Here's how you can do it:

1. First, create a new Pivot Table or open an existing one that contains the "field1" data.
2. Next, click on any cell within the Pivot Table to activate the "PivotTable Fields" pane.
3. In the "PivotTable Fields" pane, locate the "Values" section and click on the dropdown arrow next to "Sum of [field1]" (or whatever the name of your field is).
4. Select "Add Calculated Field" from the dropdown menu.
5. In the "Insert Calculated Field" dialog box, give your new field a name (e.g. "Field1 Result").
6. In the "Formula" field, enter the "if" statement you provided in your question:
   
   
   Formula:

   ---

   =IF(field1="happy",0,IF(field1="sad",1,IF(field1="neutral",2,"field1 was not completed"))) 


   ---

7. Click "OK" to save your new calculated field.
8. Your new calculated field should now appear in the "Values" section of the "PivotTable Fields" pane. Drag it to the Values area of your Pivot Table to see the results.

That's it! Your Pivot Table should now display the results of your "if" statement based on the values in "field1". If you need to make any changes to the formula, simply repeat steps 2-7 to edit the calculated field.

A calculated field will evaluate all text as zero, and can't display
text fields as text in the data area. So, all the calculations would
show zero.

Perhaps you could calculate the field in your source table instead, then
add that field to the pivot table. Instead of "field1 was not completed"
as the final argument, you might want to use a number, so it could be
displayed in the data area.

Carmen wrote:
Hi,

Is it possible to use an "if" statement in a calculated field if that "if"
statement is referring to a result in another field?

Formula= if(field1="happy", 0, if(
field1="sad",1,if(field1="neutral",2,"field1 was not completed")))

Thanks,
Carmen


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html