MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Brainteaser: formula to calculate annual rate of attrition...

Posted by rm on February 15, 2002 11:50 AM


Can anyone come up with a formula to calculate a company's monthly and annual accumulated turnover rate percentage (and help solve a difference of opinion between myself and my boss)? For example:

in January 2001, a company had 60 employees and
lost 0; in February, the company had 63 employees
and lost 2, etc...

What's the formula for monthly turnover%, then the accumulated annual percentage up to that month? I am using " =M5/N5 " to calculate that month's turnover%, (where M5 is terminated employees and N5 is the total number of employees). Then to calculate the annualized turnover rate% up to that month, I am using " =AVERAGE('array') " (where 'array' is the range of each month's turnover% up to that month--e.g. Jan-May). My boss says that this second formula is not correct and to use " =(M5/N5)*12 " for every month and also at to figure end of the year%(?!?)

I really think this is wrong, but please correct me if I am in the wrong...I appreciate any insight and help...thank you in advance...rm

Posted by John on February 15, 2002 12:19 PM

The first formula (for the monthly rate) appears correct. For the second part:
If you want Year to Date, just add the monthly percentages.
If you want the result annualized, use =AVERAGE(range)*12 {(range) is the M5/N5 cells}
If you have a month with no turnover, be sure to enter a zero, so the AVERAGE formula will work properly.
And don't argue too much with your boss, or you might be part of the "turnover".

Posted by rm on February 15, 2002 5:18 PM

...good call!

Hi John,

Thanks for the advice (the latter as well)! We really aren't arguing, though, just differing in opinion...(I do remember rules #1 and #2 if the boss is wrong!) ;P