inserting a formula into a Pivot Table

[Curious George]

Hello, I'm wanting to insert an existing formula into a pivot to help me with changing data... but how?

I'm classifying customers and it is based on the "Count" of products they have bought over the passed years.

How do I apply the cell references to the calculated pivot table fields? eg: H16 = 'FY10-11 - Count'


=
IF(AND(H16>0,F16>0,D16>0,B16>0),"A Star",
IF(AND(H16>0,F16="",D16="",B16=""),"New",
IF(AND(H16>0,(OR(F16>0,D16>0,B16>0))),"Returning",
IF(AND(H16="",(OR(F16>0,D16>0,B16>0))),"Lost",
""))))

 

[json]

this may need to be done by adding a column in your data. then the results of your formula will show in the pivot

any chance you could upload a sample?

 

[Somendra Misra]

@Curious George

Problem is your formula is returing text values, if you return numbers say 1 for excellent, 2 for good and so on, it might give you result, based on the condition the cell you want to refer in formula are say column field of pivot table.

Regards,

 

[Curious George]

Hi both, thanks for the response. Attached is a small file with 2 tabs... 1 = data, 2 = pivot. the reason I cann put the formula into the data tab is because I need to sum up to total deals for each client (unless there is a way?). The pivot table does a lovely job of summarising, but I want the "category"!

 

[Somendra Misra]

@Curious George

See the attached file Sheet1. Green Cells has formula solution. Just change Category formula as per your need.

Regards,

 

[Curious George]

wow Somendra Misra... you are definately an Excel Ninja! absolutely fantastic thanks!

 

[Somendra Misra]

@Curious George

Thanks for your kind words. Glad you like it. Welcome Back anytime.

Regards,