+ Reply to Thread
Results 1 to 2 of 2

Pivot chart+table unsure how to layout and get to do what i need

  1. #1
    Registered User
    Join Date
    03-14-2019
    Location
    Hereford
    MS-Off Ver
    Home and Business 2016
    Posts
    30

    Pivot chart+table unsure how to layout and get to do what i need

    Hi , so with my spreadsheet the idea of it is that our machines have a set amount of hours we expect it to be ran unmanned (This is shown as the estimate time row highlighted red) we are then asking one of our workers to predict what he believes the time will be for the week (Jim prediction row) and i'm also wanting a section where he can put a comment under each machine as to why this time is what he thinks. The last thing that i need to be shown is the actual time that the machine ran unmanned. this is what would need to be in the table and i'm unsure on how to lay it out as i would need to have this with a week field aswell to have it be picked up on the graph.

    With the graph im wanting it to pick out the estimate time which is a set number that wont change and then pick up the actual time, with the graph we won't be showing the Jim prediction row but would like it to be shown in the table still, on sheet 3 you can see what i have done with the first table but that doesnt work for me as i dont want to be having to copy all the machine names each time and week number as if there are 3 week of data for "MULT1" i need that to be shown all under the one machine on the graph and not have 3 cases of "MULT1" showing, hope this makes sense.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Pivot chart+table unsure how to layout and get to do what i need

    Perhaps the following will help.
    1. I would suggest leaving the data on Sheet1 in record form as it is (IMO) easier to work with. You could automate the process somewhat by using assignments for Machine and Estimate (i.e. cell C15 is populated using: =C3) You could use the following to populate the Week column: =COUNTIFS(C$3:C3,C3). I would suggest converting the data range into an Excel table as that makes it easier to update the Pivot Table.
    2. On the Pivot Table field list put Week before Machine in the Row field.
    3. If you are going to add Jim Prediction field to PT you can hide that information on the chart (I changed the series type to line and then chose series option No Line)
    4. You can select which weeks to show using the Week field button at the bottom left of the chart.
    5. I changed the series type of the Estimated times to a line chart emphasize the difference between the estimated and actual times.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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: 5
    Last Post: 03-08-2018, 08:14 AM
  2. Unsure if a Pivot Table is what I need
    By sh4d0w1ink in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-05-2016, 07:58 PM
  3. [SOLVED] Pivot table-remove expand and close from pivot layout
    By excelmr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-01-2015, 07:00 PM
  4. Pivot Table Layout
    By frustrated in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-06-2015, 02:40 PM
  5. Excel 2007 : Unsure of correct table/chart
    By Ahauntingmelody in forum Excel General
    Replies: 0
    Last Post: 08-22-2011, 06:15 PM
  6. pivot table layout
    By appleknocker in forum Excel General
    Replies: 3
    Last Post: 05-18-2006, 09:15 AM
  7. [SOLVED] Pivot table layout
    By Paul P in forum Excel General
    Replies: 2
    Last Post: 04-25-2006, 11:45 AM

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