+ Reply to Thread
Results 1 to 9 of 9

Probability Function

  1. #1
    Registered User
    Join Date
    10-30-2021
    Location
    United States
    MS-Off Ver
    v16.54 (For Mac/Student)
    Posts
    13

    Probability Function

    How do I calculate the probability of integers in a range with the following statistics table? Can I use the =prob function?
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,142

    Re: Probability Function

    can you explain the data... which column is the number of occurrences of each choice? TOTAL or COUNT?

  3. #3
    Registered User
    Join Date
    10-30-2021
    Location
    United States
    MS-Off Ver
    v16.54 (For Mac/Student)
    Posts
    13

    Re: Probability Function

    janmorris, the total column

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2504
    Posts
    19,370

    Re: Probability Function

    I am probably missing something, however it seems to me that the probability of any of the integers in the Total column is 1/6 and that a formula to yield that could be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,142

    Re: Probability Function

    put this in I2 and drag down:

    Please Login or Register  to view this content.
    then format the cells as percentage

  6. #6
    Valued Forum Contributor
    Join Date
    07-13-2021
    Location
    California, USA
    MS-Off Ver
    2010
    Posts
    513

    Re: Probability Function

    [.... withdrawn; replaced by subsequent posting ....]
    Last edited by curiouscat408; 11-26-2021 at 05:24 AM.

  7. #7
    Valued Forum Contributor
    Join Date
    07-13-2021
    Location
    California, USA
    MS-Off Ver
    2010
    Posts
    513

    Re: Probability Function

    Quote Originally Posted by worldkarma View Post
    How do I calculate the probability of integers in a range with the following statistics table? Can I use the =prob function?
    Please post the text of the assignment verbatim, not your interpretation of it. Ideally, attach an image.

    No, we cannot use the Excel PROB function -- at least, not with just the data provided.

    The second function parameter requires a discrete probability distribution table -- the probability of each integer.

    And that seems to be the crux of your question (``how do I calculate the probability of integers in a range``).

    -----

    Your question, as written, does not make much sense to me, especially with the data that you attached.

    The following is my inference....

    Apparently, the mean in column B is calculated by total/count, rounded. Specifically:

    B2: =ROUND(G2/H2,1)
    C2: =ROUND(G3/H3,0)
    Copy C2 into C3:C7

    So, my interpretation of the table is: for each choice, there are 2850 numbers ("count"; column H) between "min" (column D) and "max" (column F) that sum to "total" (column G); and the median of the 2850 numbers is given in column E.

    In order to ``calculate the probability of integers`` for each(?) choice, we need to know how the numbers are distributed -- before and after the median, which might be different distributions.

    And yes, that is somewhat tautological.

    -----

    If the assignment is to derive the distribution, arguably there is insufficient information.

    We know that 50% of the occurrences of numbers between "min" and "max" are less than "median".

    Thus, for choice #1 for example, 1425 of the numbers are between 1 and 4; and 1425 of the numbers are between 5 and 27.

    We also know the "std dev" (column C). However, like column B, the values in column C might be rounded.

    Absent more information, perhaps the assignment is: for choice #1 for example, find a frequency distribution of 2850 numbers between 1 and 27 with a mean of 5.9 (actually 16919/2850), median of 5, and a std dev of 4.6.

    Alternatively: find a probability distribution such that: the sum of the probabilities for 1 to 4 is 50%; the sum for 5 to 27 is 50%; the weighted mean is 5.9 (16919/2850); and the weighted std dev is 4.6.

    I choose to use the second alternative with Solver. See the attached file and image.

    Formulas:
    C11: =SUM(B11:B37)
    C12: =SUMIFS(B11:B37,A11:A37,"<"&E2)
    C13: =SUMIFS(B11:B37,A11:A37,">="&E2)
    C14: =SUMPRODUCT(A11:A37,B11:B37)
    C15: =SQRT(SUMPRODUCT(B11:B37*(A11:A37-C14)^2))

    Solver set-up for PROB DISTRIB:
    Initialize B11:B37 to 0.1%
    Method: GRG Nonlinear
    Set Objective: C11
    To Value Of: 100%
    By Changing: B11:B37
    Constraints:
    B11:B37 >= 0.1%
    C12 = 50%
    C13 = 50%
    C14 = B2
    C15 = C2
    All Methods Constraint Precision: 0.000000000001
    GRG Nonlinear Convergence: 0.000000000001

    -----
    Update.... For posterity, I include the Solver set-up for the first alternative (frequency distribution).

    Formulas:
    D11: =H2-D12
    D12: =SUM(C11:C37)
    D13: =SUMIFS(C11:C37,A11:A37,"<"&E2)
    D14: =SUMIFS(C11:C37,A11:A37,">="&E2)
    D15: =SUMPRODUCT(A11:A37,C11:C37)/H2
    D16: =SQRT(SUMPRODUCT(C11:C37*(A11:A37-D15)^2)/H2)

    Solver set-up for FREQ DISTRIB:
    Initiallize C11:C37 to 3
    Method: GRG Nonlinear
    Set Objective: D11
    To Value Of: 0
    By Changing: C11:C37
    Constraints:
    C11:C37 >= 3
    D13 = H2/2
    D14 = H2/2
    D15 = B2
    D16 = C2
    All Methods Constraint Precision: 0.000000000001
    GRG Nonlinear Convergence: 0.000000000001
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by curiouscat408; 11-26-2021 at 11:09 AM. Reason: freq distrib Solver

  8. #8
    Registered User
    Join Date
    10-30-2021
    Location
    United States
    MS-Off Ver
    v16.54 (For Mac/Student)
    Posts
    13

    Re: Probability Function

    janmorris, curiouscat408, is there a way to find probability of choices if only 1 choice number is available (with a list of choices and data).

    for example, if I had a list of choices and selections, how do i make a chart that shows the probability of other choices' presence

    like If choice 1 is "2" the probability of choice 2 being "3 is 12%, 4 is 8%, 99 is 0 %" etc.

    is that a combo formula of =countif, =mode, and =prob?
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor
    Join Date
    07-13-2021
    Location
    California, USA
    MS-Off Ver
    2010
    Posts
    513

    Re: Probability Function

    Quote Originally Posted by worldkarma View Post
    is there a way to find probability of choices if only 1 choice number is available (with a list of choices and data).
    Given the new presentation of data, it seems quite easy to derive a probability distribution, based on one assumption or another (TBD), then determine the probability of a single number.

    But you have not addressed any of __my__ "questions" (requests for clarity). So, why should I address yours?

    Rhetorical. Deal me out!
    Last edited by curiouscat408; 11-28-2021 at 08:36 PM.

+ 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. Calculation by looking up the right month
    By iampain13 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-01-2020, 10:57 AM
  2. Excel Probability Function - How to check and apply probability
    By StormerJack in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-10-2019, 03:06 PM
  3. Replies: 1
    Last Post: 03-21-2012, 11:22 AM
  4. Probability Distribution VBA function
    By excelmaniac in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-02-2011, 06:32 PM
  5. a probability function
    By Xorin in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 07-16-2010, 08:38 AM
  6. probability function?
    By essexpoker in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-25-2008, 06:07 PM
  7. Probability function
    By Akhilesh Dalia in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-03-2006, 05:15 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