Annualized Attrition....

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
This might get interesting....I am trying to figure out annualized attrition. I think I have it working correctly, except for my totals column seen here:
BAC AttritReport.xls
BCDEFGHIJKLMNO
152002JanFebMarAprMayJunJulAugSepOctNovDecTotal
16EOMHeadcount858585.0
17CSCAttrition312.00
18MonthlyAttrition%3.53%1.18%          2.35%
19AnnualizedMCCAttrition42.35%28.24%          2.35%
202001MonthlyAttrition
212002vs.2001%3.53%1.18%          2.35%
CRC


The total annualized attrition rate, cell O19, should match what is in the latest entry for annualized attrition, in this example, cell D19. But I haven't figured out what the formula needs to be. Right now it is just figuring out average attrition so far for the year. I don't want to use a lookup formulae where you have to type in what month you are in...I would just like it to automatically know how many months are filled in and return the annualized attrition up to and including that month...Any idea's??? You guys have been great by the way answering all my newbie questions...I really appreciate it! I can't wait to have an answer to someone's EASY question....:)

Thanks,

Dave M.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890
Dave,

You may want to try this average formula

=AVERAGE(IF(C16:N16,C16:N16))

Which is array entered or this

=SUM(C16:N16)/MAX(1,COUNTIF(C16:N16,">0"))

which is entered the "normal way"

I think I got this from either Aladin or Mark W. I know this doesn't answer your question but I thought I'd throw this in anyway. It may not even help your average formula.
 

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
Thanks for the reply...it's not quite what I'm looking for though Max....I need it to return the latest value....in this case, February's Annualized Attrtion....

Any other idea's??
 

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165

ADVERTISEMENT

Exactly!!! However, it needs to automatically change when the next months data is put in...it then needs to return the value of March...I know that seems a little redundent...but, that's what they want to see...:-/
 

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890
OK Dave here's my shot

highlight the row and using Name/Insert/Define name it say num, in the source box enter =9.99999999999999+307 click OK. Now in O19 enter =lookup(num,C16:n16). You may have to adjust the cells or range. I have short term memory loss. Credit to Aladin for this.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

On 2002-11-07 15:34, maxflia10 wrote:
OK Dave here's my shot

highlight the row and using Name/Insert/Define name it say num, in the source box enter =9.99999999999999+307 click OK. Now in O19 enter =lookup(num,C16:n16). You may have to adjust the cells or range. I have short term memory loss. Credit to Aladin for this.

That's right. The recipe is...

First define BigNum (instead of just "num")...

(1.) Activate Insert|Name|Define,
(2.) Enter BigNum as name in the Names in Workbook box.
(3.) Enter the following in the refers to box:

9.99999999999999+307 [ without "=" ]

(4.) Click OK.

=LOOKUP(BigNum,C16:N16)

Dave: I'd like to add that you compute some things twice too often. You can avoid doing that a great deal in view of better performance.
 

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
Thanks guys...I can't seem to get it to work...I'm getting a #VALUE error...:-/

Alladin, what do you mean I'm computing some things twice to often....do you mean my ISERROR?

Thanks again everyone....

Dave M.
 

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
Thanks guys...I can't seem to get it to work...I'm getting a #VALUE error...:-/

Alladin, what do you mean I'm computing some things twice to often....do you mean my ISERROR?

Thanks again everyone....

Dave M.
 

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890
Dave,

I just replicated your worksheet and get the correct answer. Try and follow Aladin's direction again for the LOOKUP. And yes I think Aladin means the ISERROR plus the array entered formulas. Try using the SUMIF. I think Aladin's gone to bed! :biggrin:
 

Forum statistics

Threads
1,144,356
Messages
5,723,887
Members
422,524
Latest member
wirkkarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top