Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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

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

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


Check out our Excel Resources

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

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".


...good call!

Posted by rm on February 15, 2002 5:18 PM
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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.