Probability Function

[worldkarma]

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

[janmorris]

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

[worldkarma]

janmorris, the total column

[JeteMc]

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:

Please Login or Register  to view this content.

Let us know if you have any questions.

[janmorris]

put this in I2 and drag down:

Please Login or Register  to view this content.

then format the cells as percentage

[curiouscat408]

[.... withdrawn; replaced by subsequent posting ....]

[curiouscat408]

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

[worldkarma]

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?

[curiouscat408]

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!