+ Reply to Thread
Results 1 to 10 of 10

Events in a time chart?

  1. #1
    Forum Contributor
    Join Date
    01-29-2019
    Location
    Drenthe, Netherlands
    MS-Off Ver
    Dutch Office 2019
    Posts
    291

    Events in a time chart?

    In row 1 i have the time in ours.
    In row 2 i haven text from a drop down. Lets say error 1, error 2, error 3, error 4, error 5, error 6.

    Is there an way with a offset function to display a chart how late which error happend. Something like a gantt?

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Events in a time chart?

    Short but useless answer -- yes, I'm sure what you want to do is possible. I'm not sure if an OFFSET() function will necessarily be a part of the solution.

    What do you have in mind? You mention a Gantt chart, how do you envision this in a Gantt chart? How do you usually make Gantt charts -- In cell charting with conditional formatting, or a stacked bar chart, or something else?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    01-29-2019
    Location
    Drenthe, Netherlands
    MS-Off Ver
    Dutch Office 2019
    Posts
    291

    Re: Events in a time chart?

    I have made a dynamic production chart with a offset function. When the production is below a certain value the employee have to fill in a reason in a drop down menu.

    Ik like to see in my dynamic production chart the reason why the production was to low at that point. I was thinking about a differed row en give the a cell a value with the if function.
    Error 1 = 1, Error 2 =2, Error 3 = 3, etc.
    Then i can make it visible in the chart. Not only is is quite some work. Also i have to figure out how to give the cart the text and not the number.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Events in a time chart?

    Can you upload a sample file? I am having trouble visualizing what you already have, so I cannot recommend the next steps to get what you want.

  5. #5
    Forum Contributor
    Join Date
    01-29-2019
    Location
    Drenthe, Netherlands
    MS-Off Ver
    Dutch Office 2019
    Posts
    291

    Re: Events in a time chart?

    Quote Originally Posted by MrShorty View Post
    Can you upload a sample file? I am having trouble visualizing what you already have, so I cannot recommend the next steps to get what you want.
    In only uploaded 2 sheet and deleted not relevant info. Zo i like the text in row 12, 15, in sheet PKB in chart 1 from de Prod. raport sheet, and row 20 en 23 from PKB in chart 2 for Prod. raport.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Events in a time chart?

    the Prod. raport sheet is password protected, so all I can do is see it -- I cannot explore what the charts are doing or make any changes to test various ideas.

    My expectation would be to use some variation of this technique: https://peltiertech.com/Excel/Charts/FormatMinMax.html In short:

    1) have a couple of helper rows with values that will position the special markers/lines where you want them.
    2) add these helper rows as 3rd and 4th data series to the chart.
    3) Display data labels for those additional data series and use the "values from cells" option to use the text from rows 12 and 15 (and rows 20 and 23) as the data label text.

    What questions do you have about implementing something like that?

  7. #7
    Forum Contributor
    Join Date
    01-29-2019
    Location
    Drenthe, Netherlands
    MS-Off Ver
    Dutch Office 2019
    Posts
    291

    Re: Events in a time chart?

    Quote Originally Posted by MrShorty View Post
    the Prod. raport sheet is password protected, so all I can do is see it -- I cannot explore what the charts are doing or make any changes to test various ideas.

    My expectation would be to use some variation of this technique: https://peltiertech.com/Excel/Charts/FormatMinMax.html In short:

    1) have a couple of helper rows with values that will position the special markers/lines where you want them.
    2) add these helper rows as 3rd and 4th data series to the chart.
    3) Display data labels for those additional data series and use the "values from cells" option to use the text from rows 12 and 15 (and rows 20 and 23) as the data label text.

    What questions do you have about implementing something like that?
    Not what i'm looking for. I upload the workbook without password. and some notes and a working dynamic chart
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    01-29-2019
    Location
    Drenthe, Netherlands
    MS-Off Ver
    Dutch Office 2019
    Posts
    291

    Re: Events in a time chart?

    someting like a gannt.

    Maybe give each error a value and a duration. Make with each value (error) a gannt en give the ax the names of the error?
    Last edited by Frigide; 02-16-2019 at 07:10 PM.

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Events in a time chart?

    i'm still not sure I understand. "Adding a Gantt chart" to what you have essentially means making your charts so they are line + stacked bar, though I think it might be easier to make them as scatter + stacked bar. Your file does not include any attempts at combining a stacked bar "gantt" chart with your line charts, so I'm not sure what you need help with. Making it this way will as you note, require calculating a starting time and duration for each error, and I don't see those calculations in the spreadsheet (though the sheet is kind of big, so I could have missed them). Are you familiar with the procedures for making a stacked bar + line or scatter combination chart? Are you able to calculate start time and duration for the errors? Broad overview of the steps I would expect:

    1) Compute start time and duration for errors.
    2) Add that data to line or scatter chart and change chart type to stacked bar.
    3) Other formatting and adjustments as needed.

    What part of this do you need hgelp with?

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Events in a time chart?

    Here is what I did to create a stacked column + scatter chart as my first attempt at this. Note that there is a lot of development still to go:

    1) I wanted to work with a smaller chunk of data, so I selected BP7:CN12 and copied into a new, blank tab.
    2) I noticed that your dates in row 7 seemed to have some entries out of order. I guessed that you intended these data to extend from 13 Feb to 15 Feb, so I corrected the Dec. 2018 and Jan 2019 dates to fit into the 13 Feb to 15 Feb dates. This, of course, would only be necessary if those dates are truly in error. If they really are back to Dec and Jan, then they can be left alone, though the line chart suggests that the dates/times should be in order.
    3) There are three errors given in row 12, so I entered these values into A16, A17, and A18.
    4) I manually entered the start times for each error type. =R7 for the 1 Shredder error, =Y7 for the 2 NIR error, and =W7 for the 3 Pre. Bunker error. These went into B16:B18.
    5) I calculated the durations for each error by subtracting the appropriate time stamps. C16 =V7-R7, C17 =1/24 (only one entry), and C18 =X7-W7.
    6) Using A16:C18, I created a stacked bar chart.
    7) I selected A11 and added it to the chart as a 3rd data series. Then changed the chart type of this data series to be XY Scatter. Excel automatically added the secondary vertical axis.
    8) Pulled up the Select Data Dialog and edited the 3rd data series so that the Y values were taken from A11:Y11, the X values were taken from A7:Y7.
    9) At this point, I have a stacked bar (Gantt) + scatter chart showing when the errors occurred relative to the data in rows 7 and 11. From here it should mostly be formatting the get the chart the way I want it.

    There is a bit of work yet to go in moving that into your larger spreadsheet and charts. I think a lot of the work is in the lookup and other formulas that will be needed to get the stacked bar (Gantt) data into the spreadsheet somewhere where the charts can reference it. But that should help you see the basics in how to build a stacked bar Gantt + XY scatter time line combination chart.
    Attached Files Attached Files
    Last edited by MrShorty; 02-17-2019 at 09:11 PM. Reason: add file

+ 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. Classifying time-stamped events into time intervals
    By juansalix in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-24-2019, 04:17 PM
  2. [SOLVED] Chart events/incidents by day of week and time
    By cobraspeed in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 05-02-2016, 03:43 PM
  3. [SOLVED] VBA strange behavior On time events firing at unexpected time intervals.
    By Kramxel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-04-2014, 05:58 AM
  4. Chart two events over time
    By darbid in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-10-2008, 07:35 AM
  5. Chart Events
    By RLang in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-09-2006, 11:19 AM
  6. [SOLVED] enabling chart events for an embedded chart
    By Wazooli in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-26-2006, 09:20 AM
  7. Turning off chart events
    By [email protected] in forum Excel General
    Replies: 0
    Last Post: 01-14-2005, 05:06 PM

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