+ Reply to Thread
Results 1 to 5 of 5

Complex Formula Based on WBS Formula

  1. #1
    Registered User
    Join Date
    11-07-2016
    Location
    Denver, CO
    MS-Off Ver
    Microsoft Office Standard 2013
    Posts
    2

    Exclamation Complex Formula Based on WBS Formula

    I'm trying to find a formula that will help me to auto-calculate the Start Date, Due Date, and % Complete based on an existing formula for Work Breakdown Structure (WBS) in a Gantt Chart. I have a project plan/gantt chart that has three levels of detail in the WBS (1, 1.1, 1.1.1, etc.). Here's an example of what I'm trying to calculate: If WBS = 1, return the earliest Start Date of all Start Dates with WBS = 1.X; If WBS = 1.1, return the earliest Start Date of all Start Dates with WBS = 1.1.X. In other words, I only want to enter the Start Date in the lowest level (Task level) (e.g., 1.1.1) and have the higher levels (e.g., 1.1) auto-calculate. I'm hoping to figure out the logic/formula and apply it to Due Date and % Complete. In addition, I want to be able to add/delete rows without having to manually update the formulas. To explain, the formulas I'm using to calculate WBS auto-update when adding/removing rows; however, I found these formulas online when researching gantt chart templates, and am not sure how to edit them to be applicable to Start Date, Due Date, and % Complete.
    Attached Files Attached Files

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Complex Formula Based on WBS Formula

    Welcome to the board.

    B
    C
    F
    J
    K
    14
    WBS Detail Start Date Min Date
    15
    1 Category
    02/20/16
    J15: {=IFERROR(1 / (1 / MIN(IF((LEFT(B16:$B$30, LEN(B15)+1) = B15 & ".") * (F16:$F$30 <> 0), F16:$F$30))), "")}
    16
    1.1 Sub-Category
    03/14/17
    17
    1.1.1 Task
    03/14/17
    18
    1.1.2 Task
    04/01/17
    19
    1.2 Sub-Category
    02/20/16
    20
    1.2.1 Task
    02/20/16
    21
    1.2.2 Task
    03/14/17
    22
    1.3 Sub-Category
    02/27/17
    23
    1.3.1 Task
    02/27/17
    24
    1.3.2 Task
    03/14/17
    25
    1.4 Sub-Category
    03/15/17
    26
    1.4.1 Task
    03/15/17
    27
    1.5 Sub-Category
    01/01/17
    28
    1.5.1 Task
    01/01/17
    29
    1.5.2 Task
    02/01/17
    30
    1.5.3 Task
    03/01/17
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    11-07-2016
    Location
    Denver, CO
    MS-Off Ver
    Microsoft Office Standard 2013
    Posts
    2

    Wink Re: Complex Formula Based on WBS Formula

    This is great - thank you!

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Complex Formula Based on WBS Formula

    You're welcome.

  5. #5
    Registered User
    Join Date
    10-04-2012
    Location
    Vienna
    MS-Off Ver
    Excel 2003 + Excel 2010
    Posts
    2

    Re: Complex Formula Based on WBS Formula

    Hello, if you put it into an excel worksheet, you get the message, you are using too many arguments.

+ 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. Creating a user defined function based on a complex worksheet formula
    By Atul Maskara in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-24-2014, 09:36 AM
  2. Calculate Figure Based off 3 Values : Complex Formula?
    By Rimberaud in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-07-2014, 06:48 AM
  3. [SOLVED] Use an existing cell formula for creating a new more complex formula
    By dubcap01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-23-2014, 07:41 AM
  4. Complex Excel Formula Required for Obtaining Values based on Column & Row
    By adeel726 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-05-2013, 04:36 PM
  5. [SOLVED] Macro to populate cell based on the result of a complex formula
    By chris5058 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-18-2012, 05:51 AM
  6. Complex If Formula or alternative formula?
    By helpneedednow in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2012, 01:58 PM
  7. [SOLVED] I Need Help with complex formula ?
    By Dan Thompson in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-13-2005, 07:05 PM

Tags for this Thread

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