🏆 Upcoming Recurring Expense / Account Health Dashboard

[sparker81]

I have found a minor calculation issue I believe in the sheet. The counting of credit card payments due seemed to be showing incorrectly. I had more than 1 credit card due within the period, but the information in cell K15 was showing that only one expense was from credit card debt. The formula in cell AD29 is looking at the Pay Method column instead of the Expense column from the Filter formula. I believe it should be specifying P4:P35.

=IFERROR(QUERY(UNIQUE(QUERY(FILTER({S4:S35}, MATCH($P$4:P35, INDIRECT($AK$13),0)), “SELECT Col1”)), “SELECT COUNT(Col1) LABEL COUNT(Col1)‘’”),0)

This should be:

=IFERROR(QUERY(UNIQUE(QUERY(FILTER({P4:P35}, MATCH($P$4:P35, INDIRECT($AK$13),0)), “SELECT Col1”)), “SELECT COUNT(Col1) LABEL COUNT(Col1)‘’”),0)

After changing this, the count showed the correct number of expenses that were going to credit cards.

Also, I found that the dollar values being displayed in K14 through K18 were not showing 2 decimal places when the numbers ended with 0’s. The formatting for the TEXT in these cells are “$####.##”, but should be “$#,##0.00” to make sure that the value always shows 2 decimal places and to (optionally) include the comma for the thousands separator. The easy fix would be to do the Find and replace (CTRL+H) and put $####.## to find, and $#,##0.00 for replace, make sure that Also search within formulas is selected, then Replace all.

[1Email2RuleThemAll]

Thanks for catching that! The text insights area was sort of a last minute addition, and I don’t even have them in the version that I use. So I wouldn’t have caught it during normal use. My version of this sheet has a picture of my dog taking up about the same amount of space. When offering it to the community, I decided to add some text insights because it looked kind of bare without them.

Thanks for the formatting tip! I tried for a little bit to get this exact formatting, but didn’t know zeros were the answer. I’ve updated the template to fix these findings. Thanks again!

[annaisakiwi]

Hi! Finally getting around to working through this setup… and I feel like i’m almost there! But having a few stumbles too - hoping you can help?

Found I had some errors on the Generated Expenses - Upcoming sheet as I didn’t have the “Note” column in my transaction sheet. Fixed that one

On the Upcoming Expenses sheet under both checking accounts and savings accounts, I have #N/A

And on the ‘Generated Expenses - Upcoming’ not all transactions are being recognised as paid - not sure how to dig into this thou?

[1Email2RuleThemAll]

Sorry to hear you’re having some troubles! I’m here to help though! When you hover over the #N/A error, what does it say? I’m not exactly sure what that could be off the top of my head. The checking accounts are coming from columns X7:X16, which are being pulled in from the Accounts sheet. All accounts with a type of Checking should be present. Similarly, savings accounts ultimately come from columns X20:X34, which should be all accounts on the Accounts sheet with a type of Savings. If you look in column X on the sheet, does it list the accounts there correctly, or is there an error there as well?

For the Generated Expenses - Upcoming sheet, the logic to mark an expense as paid is looking at a timeframe depending on the frequency chosen for the expense. This is to hone into the right expense to mark off, and avoid expenses falling off erroneously. Each frequency has their own timeframe they look at. For a weekly expense, it will only mark the expense as ‘paid’ if the transaction date you tagged in Transactions is within +/- 3 days of the expense date. So a weekly expense due on January 23rd has to be tagged to a transaction with a date between January 20th and January 26th in order to be marked as paid and fall off. Could this be what’s happening here?

If the expenses that are not falling off are under the frequency of “One-Time”, there was a bug that another user found that is causing this. The fix can be found here.

Expense frequency windows:

• Weekly: +/- 3 days
• Bi-weekly: +6 / -7 days
• Semi-monthly: +6 / -7 days
• Monthly: +14 / -13 days
• Bi-monthly: +/- 29 days
• All Else: +/- 60 days

[jjfelton4]

Hello @1Email2RuleThemAll !

For some reason I’m having an issue with Upcoming Expenses not showing anymore.

I tracked it down to the ‘Generated Expenses - Upcoming’ worksheet, for some reason everything is showing up at N/A on the Generated Expenses window.

It has an error : “Array arguments to REPT are of different size.”

Here is a screenshot :

I looked up that error and it has something to do with rows not matching the array…but I know I haven’t messed with anything like that!

Let me know, thanks!

[1Email2RuleThemAll]

This typically happens if there’s an expense entered without a frequency chosen on Recurring Expense Entry, or if there’s a frequency and start date, but no Expense name.

If you look at the list in columns A:J on the Generated Expenses - Upcoming sheet, do you see any errors there? Specifically in the Occurrences column? Take a look at the below screenshot where I removed the frequency for the Mortgage entry. I suspect something like this is going on in your workbook. The solution is to go to that entry on Recurring Expense Entry and set the value that’s missing. Please let me know if this helps, or if we need to dig further! Thanks!

[jjfelton4]

That solved it.

Thank you!