Annualized Attrition....

[Davers]

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
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
15	2002	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
16	EOMHeadcount	85	85											85.0
17	CSCAttrition	3	1											2.00
18	MonthlyAttrition%	3.53%	1.18%											2.35%
19	AnnualizedMCCAttrition	42.35%	28.24%											2.35%
20	2001MonthlyAttrition
21	2002vs.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.

 

[maxflia10]

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]

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??

 

[Aladin Akyurek]

You want to see the value of D19 appear in O19... Is that what you're asking?

 

[Davers]

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]

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]

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]

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]

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]

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!