+ Reply to Thread
Results 1 to 5 of 5

Add formula to pivot table or add data to pivot table to be shown in a graph

  1. #1
    Registered User
    Join Date
    02-19-2018
    Location
    Czech Republic
    MS-Off Ver
    Excel 2016
    Posts
    61

    Add formula to pivot table or add data to pivot table to be shown in a graph

    Hello everyone,

    I have a pivot table that shows these results according my selected filters:

    DEL 23
    KAL 10
    LAN 2
    ZR1 12
    ZR3 21


    The things is, that I would like to have the results of what contains "ZR3" to be divided by 3, so, in this case I would like to have as a result for ZR3 to be 7 instead of 21. I tried to add a formula using "calculated field" option, but seems to be that it doesnt work when you want to put a condition of text as requests my need (something like "=IF(Customer="ZR3";nš/3;nš)" doesnt work.

    I also tried to add the list in red with the desired results, but can not be added to the graph, because is blocked connected to the pivot table.

    So, is there a way to add a formula into the pivot table to divide always the results of "ZR3" by 3, or to add the list with the red numbers into the graph?

    PS: Note that the graph has to be always connected to the data of the pivot table, I can not make an independent graph just with the results of the list in red.
    Attached Files Attached Files

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Add formula to pivot table or add data to pivot table to be shown in a graph

    Hi,

    You need a calculated item, not a calculated field. You can then simply use the formula =ZR3/3. I have done this in the attached version and named it ZR3_
    Attached Files Attached Files
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    02-19-2018
    Location
    Czech Republic
    MS-Off Ver
    Excel 2016
    Posts
    61

    Re: Add formula to pivot table or add data to pivot table to be shown in a graph

    Thank you so much for your fast response! This is exactly what I wanted

    The thing now is... can you please describe how you did it? When I want to use the "calculated item" option I just see it in grey and I can not use it.... :/
    Last edited by thorblow; 02-19-2018 at 06:30 AM.

  4. #4
    Registered User
    Join Date
    02-19-2018
    Location
    Czech Republic
    MS-Off Ver
    Excel 2016
    Posts
    61

    Re: Add formula to pivot table or add data to pivot table to be shown in a graph

    OK, now I see, I just need to be in the cell that contains "ZR3" and then I can use calculated item! Thank you again for your support!

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Add formula to pivot table or add data to pivot table to be shown in a graph

    You must select an item cell in the pivot table first- for example G5. Then the menu item will be enabled.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 01-24-2017, 06:56 PM
  2. Text to been shown in Pivot table
    By Ms. Excel in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-27-2016, 09:42 AM
  3. Replies: 1
    Last Post: 07-29-2016, 04:30 PM
  4. Automating Pivot table and pivot chart creation if data table names unknown
    By Vegiepie2016 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-04-2016, 11:52 AM
  5. Pivot Table - formatting data shown
    By maitehar in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-23-2015, 12:02 PM
  6. [SOLVED] Pivot table - how to get two data fields shown in two columns
    By Seamus O'Connell in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-11-2006, 09:35 AM
  7. [SOLVED] why is the data row in pivot table incompletely shown?
    By buumoon in forum Excel General
    Replies: 1
    Last Post: 02-06-2006, 05:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1