So it's not really a question (unless anyone has a suggestion for a better way to go about it) and if this isn't the place to post this sort of thing please let me know, but I've put together a sheet in my budget that tracks the account that my bills are set to auto-pay from and forecasts out the next 21 pay periods (6 months for me) to help me predict savings or dips in the balance (eg. all the bills hit at once). It's been immensely useful for me to see and I wanted to share it where it might be appreciated.
I posted here a few months ago looking for help with the FILTER function, like so many do. I got some great advice, but couldn't quite put the pieces together. A few weeks later, after a lightbulb moment as I talked about it with family, it clicked. I've finally gotten to a point where the formulas work *and* I can explain how.
With all the over-hype out of the way, here's The Thing:
"Out" column formula (where "A18" is the first cell of the "Day" column and "ReserveBills15" is the "Bills on Auto Pay" table):
Formula Step Breakdown:
Step 1: Check for Bills with due date greater than this payday and less than next payday
Step 2: If next payday is the first (breaks first formula), check for bills with due date greater than this payday and less than End of Month
Step 3: If this pay period wraps into next month, check for bills due after this payday or before next payday
Step 4: Zero (nothing due)
Bonus Formula:
If you take the SUM function out and change the return targets from "Due Date" to "Name", Excel will spill all the bills due within the specified date range
Including a mini-sheet with the pictured tables as a playground/reference.
Tell me what you think!!
I posted here a few months ago looking for help with the FILTER function, like so many do. I got some great advice, but couldn't quite put the pieces together. A few weeks later, after a lightbulb moment as I talked about it with family, it clicked. I've finally gotten to a point where the formulas work *and* I can explain how.
With all the over-hype out of the way, here's The Thing:
"Out" column formula (where "A18" is the first cell of the "Day" column and "ReserveBills15" is the "Bills on Auto Pay" table):
Excel Formula:
=-SUM(FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]>=A18)*(ReserveBills15[Due Date]<A19),IF(A19=1,FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]<=(EOMONTH(B18,0)))*(ReserveBills15[Due Date]>A18),0),IF((A18<=6)+(A18>=24),FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]<=A19)+(ReserveBills15[Due Date]>=A18),0),0))))
Formula Step Breakdown:
Step 1: Check for Bills with due date greater than this payday and less than next payday
Step 2: If next payday is the first (breaks first formula), check for bills with due date greater than this payday and less than End of Month
Step 3: If this pay period wraps into next month, check for bills due after this payday or before next payday
Step 4: Zero (nothing due)
Bonus Formula:
If you take the SUM function out and change the return targets from "Due Date" to "Name", Excel will spill all the bills due within the specified date range
Excel Formula:
=(FILTER(ReserveBills15[Name],(ReserveBills15[Due Date]>=A18)*(ReserveBills15[Due Date]<A19),IF(A19=1,FILTER(ReserveBills15[Name],(ReserveBills15[Due Date]<=(EOMONTH(B18,0)))*(ReserveBills15[Due Date]>A18),0),IF((A18<=6)+(A18>=24),FILTER(ReserveBills15[Name],(ReserveBills15[Due Date]<=A19)+('Monthly Budget.xlsx'!ReserveBills15[Due Date]>=A18),0),"None"))))
Including a mini-sheet with the pictured tables as a playground/reference.
Tell me what you think!!
Reserve Forecast + Tracker.xlsx | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
1 | Current Balance | |||||||||||||||||
2 | 1318 | |||||||||||||||||
3 | In Options | Bal. Options | Reference table not pictured (privacy) | |||||||||||||||
4 | $175 | $768.00 | (Current) | |||||||||||||||
5 | $287.17 | $1,055.17 | (+Next Deposit) | |||||||||||||||
6 | $300 | |||||||||||||||||
7 | ||||||||||||||||||
8 | ||||||||||||||||||
9 | ||||||||||||||||||
10 | ||||||||||||||||||
11 | ||||||||||||||||||
12 | Set Aside: | $287.17 | ||||||||||||||||
13 | This Period | |||||||||||||||||
14 | 1/27/2023 | 27 | ||||||||||||||||
15 | 2/3/2023 | 3 | Current Bal. | Amount Due per Period | ||||||||||||||
16 | $1,318.00 | Years | 2023 | |||||||||||||||
17 | DAY | DATE | IN | OUT | BALANCE | DATE | Feb | |||||||||||
18 | 27 | 1/27/2023 | $287.17 | -$550.00 | $768.00 | |||||||||||||
19 | 3 | 2/3/2023 | $287.17 | -$84.52 | $970.65 | Date | Sum of OUT | |||||||||||
20 | 10 | 2/10/2023 | $287.17 | $0.00 | $1,257.83 | 3 | ($84.52) | |||||||||||
21 | 17 | 2/17/2023 | $287.17 | -$350.00 | $1,195.00 | 10 | $0.00 | |||||||||||
22 | 24 | 2/24/2023 | $287.17 | -$605.00 | $877.18 | 17 | ($350.00) | |||||||||||
23 | 3 | 3/3/2023 | $287.17 | -$84.52 | $1,079.83 | 24 | ($305.00) | |||||||||||
24 | 10 | 3/10/2023 | $287.17 | $0.00 | $1,367.00 | |||||||||||||
25 | 17 | 3/17/2023 | $287.17 | -$350.00 | $1,304.18 | |||||||||||||
26 | 24 | 3/24/2023 | $287.17 | -$55.00 | $1,536.35 | Bills on Auto Pay | ||||||||||||
27 | 31 | 3/31/2023 | $287.17 | -$634.52 | $1,189.00 | Name | Amount | Due Date | ||||||||||
28 | 7 | 4/7/2023 | $287.17 | $0.00 | $1,476.18 | Rent | $500.00 | 1 | ||||||||||
29 | 14 | 4/14/2023 | $287.17 | $0.00 | $1,763.35 | Storage | $50.00 | 1 | ||||||||||
30 | 21 | 4/21/2023 | $287.17 | -$405.00 | $1,645.53 | Progressive | $84.52 | 6 | ||||||||||
31 | 28 | 4/28/2023 | $287.17 | -$550.00 | $1,382.70 | Credit Card | $350.00 | 21 | ||||||||||
32 | 5 | 5/5/2023 | $287.17 | -$84.52 | $1,585.35 | Internet | $55.00 | 25 | ||||||||||
33 | 12 | 5/12/2023 | $287.17 | $0.00 | $1,872.53 | |||||||||||||
34 | 19 | 5/19/2023 | $287.17 | -$405.00 | $1,754.70 | |||||||||||||
35 | 26 | 5/26/2023 | $287.17 | -$550.00 | $1,491.88 | |||||||||||||
36 | 2 | 6/2/2023 | $287.17 | -$84.52 | $1,694.53 | |||||||||||||
37 | 9 | 6/9/2023 | $287.17 | $0.00 | $1,981.70 | |||||||||||||
38 | 16 | 6/16/2023 | $287.17 | $0.00 | $2,268.88 | |||||||||||||
39 | ||||||||||||||||||
40 | Due This Week | |||||||||||||||||
41 | Rent | Reserve Table Step Logic | D1 = Day 1 | |||||||||||||||
42 | Storage | Initial SUM of Amount with Due Date (> D1) AND (< D2) | D2 = Day 2 | |||||||||||||||
43 | 0 | |||||||||||||||||
44 | If Zero AND D2 =1, SUM of Amount with Due Date (<= EOM) AND (> D1) | |||||||||||||||||
45 | 0 | |||||||||||||||||
46 | If Zero AND D2 <=6 OR D1 >=24, SUM of Amount with Due Date (<= D2) + (> D1) | |||||||||||||||||
47 | -550 | |||||||||||||||||
48 | Else, Zero | |||||||||||||||||
49 | ||||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E4 | E4 | =O2+D18 |
E5 | E5 | =E4+C12 |
C14:C15 | C14 | =DAY(B14) |
B15,B19:B38 | B15 | =B14+7 |
E16 | E16 | =O2 |
A18 | A18 | =DAY($B$18) |
B18 | B18 | =B14 |
C18:C38 | C18 | =$C$12 |
D18:D38 | D18 | =-SUM(FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]>=A18)*(ReserveBills15[Due Date]<A19),IF(A19=1,FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]<=(EOMONTH(B18,0)))*(ReserveBills15[Due Date]>A18),0),IF((A18<=6)+(A18>=24),FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]<=A19)+(ReserveBills15[Due Date]>=A18),0),0)))) |
E18 | E18 | =O2+D18 |
A19:A38 | A19 | =DAY(B19) |
E19:E38 | E19 | =SUM(E18,[@IN],[@OUT]) |
A41:A42 | A41 | =(FILTER(ReserveBills15[Name],(ReserveBills15[Due Date]>=A18)*(ReserveBills15[Due Date]<A19),IF(A19=1,FILTER(ReserveBills15[Name],(ReserveBills15[Due Date]<=(EOMONTH(B18,0)))*(ReserveBills15[Due Date]>A18),0),IF((A18<=6)+(A18>=24),FILTER(ReserveBills15[Name],(ReserveBills15[Due Date]<=A19)+(ReserveBills15[Due Date]>=A18),0),"None")))) |
E43 | E43 | =-SUM(FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]>=A18)*(ReserveBills15[Due Date]<A19),0)) |
E45 | E45 | =-SUM(IF(A19<2,FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]<=(EOMONTH(B18,0)))*(ReserveBills15[Due Date]>A18),0))) |
E47 | E47 | =-SUM(IF((A19<6)+(A18>24),FILTER(ReserveBills15[Amount],(ReserveBills15[Due Date]<=A19)+(ReserveBills15[Due Date]>=A18),0),0)) |
Dynamic array formulas. |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
A41:A45,G28:G32 | Cell Value | duplicates | text | NO |
A41:A45 | Cell Value | duplicates | text | NO |
E18:E38 | Other Type | Color scale | NO | |
E16 | Cell Value | <#REF! | text | NO |
E16 | Cell Value | >#REF! | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
C12 | List | =$C$4:$C$6 |
Last edited: