+ Reply to Thread
Results 1 to 7 of 7

Help using Bayes Theorem for Project Threats Analysis

  1. #1
    Registered User
    Join Date
    11-13-2015
    Location
    Toronto
    MS-Off Ver
    MAC 2016
    Posts
    6

    Help using Bayes Theorem for Project Threats Analysis

    Hello. I have not been oil this site for a long while. My career as a Business Analyst took me into the world of education and lectures. I am in the process of publishing a book with J. Ross Publishing. As part of its release I provide readers with an Excel sheet with a few handy tools:
    1. An advanced pre-project survey that identifies initial requirements management risks and identifies initial probabilities for success
    2. A requirements activities management table that accounts for effort, duration, productivity, and efficiency to determine probabilities of task completion
    3. A use case points analysis sheet

    I need help with the Bayes pre-project survey and creating a normal curve for the results of the activities management table. Would someone like to donate a few minutes of their time to help me with this urgent deliverable? My publisher is most anxious to receive the finial product so it does not hold up the book. In exchange I would be more than happy to promote this site and the person who helped me when I begin marketing.

    Can someone hep me?

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,709

    Re: Help using Bayes Theorem for Project Threats Analysis

    Someone can probably help you. but an example of what you have so far and what the end product would look like are more likely to gain you a response. At the moment it is a bit unclear. Presumably the sheet will only accomplish something you can currently do by other means, so you should be able to explain the process?

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    11-13-2015
    Location
    Toronto
    MS-Off Ver
    MAC 2016
    Posts
    6

    Re: Help using Bayes Theorem for Project Threats Analysis

    Thank you for responding to this thread. I have attached a workbook containing two sheets.

    Sheet "Threat_Analysis"
    It contains a table which, I hope, makes use of Bayes' Theorem. Would you please check my equations and let me know if it is correct?

    Sheet "Project_Survey"
    In this sheet, I am writing three tables for project managers and business analysts to assess the risk of a project before the planning begins. I want to evaluate the results in the following manner.

    IF the SUM of the EDF Score is BETWEEN 0 AND 24 return "Low Risk"
    IF the SUM of the EDF Score is BETWEEN 25 AND 49 return "Moderate Risk" ELSE return "High Risk"

    Sadly Excel does not have a BETWEEN (SQL is so much easier), so I have been trying to nest IF(AND()SUM(), but I can't seem to make it work. Would you also let me know where I messed up?

    =IF(AND(SUM(tblexternaldependencies[EDF Score])=>0,SUM(tblexternaldependencies[EDF Score])<=24),"Low Risk","Moderate Risk"),IF(AND(SUM(tblexternaldependencies[EDF Score])=>25,SUM(tblexternaldependencies[EDF Score])<=49),"Moderate Risk","High Risk")
    Attached Files Attached Files

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,460

    Re: Help using Bayes Theorem for Project Threats Analysis

    Maybe

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by TMS; 10-19-2019 at 08:58 PM.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,460

    Re: Help using Bayes Theorem for Project Threats Analysis

    Note the AND is redundant in the second part as you can just say <=49

    What if the value is negative?
    Last edited by TMS; 10-20-2019 at 04:43 AM. Reason: Fix typo

  6. #6
    Registered User
    Join Date
    10-18-2019
    Location
    Houston, Texas, USA
    MS-Off Ver
    365 ProPlus
    Posts
    1
    “Sadly Excel does not have a BETWEEN (SQL is so much easier), so I have been trying to nest IF(AND()SUM(), but I can't seem to make it work.”

    If you feel limited without the BETWEEN function, it would be fairly straightforward to write a User-Defined Function in Excel called BETWEEN that you can save in this file or on your computer to access from other Excel files.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,524

    Re: Help using Bayes Theorem for Project Threats Analysis

    You've already tested for the score being less than 25 in the first IF, so you don't need to test for it being greater than 25 in the second IF, because if Excel gets to that part then it must be >= 25.

    Hope this helps.

    Pete

    EDIT: I see that Trevor has picked that up in his second post.

+ 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. Continue With API Project - Data Analysis With Charts
    By Hillster in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-22-2018, 09:29 PM
  2. Avast identifiyng threats
    By jaslake in forum Suggestions for Improvement
    Replies: 2
    Last Post: 02-08-2016, 01:59 PM
  3. Is it Possible to use Bayes' theorem
    By pmcleod in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-13-2015, 11:24 PM
  4. Bayes Theorem with formulas and ranking
    By mloffredo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-01-2015, 09:18 PM
  5. Complex Data Analysis Project
    By CapstanJD in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-22-2014, 10:18 AM
  6. GUI School & Work Project for Analysis
    By Mordred in forum The Water Cooler
    Replies: 16
    Last Post: 04-13-2011, 04:50 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