How do I calculate the probability of integers in a range with the following statistics table? Can I use the =prob function?
How do I calculate the probability of integers in a range with the following statistics table? Can I use the =prob function?
can you explain the data... which column is the number of occurrences of each choice? TOTAL or COUNT?
janmorris, the total column
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:
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.
put this in I2 and drag down:
then format the cells as percentage![]()
Please Login or Register to view this content.
[.... withdrawn; replaced by subsequent posting ....]
Last edited by curiouscat408; 11-26-2021 at 05:24 AM.
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
Last edited by curiouscat408; 11-26-2021 at 11:09 AM. Reason: freq distrib Solver
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?
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks