I’m using the Savings Budget and I’m noticing that some Categories are having the Actuals amounts completely missing from the table. I see the correct Actuals data for these few Categories in both the Monthly Budget template, as well as in the hidden columns to the right within the Savings Budget. They just are not actually showing up in the table (Columns B through I).
Just a follow-up here. I’ve found for some of the Categories that are behaving as described in the original post, if I change that Category name to something else (e.g. Utilities is not showing any Actual data on the Savings Budget B-I columns (but again is on the hidden columns and on the Monthly Budget, so I change it to “Stuff”) and I am able to see the correct values appear in the Savings Budget. Utilities is nested under parent group Bills & Utilities. Does this make any sense to anyone?
Alright, I think figured out the issue I believe but have no clue how to fix this. It looks like certain categories in an alphabetical order are not including Actuals data in the Savings Budget. The Budget data is appearing, but not Actual, as mentioned above. If I change a Category that appears lower down on the Category list (Utilities for example, to Tiles, which appears higher on the alphabetical Categories sheet list), and match the Utilities transactions to the new Tiles category, I am able to see this data appear in the Actual. But if then change that category to something that is lower down on the alphabetical Categories list, like Zebra, and recategorize the transactions, then the Actual data disappears again. So it seems that the Actual column on Saving Budget is not including Actual data for Categories that are lower down in the alphabetical ordering on the Categories sheet. Is a formula not accounting for the larger Categories list I have (currently 208 Categories on the Categories sheet)? I looked at the formula in the Actual column on the Savings Budget page, but can’t figure this one out. Thanks.
Gonna tag @randy since he was the creator of the Savings Budget web page. No hurry, just wanna make sure your eyes see this at some point. Thank you.
Thanks for documenting this, @mu3484343. Most likely the issue is the large number of categories. We design most stock templates for up to 100 accounts and up to 200 categories. Often, accommodating more categories is simply a matter of expanding formulas down into sufficient rows… but we don’t typically build or test for this because there can be performance issues for the vast majority of users that have a few dozen categories.
Ya, I’m noticing it’s cutting off including Actuals for Categories around row 195-197 from the Categories sheet. Weird thing is, the Budgets are actually being included throughout the entire Category sheet.
It’s also including all of the Actuals data in the Hidden columns labeled “Actuals in Period”, “Actuals Prior to Period”, “Integrated Categories”, and “Ordered Categories for Displayed Category List”. So all of this data appears to be being pulled correctly into the Savings Budget sheet (hidden columns), it’s just not being output in that Actual column for the visible data. Any idea if there is something in this sheet that I can tweak to fix that?
I should also note that the Budget and Savings columns on the Savings Budget sheet is showing data for all Categories as well. Which is why I’m wondering if there is just something limiting the output within the coding on the Savings Budget sheet (and not something more systemic throughout the entire Foundation suite of sheets).
Ok, now I’ve pin pointed it down to the Hidden “Modified Actuals” column on the Savings Budget sheet. It is not outputting the Actuals amount once it gets down the Category list…so I’m gonna look into this formula for that column.
Sure enough, the formula for the hidden “Modified Actual” on the Savings Budget page needed to be extended down. Works perfectly. This sheet is amazing, great work and thanks for the webinar explaining it all. I appreciate your work.
I try to use ARRAYFORMULAs when possible rather than extending formulas down. These essentially accommodate unlimited rows. Some formulas aren’t compatible with ARRAYFORMULA and I often expand those down to accommodate up to 200 categories. Most likely this explains the behavior of some calc columns working and others not working.
Glad you figured it out. Be aware that if you update or restore the template, it will wash out your changes.
1 Like