+ Reply to Thread
Results 1 to 12 of 12

Excel Gantt - 2 Questions

  1. #1
    Registered User
    Join Date
    02-09-2017
    Location
    Portugal
    MS-Off Ver
    2010
    Posts
    10

    Excel Gantt - 2 Questions

    Hello guys.

    I'm creating a timeline of one big process and i need your help in my work until now.
    GANTT_TESTING.xlsx

    I have two big questions on my chart that i cannot solve until now:

    1. How can i put in my Gantt Chart the alarm column? Basically my idea is to put one big ball or someting else on that exactly time of the alarm of each task.

    2. Is there any way to put and highlight in the chart the precedence column of each task?


    Thanks in advance!

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

    Re: Excel Gantt - 2 Questions

    1. How can i put in my Gantt Chart the alarm column? Basically my idea is to put one big ball or someting else on that exactly time of the alarm of each task.
    It is not clear to me exactly what you want to do with the alarm times. My first thought is to add the alarm times series, change it to a scatter chart (so your chart becomes a stacked bar + XY scatter combination chart), then figure out what values to put for the Y values of this series to position the alarm times correctly. Basic tutorial for a bar+XY scatter combination chart: http://peltiertech.com/Excel/Charts/BarLineCombo.html

    2. Is there any way to put and highlight in the chart the precedence column of each task?
    What do you have in mind? You could add a data label (using Rob Bovey's XY chart labeler add-in from appspro.com) to a data series that will put the value from the precedence column on the chart.

    Or you could "conditional format" the chart series so that different precedences get different colors. This usually involves adding each "condition" to the chart as a separate series, which means that most of the work is done in the spreadsheet (not the chart). Tutorial here: http://peltiertech.com/conditional-f...-excel-charts/
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    02-09-2017
    Location
    Portugal
    MS-Off Ver
    2010
    Posts
    10

    Re: Excel Gantt - 2 Questions

    Let me try to explain better.
    See my new attachment plz:

    GANTT_TESTING.xlsx

    1. The initial alarm is the exact time that some task have to start e i need to put that in chart. In this new attach i drew one big red ball on TASK12 at exact alarm time: 01:00
    What i want it's to put this dynamic. If i alter the column E (Initial Alarm), the ball should appear on this new time.

    2. By precedences i mean that, for example, Task 3 should only start when Task 2 finishes.
    In this new attachment i drew an arrow. And here i want the same as i told before, put this dynamic. Is possible this in Excel?

    Many thanks!

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

    Re: Excel Gantt - 2 Questions

    As I indicated -- 1 should be as easy as creating an XY scatter series and adding it to the stacked bar/Gantt chart to create the combination chart. Is there a specific part of creating the combination chart that you are stuck on?

    2. Getting one task to wait until another task is completed should be "easily" accomplished by the choice of value in the "initial hour" column. If you want Task 3 to wait until task 2 finishes, then the initial hour column for task 3 might have =G3 (the final hour for task 2) in it. Fully automating this is about figuring out all of the necessary logic for this column to account for the many different types of values in the "precedence" column. You probably need to think through every possible value for precedence -- figure out what that value means for the initial hour column, then come up with a suitable formula for the initial hour column.

  5. #5
    Registered User
    Join Date
    02-09-2017
    Location
    Portugal
    MS-Off Ver
    2010
    Posts
    10

    Re: Excel Gantt - 2 Questions

    I need only help now on point 1.
    I create a Scatter Chart Type for the column Initial Alarm, but the balls don't appear on my chart

    Can help doing this please mate?

    Thank you!

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

    Re: Excel Gantt - 2 Questions

    How did you add them? Here's how I would tend to do this:

    1) Add a column for the Y values of the XY scatter chart series (I put 0.5 into M2. M3=M2+1. copy down).
    2) Chart Tools -> Design -> Select Data -> Add series -> Select the added column (M2:M49 in my example) as values.
    3) Select newly added series and change the chart type of the newly added series to XY scatter (in mine, Excel automatically adds the secondary vertical axis).
    3) Chart Tools -> Layout -> Axes -> Secondary Horizontal Axis -> Show default axis. Your XY scatter series should now be visible.
    4) Chart Tools -> Design -> Select Data -> Select scatter series -> Edit -> Select the Alarm times column (E2:E49) as the X values for this series.
    4a) Note that nothing may have changed -- due to the text "N/A" in E8. Clear E8, or change to =na() to return the #N/A error value rather than the text string.
    5) Format the secondary horizontal axis so that its max and min limits are the same as the primary horizontal axis.
    6) Format the secondary vertical axis so that it is fixed at 0 minimum and 48 maximum and check the "values in reverse order" check box.
    7) Format data series so that it has the marker appearance you want.

    That should be pretty close to what you want.

  7. #7
    Registered User
    Join Date
    02-09-2017
    Location
    Portugal
    MS-Off Ver
    2010
    Posts
    10

    Re: Excel Gantt - 2 Questions

    Thanks for the tips.
    After doing that, my chart:

    GANTT_TESTING.xlsx

    still not appear the balls at exactly time of alarm column (E)

    Why so?

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

    Re: Excel Gantt - 2 Questions

    In step 6 (formatting the secondary vertical axis), you left "values in reverse order" unchecked and left the minimum at Auto (Excel seems to default to -2). You need to check the values in reverse order (so it will count from 0 to 48 from top to bottom) and set the axis minimum to 0 instead of auto.

  9. #9
    Registered User
    Join Date
    02-09-2017
    Location
    Portugal
    MS-Off Ver
    2010
    Posts
    10

    Re: Excel Gantt - 2 Questions

    Thanks very much mate!

  10. #10
    Registered User
    Join Date
    02-09-2017
    Location
    Portugal
    MS-Off Ver
    2010
    Posts
    10

    Re: Excel Gantt - 2 Questions

    My friend, new problema..

    GANTT_TESTING_V2.xlsx

    Suppose that i have add new lines in table.
    Why my balls on Scatter Chart become misaligned?!

    I think it's because the value of 0.5 that we put on column Q (=> (1) Add a column for the Y values of the XY scatter chart series (I put 0.5 into M2. M3=M2+1. copy down))

    How can i know which here is the best value to put?

    Thanks in advance!

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

    Re: Excel Gantt - 2 Questions

    I notice that you hid the secondary horizontal axis and deleted the secondary vertical axis. After adding the secondary vertical axis back to the chart, I notice that it is still set to a minimum of 0 and a maximum of 48. With the additional lines, you need to extend the axis max to 53. Whenever you add (or remove) tasks from the table, you need to adjust the max value for the secondary vertical axis so that it is always the same as the number of tasks.

    How many tasks will this eventually need to show, or does it need to be easily varied? If the number of tasks will be mostly fixed, it might be easiest to occasionally go in and change the max value on the secondary vertical axis (needs to be set to the number of tasks). If this needs to vary frequently, it might be easier to set the axis min and max to set values (0 and 1 are my obvious first choices), then interpolate to get the Y value. I would have a small table out of the way (assuming axis limits are set to 0 and 1):

    known_x -- known_y
    1 -- =1/count of tasks/2
    count of tasks -- =1-1/count of tasks/2

    Then, where I am putting the y values for the alarm series (column Q), I use a TREND() function =TREND(known_y from above table,known_x from above table,task number).

  12. #12
    Registered User
    Join Date
    02-09-2017
    Location
    Portugal
    MS-Off Ver
    2010
    Posts
    10

    Re: Excel Gantt - 2 Questions

    Perfect mate, understood!


    In principle, the number of processes will be fixed.
    But today I was asked to add more processes, so I needed to change the table, adding in this way.

    Thank you!

+ 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. Gantt Chart in Excel
    By SBBmaster09 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-04-2013, 07:27 PM
  2. Replies: 5
    Last Post: 10-04-2012, 07:01 AM
  3. Need to have a gantt chart build automatically based on a series of questions
    By kaalbdm in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-30-2012, 05:55 PM
  4. Replies: 0
    Last Post: 07-13-2012, 06:40 PM
  5. Excel Gantt chart
    By systechguy36 in forum Excel General
    Replies: 1
    Last Post: 04-15-2012, 10:30 AM
  6. questions that aren't really excel questions
    By martindwilson in forum The Water Cooler
    Replies: 1
    Last Post: 03-11-2012, 08:21 PM
  7. Gantt Chart in Excel
    By dblcrona in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-21-2011, 09:44 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