Annualized Attrition....

Davers

Well-known Member
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
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

maxflia10

Well-known Member
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
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??

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

Davers

Well-known Member

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

MrExcel MVP

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

Replies
1
Views
96
Replies
0
Views
617
Replies
10
Views
565
Replies
0
Views
288
Replies
1
Views
231

1,148,277
Messages
5,745,813
Members
423,980
Latest member
zimza

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?

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

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