Pivot table - grouping with formulas
 

I use a pivot table that is linked to data which I update weekly.
One of the fields I have is customer age.
This is the number of products a customer has bought from us in the
past.
If a customer has only bought one product in the past, we call them
sensitive.
If a customer has bought two products, he is semi-sensitive.
If they have bought 3 or more, we call them firm.
These 3 categories of customer ages we call Customer Sensitivity.

The customer age field in my data runs from 1 to 90.
I use the group function in the pivot table to group all sub ages from
3 to 90 and call them Firm.

This is where my problem begins.

If, on the following week, we now have a customer with a new age 91. I
now need to ungroup my Customer Sensitivity and regoup the firms to
include age 91.

Is it possible to use the formulas - calculated field or calculated
item - to enter something along the lines of...(if customer age2,
'Firm', (if customer age=2, 'Semi-sensitive','Sensitive'))

or something like that!
Many thanks in advance for any advice.

Pivot table - grouping with formulas
 

=IF([customer age]2,"Firm",IF([customer age]=2,"Semi-sensitive","Sensitive"))

Note the double quotation marks and note that you need two closing
parentheticals since you have a nested IF statement. [customer age] should
be replaced with the appropriate cell reference.

Dave
--
Brevity is the soul of wit.


"Owen" wrote:

I use a pivot table that is linked to data which I update weekly.
One of the fields I have is customer age.
This is the number of products a customer has bought from us in the
past.
If a customer has only bought one product in the past, we call them
sensitive.
If a customer has bought two products, he is semi-sensitive.
If they have bought 3 or more, we call them firm.
These 3 categories of customer ages we call Customer Sensitivity.

The customer age field in my data runs from 1 to 90.
I use the group function in the pivot table to group all sub ages from
3 to 90 and call them Firm.

This is where my problem begins.

If, on the following week, we now have a customer with a new age 91. I
now need to ungroup my Customer Sensitivity and regoup the firms to
include age 91.

Is it possible to use the formulas - calculated field or calculated
item - to enter something along the lines of...(if customer age2,
'Firm', (if customer age=2, 'Semi-sensitive','Sensitive'))

or something like that!
Many thanks in advance for any advice.

Pivot table - grouping with formulas
 

It occurs to me that, on re-reading your post, you're asking if you can do
the IF formula in the pivot table itself? I don't know that you can,
however, why can't you do the calculation in the source table you're using?

Dave
--
Brevity is the soul of wit.


"Owen" wrote:

I use a pivot table that is linked to data which I update weekly.
One of the fields I have is customer age.
This is the number of products a customer has bought from us in the
past.
If a customer has only bought one product in the past, we call them
sensitive.
If a customer has bought two products, he is semi-sensitive.
If they have bought 3 or more, we call them firm.
These 3 categories of customer ages we call Customer Sensitivity.

The customer age field in my data runs from 1 to 90.
I use the group function in the pivot table to group all sub ages from
3 to 90 and call them Firm.

This is where my problem begins.

If, on the following week, we now have a customer with a new age 91. I
now need to ungroup my Customer Sensitivity and regoup the firms to
include age 91.

Is it possible to use the formulas - calculated field or calculated
item - to enter something along the lines of...(if customer age2,
'Firm', (if customer age=2, 'Semi-sensitive','Sensitive'))

or something like that!
Many thanks in advance for any advice.

Pivot table - grouping with formulas
 

So is it not possible using the formulas function (beneath the group
function)?