I need to calculate the probability that someone will retire at a certain age
based on 4 years of historical data, but I am not sure how to do this in
Excel. I would also like to be able to factor in years of service and/or
department. Is there a formula I can use to do this calculation or do I need
something more complex?

You can use a pivot table. Say your raw data is two columns, the name of the
retiree and the age at retirement. For example:

age
60
59
58
56
60
55
59
60
57
56
55

Generate a pivot table showing Count of age by age. Will look like this:

Count of age
age Total
55 2
56 2
57 1
58 1
59 2
60 3
Grand Total 11

The probability of retiring on or before the age of 58 is:

(2+2+1+1)/11
or
0.545454545

--
Gary''s Student - gsnu200771


"Daisy" wrote:

I need to calculate the probability that someone will retire at a certain age
based on 4 years of historical data, but I am not sure how to do this in
Excel. I would also like to be able to factor in years of service and/or
department. Is there a formula I can use to do this calculation or do I need
something more complex?

Hi Gary's Student,

Thank you for the simple solution. I was expecting and fearing something
much more complicated. I think this will work!

"Gary''s Student" wrote:

You can use a pivot table. Say your raw data is two columns, the name of the
retiree and the age at retirement. For example:

age
60
59
58
56
60
55
59
60
57
56
55

Generate a pivot table showing Count of age by age. Will look like this:

Count of age
age Total
55 2
56 2
57 1
58 1
59 2
60 3
Grand Total 11

The probability of retiring on or before the age of 58 is:

(2+2+1+1)/11
or
0.545454545

--
Gary''s Student - gsnu200771


"Daisy" wrote:

I need to calculate the probability that someone will retire at a certain age
based on 4 years of historical data, but I am not sure how to do this in
Excel. I would also like to be able to factor in years of service and/or
department. Is there a formula I can use to do this calculation or do I need
something more complex?

And if Pivot Tables bother you (no reason they should) you can get the same
results with FREQUENCY
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Daisy" wrote in message
...
Hi Gary's Student,

Thank you for the simple solution. I was expecting and fearing something
much more complicated. I think this will work!

"Gary''s Student" wrote:

You can use a pivot table. Say your raw data is two columns, the name of
the
retiree and the age at retirement. For example:

age
60
59
58
56
60
55
59
60
57
56
55

Generate a pivot table showing Count of age by age. Will look like this:

Count of age
age Total
55 2
56 2
57 1
58 1
59 2
60 3
Grand Total 11

The probability of retiring on or before the age of 58 is:

(2+2+1+1)/11
or
0.545454545

--
Gary''s Student - gsnu200771


"Daisy" wrote:

I need to calculate the probability that someone will retire at a
certain age
based on 4 years of historical data, but I am not sure how to do this
in
Excel. I would also like to be able to factor in years of service
and/or
department. Is there a formula I can use to do this calculation or do I
need
something more complex?