"Available" in Savings Budget not calculating correctly

Google Sheets
1

I’m trying to use the Savings Budget community add-on, but the “Available” field in the Savings Budget tab is not calculating properly. It seems to be ignoring the “Budget” column in it’s calculation of what’s available. It should be: Savings+Budget-Actual

I think I’ve tracked it down to a buggy formula in the hidden section at W10. It seems to be putting together an array to populate the table of “Category”, “Type”, “Period Budget” and “Prior Budgets”. The “Period Budget” is not populating. Here’s the current formula:
={{"Category","Type","Period Budget"};{INDIRECT(BD34&"!"&BE35&"2:"&BE35),INDIRECT(BD34&"!"&BE36&"2:"&BE36),OFFSET(INDIRECT("Categories!$A2:$A"),0,$BE$19-1)}}

Here are some screenshots to show the problem:

Screen Shot 2023-10-18 at 9.04.06 AM
Screen Shot 2023-10-18 at 9.04.06 AM2346×1318 335 KB

Can someone help me troubleshoot why the Budgeted amount is not being considered in the equation. What’s interesting is that the “progress bar” is properly calculating the remaining amount.

2

Here’s another screenshot:

Screen Shot 2023-10-18 at 9.04.17 AM
Screen Shot 2023-10-18 at 9.04.17 AM1364×1236 115 KB

3

Hi @daniel.trubeyweller you do have a REF error in E12. Can you try to clear out that cell if something was typed in there or restore the template using the Tiler Community Solutions add-on?

1 Like
4

Here is the formula in that cell and it’s human readable error:

=ARRAYFORMULA(IF(N12:N="Category",IF(ISBLANK(VLOOKUP(B12:B,{$AE$11:$AE,$AM$11:$AM},2,FALSE)),if($BE$7="Budget",VLOOKUP(B12:B,{$AE$11:$AE,$AL11:$AL},2,FALSE),iferror(1/0)),VLOOKUP(B12:B,{$AE$11:$AE,$AM$11:$AM},2,FALSE)+if($BE$7="Budget",VLOOKUP(B12:B,{$AE$11:$AE,$AL11:$AL},2,FALSE),0)),IF(N12:N="Type",SUMIF($AF$11:$AF&$AH$11:$AH,B12:B,$AM$11:$AM)+if($BE$7="Budget",SUMIF($AF11:$AF,B12:B,$AL$11:$AL),0),IF(ISBLANK(N12:N),IFERROR(1/0),SUMIF($AF$11:$AF&$AG$11:$AG&$AH$11:$AH,N12:N&B12:B,$AM$11:$AM)+if($BE$7="Budget",SUMIF($AF$11:$AF&$AG$11:$AG,N12:N&B12:B,$AL$11:$AL),0)))))

Screen Shot 2023-10-18 at 1.30.51 PM
Screen Shot 2023-10-18 at 1.30.51 PM1160×662 42.5 KB

5

It means there is some text in E20 that you need to clear out so the formula can fill out that column. There could be text in other cells just keep clearing out the cell referenced in the error until the Error no longer appears or use the Tiller Community Solutions add-on to restore the dashboard and fix the errors that way.

2 Likes
6

Good catch @morgan i believe you are typing in these budget amounts (shown in E20:E25 on your screenshot) manually on this sheet which should be the issue. These are going to automatically populate from the categories sheet based on the month and year selected in I1:I2. Clear these manual inputs out like @heather said and everything should go back to working.

Generally speaking, on templates like this the only part that should be edited is in the green cells. Everything else is generated based on those selections.

2 Likes
7

Thanks everyone! I didn’t realize the budget is input in the categories sheet. It’s fixed now :slight_smile: