I apologize for the late reply. I had fractured my hand recently, and have been minimizing my exposure to typing.
So if I understand correctly:
- You want to figure out the minimum amount due to avoid a late fee, instead of the minimum amount due to avoid interest charges.
- To achieve this, youâre entering credit cards in as recurring expenses on the
Recurring Expense Entry sheet.
- To get the desired balance for each credit card, you created a formula that looks at the
Business Dashboard solution.
- This formula multiplies the total amount due by .002 if that amount is above $25 to get 2% of the balance. Credit cards without a balance are written as zero.
- These zero balanced cards are showing up on the
Upcoming Expenses dashboard in the âExpenses due by next checkâ section to the right.
- You want to filter these cards with a zero balance from showing up in this section.
Recurring expenses are supposed to be expenses with a regular or semi-regular amount that occur on a cadence (weekly, bi-weekly, etcâŚ). Credit cards and balances are handled by a different part of my solution. I never really intended on credit card totals ever being entered into the recurring expense list, so your fix might cause some unforeseen issues.
You can remove the cards with a zero balance from showing up by just adding another filter exception to the formula in P3 on the Upcoming Expenses sheet to filter out expenses that have a balance of zero. This is a more lightweight and âTiller friendlyâ solution than your script. It will also have less unintended effects vs. using that script. Just replace P3 on Upcoming Expenses with:
={"Expense","Due Date","Amount","Pay Method","Auto-Pay";
{
SORT(
{
IFERROR(QUERY(FILTER({INDIRECT($AF$11), INDIRECT($AF$12), INDIRECT($AF$13), INDIRECT($AF$14), INDIRECT($AF$15), INDIRECT($AF$16), INDIRECT($AF$17)}, INDIRECT($AF$17) = 1, INDIRECT($AF$16) = "" , INDIRECT($AF$12) < Q1, INDIRECT($AF$13) <> 0), "SELECT Col1, Col2, Col3, Col4, Col5"), {"","","","",""});
IFERROR({QUERY(FILTER({INDIRECT($AK$13), INDIRECT($AK$16), INDIRECT($AK$15), INDIRECT($AK$17), INDIRECT($AK$20)}, INDIRECT($AK$13) <> "", INDIRECT($AK$16) < Q1, VALUE(INDIRECT($AK$15)) > 0), "SELECT Col1, Col2, Col3, Col4, Col5")}, {"","","","",""})
}, 2, TRUE)
}
}
But I would like to suggest a different solution altogether:
Credit cards really do not belong in Recurring Expenses. It would be better to change the Credit Card Settings sheet to add a new column that holds your calculation. Then, you can pull that column in to use on the Upcoming Expenses dashboard instead of the Total Due amount currently being used.
I can probably write down some instruction for you to implement this on the side, if youâre interested. I do not want to incorporate this logic into the main solution, however, as I donât really want to promote using the minimum amount due to avoid a late fee. It is antithetical to maintaining proper financial health. I personally do not acknowledge that amount exists. The minimum amount due, to me, is always the minimum amount you need to pay to avoid interest charges (your previous statement balance).
