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?
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?
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?
Originally Posted by shg
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.
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.
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?
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.
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?
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.
Last edited by MrShorty; 02-17-2019 at 09:11 PM. Reason: add file
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks