Text Values in the Calculated Field of a Pivot Tbl more than 2 Conditions in Custom Format

[e4excel]

Text Values in the Calculated Field of a Pivot Tbl more than 2 Conditions in Custom Format

Dear Forum,

I am trying to have Text Values in the Calculated Field of a Pivot Table, however its not possible to do the same..
I have referred a website where its shown how to do the same but somehow I am able to only display only 1 Scenario at a time..

https://www.contextures.com/pivottabletextvalues.html

I have 4 Scenarios where I need to display four different text values.. as my entire report is managed in the Pivot table except this I was wanting this to also be incorporated in the Pivot Table.

=IF(AND(NPE>=0,NPE<3),1,IF(AND(NPE>=3,NPE<7),2,IF(AND(NPE>=7,NPE<10),3,4)))        where NPE is a Pivot Element...

where 1,2,3 and 4 are the below Text Values.

0 - 2 Times
3 - 6 Times
7 - 9 Times
10 -12 Times


Warm Regards
e4excel

[JeteMc]

Debra's method works as seen in the attached pivot table.
If you would like to see it applied to your pivot table please attach a workbook following the instructions in the banner at the top of the page that begins: e4excel Fast answers need...
Let us know if you have any questions.

[e4excel]

Dear JeteMc,

Sorry for the delay in creating a sample file but please find attached.. I have given the desired result column , I would want this to be shown in the Pivot Table.

[JeteMc]

Here is Debra's method applied to the Total column (M) of the pivot table.
Let us know if you have any questions.

[e4excel]

Thanks a lot JetMC, but I have absolutely no clues to how it worked and just one more thing is it possible to add more scenarios than 4 to display still more no of desired text...
Also, how do I not display the Grand Total Number for this NPE No of Times Column in the Pivot Calculated Field..

Please explain...in details as I am not able to figure out this at all..

I was aware of the Number Format Solution which gives 3 Options -Positive , Negative and for Zero but this is not that...

Also it possible to have this text as Originally Text and not just for display purpose ?

[e4excel]

Ok after checking the formatting in the Column M I found 4 Custom Number Formats with a ; General in the end..
Does that play a role in for the format ?

[JeteMc]

Yes, the custom number formats do play a role and in fact I made a mistake in assigning the custom number formatting. I should read [=4]"10-12 Times";; (corrected in the attached file)
I am not sure that I can explain any better than Debra does in the article linked in post #1.
Read the section that is headed: Manually Add Conditional Formatting
For this application we would change the instructions to read:

• In the formula box, type the formula : =M3=1
• Click the Format button, then click the Number tab
• In the Category list, click Custom
• In the Type box, enter this custom number format: [=1]"0-2 Times";;

I would suggest trying to add another scenario and, if unsuccessful, upload a copy of the file so that we can see where things are not working.
To not display a grand total > pivot table tools > design > grand totals > off for rows and columns
I do not understand what is meant by "Also it possible to have this text as Originally Text and not just for display purpose ?"
Let us know if you have any questions.

[e4excel]

Thanks once again, but I can see several Custom Formats for [1] till [4] so how does it work it differently for different values in the Pivot Table. Honestly, I did not understand how to apply this rule for more than 3 Scenarios..
Also now for this particular Pivot Table Column I needed to display these values but for other columns which I intend to insert with the same approach.. [1] "Less than 1 Million"; [2] "More than 2 Million" so on , how ill this work...

Please explain as to how do I apply this Custom Format differently for different values as all these values are in a bunch of 4 scenarios...

What I meant was that the original value of 1,2,3 and 4 is seen which is displayed as TEXT can this too be converted into text ? I dont expect this to be answered so badly but is that possible ?

[JeteMc]

"I can see several Custom Formats for [1] till [4] so how does it work it differently for different values in the Pivot Table."
There will need to be a different rule for each number, if that is what you are asking. So you may apply to more than 3 scenarios for a column, however you'll have to set up custom formatting for each one.
To use the approach for other columns you would need to set up conditional formatting/custom formatting rules per scenario for those columns. In fact if you are going to do a lot of this then it may be easier to add columns to the source data.
It may help if we could see a small desensitized sample of the source data along with a manually produced mock up what you want to see in the pivot table, like you did in columns A:F of the file attached to post #3. In that way we may be able to have a better understanding and provide more in the way of help.
Let us know if you have any questions.

[e4excel]

As of now, I am still trying to implement this for the single column M and unable to do so...If I set it for the first scenario [1] then next time [2] then the first does not remain so I have not figured out still how to apply it even after reading the link...again and again
Please humble request please explain step by step to set this up...

[JeteMc]

I did not see a mention of version restrictions in the article, so I assume the method should work for you.
Perhaps it would be more productive if you upload a sample showing your effort to implement the method in column M so that we can see what is going wrong.
Let us know if you have any questions.