Probability formula needed

[PATSYS]

Hi everyone,

I need an efficient formula that I can copy to about 1000 rows.

The formula calculates the chances of reaching 60 years old based on the person's current age, using the following table (1st column is age, 2nd column is probability):


<30 20%
30-39 15%
40-44 10%
45-49 7.5%
50-54 5%
55-59 2.5%


So for example, the given age is 25, the chances of that person reaching 60 is 2.069% calculated as follows:

1 * (1-20%)^5 * (1-15%)^9 * (1-10%)^5 *(1-7.5%)^5 *(1-5%)^5 *(1-2.5%)^5

This formula must be applicable to any person's age.

Thanks

 

[Oaktree]

Shouldn't the ^9 be ^10 as there are 10 numbers between 30-39 (endpoints included)?

Set up A1:B6 to be this table:

0	20%
30	15%
40	10%
45	7.50%
50	5%
55	2.50%

Then, with calculation ages in column D, try this in E1 and copy down:

E1=1 * (1-$B$1)^MAX(0,$A$2-D1) * (1-$B$2)^MIN(MAX(0,$A$3-D1),$A$3-$A$2) * (1-10%)^MIN(MAX(0,$A$4-D1),$A$4-$A$3) *(1-7.5%)^MIN(MAX(0,$A$5-D1),$A$5-$A$4) *(1-5%)^MIN(MAX(0,$A$6-D1),$A$6-$A$5)*(1-2.5%)^MIN(MAX(0,60-D1),60-$A$6)