AAoA Spreadsheet Help

[Asilomar]

I'm creating a spreadsheet to keep track of my AAoA and I ran into formula trouble.  Can any of you spreadsheet gurus help me out, please?

I'm missing two things.  

1)  Like I have in column I, I want to express the value for AAoA in English.

2)  I need to find a way to divide the averaged sum in H3 by 12.  I've been trying to do like =DIVIDE(H3/12) but that's obviously incorrect.  

Here's a screencap of my Experian table without the formula showing (I'm going to hide the cells and rows that contain formula when I'm done):

With formula:  

[Asilomar]

I found this 5 year old thread.  Is this still true?  That FICO rounds down to the nearest whole year?  And that new accounts, since nothing can be less than 1 year old, are valued at 12 months but are not valued at 2 years until they are 24 months?  If so, then everyone's AAoA would be represented as whole numbers, and that's certainly not the case.  

Confused.  

[iv]

---

@Asilomar wrote:

1)  Like I have in column I, I want to express the value for AAoA in English.

---

Just directly use:

=CONCATENATE(DATEDIF(AVERAGE(C7:C16),NOW(),"Y")," y ",DATEDIF(AVERAGE(C7:C16),NOW(),"YM")," m ",DATEDIF(AVERAGE(C7:C16),NOW(),"MD")," d")

No intermediate cells/formulas required.

---

@Asilomar wrote:

2)  I need to find a way to divide the averaged sum in H3 by 12.  I've been trying to do like =DIVIDE(H3/12) but that's obviously incorrect.   

---

That's incorrect because "total months" isn't a useful number, here. You need the AVERAGE number of months, but you just have a straight SUM().  If you wanted AAoA in months, use:

=DATEDIF(AVERAGE(C7:C16),NOW(),"M")

Also no intermediate cells/formulas required, although if you wanted to continue displaying the account age per-account, you could use:

=CONCATENATE(DATEDIF(C7,NOW(),"Y")," y ",DATEDIF(C7,NOW(),"YM")," m ",DATEDIF(C7,NOW(),"MD")," d")

(And repeat down as needed for C8, C9, etc...)

Columns D through H are not really needed.

[iv]

---

@Asilomar wrote:

I found this 5 year old thread.  Is this still true?  That FICO rounds down to the nearest whole year?  And that new accounts, since nothing can be less than 1 year old, are valued at 12 months but are not valued at 2 years until they are 24 months?  If so, then everyone's AAoA would be represented as whole numbers, and that's certainly not the case.  

Confused. 

---

Don't confuse what FICO models use for AAoA breakpoints with the actual age calculations.

The actual AAoA is just a straight average - no year rounding, no minimum values.  (Although you will see four different ways to calculate AAoA displayed by major credit monitoring services - all four combos of with/without AUs x with/without closed accounts.)

FICO always includes closed accounts.

Older FICO models always include AUs.

Newer (8/9) FICO models sometimes exclude AUs (this will vary per person...)

FICO may not include accounts currently under dispute.

FICO breakpoints for AAoA exist at various points - the thread you referred to was just implying that there's no scoring difference between 1 year, and anything less than 1 year.  There are definitely multiple scoring breakpoints between 1 year AAoA and 8 years AAoA - how many there are, and exactly where all of them are is a matter of some debate.  (Some people have suggested that various half-year breakpoints do exist, and that there might be breakpoints above 8 years.)

[Asilomar]

Thank you, iv!  I really appreciate you taking the time to help out both with the formula and the data clarification.  I'm not very good at spreadsheets -- though I love using them -- and sort of just blunder my way through them.  

[Anonymous]

You're making it much more complicated than it shoud be.

1.  List the dates the accounts were opened in Column C

2.  In Column D, add the formula =DATEDIF(C7,TODAY(),"m") for each cell in Column C.  This formula calculates your account age in months.  You will need to change the C7 location for each corresponding cell; or better yet just add the formula to cell C7 and copy it down.  

3.  In Cell D17, add the formula =ROUND(((SUM(D7: D16)/COUNTA(D7: D16))/12),1) REMOVE THE SPACE BETWEEN THE COLON AND THE CELL REFERENCE This formula calculates the AAoA by adding the total months in Column D, then counting the number of cells in the index of D7: D16 and using that count to divide the months (That's your average).  Divide by 12 to convert the result to YEARS, and round by 1 to limit the result to one decimal point.

Where do I send the invoice?

[Asilomar]

Yipee! More help!  

And I'm making it so much more complicated than it should be because I suck at spreadsheeting.    

I havn't yet had a chance to get back to my little project but rest assured I'll be trying out both yours and iv's formulas soon.  

And you might want to hold on to that invoice -- I may owe you more money since I suspect I'm going to have a hard time following your instructions the first go round.  

[Anonymous]

 

[Asilomar]

Awww!  MaizeandBlue, you doll, you!  

Your 'sheet is so elegant...so streamlined... while mine is... total crap.    

What is it you do for a living?  Please tell me it has something to do with working on spreadsheets 8 hours a day...

[Anonymous]

I've been using spreadsheets - Lotus 1-2-3 and Quattro Pro - since the mid-80's.