+ Reply to Thread
Results 1 to 6 of 6

Gantt charts

  1. #1
    Registered User
    Join Date
    01-26-2020
    Location
    Thailand
    MS-Off Ver
    MS Offiice for Mac 2016
    Posts
    3

    Cool Gantt charts

    Hi Guys, I'm a newbie so please speak slowly!

    I have a project which I have been creating 3 Gantt charts in an Excel work book for. The charts are quite simple ie. tasks verses planned time and also showing progress bars for each task.

    I am now putting together a Dashboard where I am copying across the charts and doing some eye catching formatting etc.

    What I would like to do is to add a percentage complete figure for each of the 3 Gantt charts (across each charts tasks percentage complete percentages) and also a Project percentage complete across the three Gantt charts? My maths is letting me down a bit as well as syntax and formulas for completing the formulas(s) even if I could do the maths but, the only way I can see of accomplishing this is by using the AVERAGE function not? I am not sure that this is the most accurate way nor, the best way that this can be accomplished?

    Any suggestions would be most gratefully welcomed?
    Last edited by AliGW; 01-26-2020 at 03:34 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Gantt charts

    Welcome to the forum.

    Instructions telling you how to attach your sample workbook are at the top of the page.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    01-26-2020
    Location
    Thailand
    MS-Off Ver
    MS Offiice for Mac 2016
    Posts
    3

    Re: Gantt charts

    Hi all!

    Excel workbook now uploaded as instructed!

    Thank you AliGW.
    Attached Files Attached Files

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Gantt charts

    The first thing to establish is how you want to define the average completion

    Take two tasks

    Task1 20 days 80% complete --->implies task duration 25 days
    Task2 50 days 40% complete --->implies task duration 125 days

    As simple average of 80% and 40% would give 60%

    However for the combined project of 150 days, the 70 days complete is only 46.7% complete

    This boils down to what you are trying to show.
    Take an extreme example where two tasks are: 1 day, 100% complete and another of 100 days only 1% complete. A simple average would give 50.5% complete whereas the overall average weighted by the 100 day task is 1.01% complete.

    Explain the definition you want and no doubt we can offer a solution. Usually with all these average tyoe of questions it's important to weight the underlying base data. The same point applies to averaging several projects as well as tasks within a single project.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Registered User
    Join Date
    01-26-2020
    Location
    Thailand
    MS-Off Ver
    MS Offiice for Mac 2016
    Posts
    3

    Re: Gantt charts

    Richard

    many thanks for your reply!

    I had to think about this some cos, as I stated on my Hello guys (intro post), I am not a great mathematician but, on digestion I think what you are saying is that:
    80% of 25 days ie 20 days +
    40% of 125 days is 50 days, 70 days in total.

    the combined average Percentage completed = 80 + 40/2 =60% got it!

    however:

    150 days (125 +25) with 70 days completed gives a percentage of 46.666666% completed!

    I can work through both scenarios, I see both but, I don't see how the mathematical error arise, sorry.... it must be in the averaging cos also, 150 days at 60% is equal to 90 days and not 70 YES... please help me here?

    I am thinking that the less averaging the the more correct the calls will be?


    Many thanks!

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Gantt charts

    There is no arithmetical error. The two examples are NOT measuring the same thing.

    Using the simple average does not reflect the difference in the length of the tasks. Let me ask a question.

    Does the length of the tasks reflect in any way the importance and significance of the task TO YOUR BUSINESS i.e. if a 2 day task is 50 % complete is that more, less or the same importance as a 100 day task that is 50% complete - and which may be behind schedule.

    These charts are using future dates not tasks that are completed by the current date. That raises the question whether the risks of each task not being completed by its plan date is of importance. If it is then arguably a new metric for a risk assessment factor should be built in. That could then be used for weighting the % complete that you show currently rather than the weighting being the length of the tasks.

+ 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. [SOLVED] Gantt Charts & Conditional Formatting
    By highguyuk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-10-2015, 04:27 PM
  2. [SOLVED] Gantt charts - help to tidy it up
    By nje in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-12-2015, 04:57 PM
  3. [SOLVED] Gantt charts for scheduling
    By Ram8516 in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 03-25-2013, 02:19 AM
  4. Fun with Gantt charts
    By Juvenal in forum Excel General
    Replies: 0
    Last Post: 04-03-2012, 07:53 PM
  5. help with autoshapes and gantt charts.
    By 0neup in forum Excel General
    Replies: 5
    Last Post: 09-22-2009, 05:08 AM
  6. Gantt charts
    By Drew Cutter in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-25-2006, 06:10 PM
  7. OVERLAY TWO CHARTS(GANTT)
    By Mike in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-01-2005, 01:05 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