No credit card required
Browse credit cards from a variety of issuers to see if there's a better card for you.
@ccquest wrote:Alright, here's the latest version everybody!
This should work for all versions and shows the age in a bit better format thanks to recommendation of Cassie.
Hope it works better for everyone and thanks for the feedback!
That looks nice! I'll take a look inside.
Now that you've given birth to it, you're stuck with raising it for a minimum of 18 years!
"Every program attempts to expand until it can read mail. Those programs which cannot so expand are replaced by ones which can."
- Zawinski's Law of Software Envelopment (Link)
There is an error in the AAoOIA formula in cell H9. It returns #DIV/0! if there are no Open Installment accounts. (The Average function didn't find any Open Installment accounts - 0/0.)
Use this formula in H9 instead:
=INT(IFERROR(AVERAGEIFS(J18:J200,B18:B200,"Installment", C18:C200, "Open"),0)/12)&"yr "&ROUND(MOD(IFERROR(AVERAGEIFS(J18:J200,B18:B200,"Installment", C18:C200, "Open"),0),12),0)&"mo"
IL TOTAL LIMIT will be $0.00 with no OR closed installment accounts on file. That will make IL % return a #DIV/0! error.
Use this formula in F15:
=IF(H15=0,0,G15/H15)
This workbook can be used by anyone with Excel 2007 or later, if the total dollar amount cell formulas are changed to use SUMIFS.
It looks like this for REV $, cell G13 (change range criteria for the other values):
=SUMIFS(F18:F200,B18:B200,"Revolver", C18:C200, "Open")
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, citeria2)
Matching Revolver and Open here. Cell H15 (IL TOTAL LIMIT) would need to match Installment and Open, and so on for the other cells.
@ccquestYou can bet if Cassie told you SUMIFS works on 2007, it will! 😉 But, I’ve got 2016, so I am great now!! I can’t wait to have time to put my data in! Hopefully most ppl have '16 or higher anyway. If not, upgrade might be worth it.
I think it’s awesome! what did you change in the latest version? I made the two changes Cassie suggested, H9 & F15. But tell us what goodies you added re: logic!
Im excited! If we can get people to use this, we can get accurate data instead of the skewed stuff from the frontends, it’ll make it so much easier to solve people’s issues and collect accurate dps!
Patch Notes:
- Bug fixes and improvements
The end.
Just kidding, hate those ones! The ones off the top of my head are:
Think that covers them...for the %s it may have been more direct to check if the Total Limit in columns D or H were $0 and just blank it out, but I wanted to have similar logic throughout all of them that looks at the data instead of other calculations.
@ccquest wrote:That's what happens when you build it just based on your own case and don't bother testing others
That's exactly how I build most Windows apps for myself - bare minimum, occasional try-catch block, several synchronous functions that lock up the UI thread because too lazy to thread it properly....lol total opposite of what I would do for any corporate application.
I've added more logic based on whether or Installment and Revolvers are present and/or open. Stayed away from SUMIFS still because the Microsoft site doesn't list it as supported in 2007, earliest is "Excel Starter 2010" but doesn't even mention 2013 or 16.
This version overwrote the one from before so link should take you to the latest still.
You did great using array formulas!
Microsoft lists most functions with a compatibility line of "Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web Excel 2019, Excel 2019 for Mac", but the file compatibility checker in Excel will tell a different story. Aiming for Excel 97-2003 compatibility is painful and just not worth it anyway.
Your file is 2007+ compatible as is - I just think it's easier to read SUMIFS functions (introduced with 2007) if I'm changing a cell formula anyway.
(@Anonymous also) Should TOTAL LIMIT under ALL ACCOUNTS include a Closed Installment Loan Limit? It's a straight SUM under AGG $ and TOTAL LIMIT.
Change the number of decimal places for any Percentage format cell to 2.
I'm setting up my profile to report 2 cards as close to 9.5% as possible without going over.
With a single decimal place, Utilization will show as 9.5% when I actually have 9.45% (189/2000) and 9.49% ( 1234/13000).
Less than 9.5% should not cause a jump into the 10%+ interval, based on new info from the AMA.
@Anonymous wrote:
(@Anonymous also) Should TOTAL LIMIT under ALL ACCOUNTS include a Closed Installment Loan Limit? It's a straight SUM under AGG $ and TOTAL LIMIT.
That part was done 100% on purpose. I wasn't sure on exactly how closed accounts report and I'm pretty sure I've read it varies based on the account type and maybe lender too. Like some closed revolvers may have a $600 balance left on what was a $2500 limit and report at 100% utilization because it's closed, while others may be 24%. It's just flat total numbers to avoid any misrepresentation there.
There may be some version of aggregate revolver utilization to incorporate though that looks at open accounts and also adds in closed revolvers with balances as if they were always at 100% (so add the balance as a limit instead of even considering the limit).
We’re not exactly sure how the algorithm handles closed accounts. I think we’re fairly certain it does not show maxed out unless balance chased possibly.
If the account is closed with no balance, the limit should not be included, whether revolving or installment.
If a revolver is closed with a balance, the balance should be included in balances, but for the limits, I’m really uncertain. We could have two cells, one with and one without the closed revolver limit maybe?
I have seen no evidence closed installment loans are treated differently from closed revolvers.
A potential development suggestion. Include an inquiry counter that included dates and type of inquiry.
Edited.