pivottable calculated fields

[Guest]

Hello,

I'm having problems geting my calculated field to return the desired
information. I have a text field which has one of several names.

Text field
Name1
Name2
Name3

For each entry of name2 I want the perform an operation on the coorisponding
value in another number field. I've entered something like this:

=if('text field="name2",numberfield/2, numberfield)
I can only get a false response from this even when its true.

What am I missing? Are there limitations of what formulas I can make? I
write a similar function outside the pivottable and get the expected results.

Thanks, Al

 

[Guest]

Hi,

Thanks for your response. In the context of general excel functions and
calculations you are correct but I'm trying to generate a calculated field in
a pivot table. I can not make a reference to a specific cell.

I hope someone else can help me out there.

Thanks

 

[Debra Dalgleish]

In the calculated field, numeric value of a text field is zero, so no
item will be equal to "name2".

You could add a field to the source data, and do the calculation there.
Then, add that field to the pivot table.

 

[Guest]

Debra,

Thanks for the response. Yes one work around is to add another column of
data to the source data. There are two reasons that this is not ideal.
1. It makes the data file larger (i'm working with max size files (65k rows))
2. Some calculations that I want to do with the pivottable calculated
fields are very difficult in the source data. Part of the power of
Pivottables is the summarization of data and the calculated fields allows me
to operate on that data.

To try and understand what limitation might exist for the IF function in the
calculated field I changed the text field to a number field and entered 1 or
2 for each level of the text field. I then created the same if function
using this time the number field. The result is the same as when using the
text field. The comparision operator is simingly ignored.

There must be something fundamental here that I'm missing.

Any idea?