Calculating Probabilities
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? |
Calculating Probabilities
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? |
Calculating Probabilities
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? |
Calculating Probabilities
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? |
All times are GMT +1. The time now is 09:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com