Standard Deviation & Probability

[paddyk]

Hi,

I can calculate the probability of the mean occurring using the NORM.DIST function, ie 50% probability of being the mean, 28% probability of being the mean +1,etc.

What I need to get though is what mean value (mean +1, mean +1.1 etc) is for a given probability %. For example at 25%/50%/75%/100% probability the mean value is x.

Can anyone help?

Thanks in advance.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

x​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

25%​

[/td][td="bgcolor:#CCFFCC"]

-0.67449​

[/td][td]B2: =NORM.S.INV(A2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

50%​

[/td][td="bgcolor:#CCFFCC"]

0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

75%​

[/td][td="bgcolor:#CCFFCC"]

0.67449​

[/td][td][/td][/tr]
[/table]

 

[paddyk]

Thanks, is there any way I can apply that to a data set? So that using the mean and standard deviation I can say the mean at 25% probability?

My data set includes a number of days per record, I have calculated the probability of the day's being mean+1,mean-1,etc but what would be more useful for me would be to reverse that calculate the number of days for the data set at a set probability % (25,50,75, etc). So instead of getting a 41% probability of being mean-1, it would show at 25% probability the mean would be 4.2.

Hope that makes sense!

 

[shg]

I don't follow that. Please provide a clear example.

 

[paddyk]

What I have calculated is in rows 1-5, where for example, for code 3660 there's a 22% probability the number of days will be equal to or less than mean-1 (2.55 days) and a 78% probability the number of days will be equal to or less than mean+1 (4.55 days).

What would be more useful is if I could calculate the number of days at banded probabilities, rows 7-9. For example, for code 3660 at 25% probability the number of days will be x, at 75% probability the number of days will be y.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Code[/TD]
[TD="align: center"]Mean[/TD]
[TD="align: center"]Std Deviation[/TD]
[TD="align: center"]Probability Mean-2[/TD]
[TD="align: center"]Probability Mean-1[/TD]
[TD="align: center"]Probability Mean[/TD]
[TD="align: center"]Probability Mean+1[/TD]
[TD="align: center"]Probability Mean+2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]3660[/TD]
[TD="align: center"]3.55[/TD]
[TD="align: center"]1.3[/TD]
[TD="align: center"]6%[/TD]
[TD="align: center"]22%[/TD]
[TD="align: center"]50%[/TD]
[TD="align: center"]78%[/TD]
[TD="align: center"]94%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]455[/TD]
[TD="align: center"]8.8[/TD]
[TD="align: center"]11.36[/TD]
[TD="align: center"]43%[/TD]
[TD="align: center"]46%[/TD]
[TD="align: center"]50%[/TD]
[TD="align: center"]54%[/TD]
[TD="align: center"]57%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]3910[/TD]
[TD="align: center"]3.83[/TD]
[TD="align: center"]1.43[/TD]
[TD="align: center"]8%[/TD]
[TD="align: center"]24%[/TD]
[TD="align: center"]50%[/TD]
[TD="align: center"]76%[/TD]
[TD="align: center"]92%[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]3661[/TD]
[TD="align: center"]9.85[/TD]
[TD="align: center"]10.77[/TD]
[TD="align: center"]43%[/TD]
[TD="align: center"]46%[/TD]
[TD="align: center"]50%[/TD]
[TD="align: center"]54%[/TD]
[TD="align: center"]57%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Code[/TD]
[TD="align: center"]Mean[/TD]
[TD="align: center"]Std Deviation[/TD]
[TD="align: center"]Mean @ 25% Probability[/TD]
[TD="align: center"]Mean @ 50% Probability[/TD]
[TD="align: center"]Mean @ 75% Probability[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]3660[/TD]
[TD="align: center"]3.55[/TD]
[TD="align: center"]1.3[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]3.55[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]455[/TD]
[TD="align: center"]8.8[/TD]
[TD="align: center"]11.36[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]8.8[/TD]
[TD="align: center"]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

7​

[/td][td="bgcolor:#F3F3F3"]

Code​

[/td][td="bgcolor:#F3F3F3"]

Mean​

[/td][td="bgcolor:#F3F3F3"]

Std Deviation​

[/td][td="bgcolor:#F3F3F3"]

25%​

[/td][td="bgcolor:#F3F3F3"]

50%​

[/td][td="bgcolor:#F3F3F3"]

75%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

3660​

[/td][td]

3.55​

[/td][td]

1.30​

[/td][td="bgcolor:#CCFFCC"]

2.67​

[/td][td="bgcolor:#CCFFCC"]

3.55​

[/td][td="bgcolor:#CCFFCC"]

4.43​

[/td][td="bgcolor:#CCFFCC"]D8: =NORM.INV(D$7, $B8, $C8)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

455​

[/td][td]

8.80​

[/td][td]

11.36​

[/td][td="bgcolor:#CCFFCC"]

1.14​

[/td][td="bgcolor:#CCFFCC"]

8.80​

[/td][td="bgcolor:#CCFFCC"]

16.46​

[/td][td][/td][/tr]
[/table]

 

[paddyk]

Thanks a million! Worked perfectly.

 

[shg]

You're welcome.