I’m new to Tiller. For the month of September, the life insurance category (part of my financial group) is correctly populated in the monthly breakdown. E.g., $200 for this category. However, on the yearly view, the September life insurance cell in the “actual” column says “0”. Other rows in the Financial group are correctly populated for September in the yearly view. Also, the Total for the Financial group, actual column is correct - it seems to be using the missing life insurance data. I have the same issue periodically for other categories and months. Any ideas on how to trouble shoot this? I haven’t changed any forumulas, this is the “out of the box” Foundation template. TIA
Can you tell us a little bit more about your setup, @KenRo? Are you on Sheets or in Excel? You’re saying that the actual in the Monthly Budget dashboard is correct but the actual for the same month in the Yearly Budget is wrong?
Is it possible that your transaction “Date” is a timestamp with an hour/minute/second value appended? (I.e. wondering if this is an edge case with a time format spanning a month…)
Thanks Randy. I’m using Sheets.
Yes, the actual in the Monthly Budget dashboard is correct. See this screenshot for September:
But when you go to the Yearly dashboard, and look at September, the cell for the actuals for life insurance (column D) is blank. Oddly, the total for the financial group is correct, but the “actual” cells for the categories are missing data. (I’m putting the screenshot in the next post, as a new user I can only embed one image per post).
There are 4 transactions for life insurance in September – 2 on Sept 22 and 2 on Sept 1. But none of these transactions are reflected in the “Actual” cell in the yearly budget (so I don’t think it is just a Sept 1 date issue - if so, presumably the Sept 22 transactions would display),
Also, the formula for the life insurance “actual” cell in the yearly spreadsheet is =IF(isblank($A$7:$A),iferror(1/0),if(counta(‘Monthly Budget’!$N$16:$N)=2,if(row(AD36:AD)-row(AD$7)>=max(‘Monthly Budget’!$N$16:$N),-1,1),if(‘Monthly Budget’!$Q$16=“Income”,1,-1))*iferror(if(isna(match(row(AD36:AD)-row(AD$7),‘Monthly Budget’!$N$16:$N,0)),IF(isna(match(row(AD36:AD)-row(AC$7),‘Monthly Budget’!$O$16:$O,0)),IF (ISERROR(VLOOKUP($A$7:$A,{$AR$7:$AR,offset($AR$7:$AR,0,match(AC$3,$AS$5:$BD$5,0))},2,FALSE)),0,VLOOKUP($A$7:$A,{$AR$7:$AR,offset($AR$7:$AR,0,match(AC$3,$AS$5:$BD$5,0))},2,FALSE)),SUMIFS(offset($AR$7:$AR,0,match(AC$3,$AS$5:$BD$5,0)),$AP$7:$AP,$A$7:$A,$AQ$7:$AQ,“<>Hide”,$AO$7:$AO,if(row(AD36:AD)-row(AD$7)>=max(‘Monthly Budget’!$N$16:$N),“Expense”,“Income”))),SUMIFS(offset($AR$7:$AR,0,match(AC$3,$AS$5:$BD$5,0)),$AO$7:$AO,$A$7:$A,$AQ$7:$AQ,“<>Hide”)),0))
I didn’t intentionally change it.
Thanks for documenting this so thoroughly, @KenRo.
If the transactions from mid month aren’t summing either then I agree with you that it isn’t a beginning- or end-of-month date issue.
These templates have been used by 10k’s of users so a formula error seems unlikely but possible.
A few more ideas…
- I know you mentioned you haven’t messed with the template but have you tried doing a restore on the Yearly Budget yet? Use the TMF add-on. It’s worth a try.
- If I had the spreadsheet, I’d want to see what is going on in the hidden area. Unhide the cells off to the right and see if you can see if the actuals are totaling over there.
Sorry to not be of more help.
I did restore the yearly budget template, it made no difference.
I don’t really understand what you mean about unhiding the cells. I just can’t understand why this isn’t working. Super frustrating.
Just a note we’re troubleshooting this through our official support channel.