Looking for a Group Budget solution

Google Sheets
1

I’m trying to figure out how I can have a Group budget, or something that has a similar outcome. I’ve seen one other thread about this, but the solution did not seem to address what I’m looking to do.

Food is the perfect Group example for this. Say I’d like to set aside $1,000/mo for anything related to food - that would include Groceries, Restaurants, Fast Food. However, Each of the category amounts fluctuate each month so setting individual budgets for each isn’t really feasible. Instead, setting the Food Group budget to $1,000/mo would give me flexibility to spend in those categories however we’d like, while still respecting that $1,000 Group budget between those 3 categories.

Tracking actual spending in Groceries, Restaurants, etc (i.e. the Categories) is important, but the budget would ideally just be the Food Group holding them all. I’ve tried a few things, but since the Savings Budget and Budgeting in general is built around the Categories sheet, with budgets set there, and there doesn’t seem to be a way to have a Group budget on that sheet, I can’t figure this out.

2

What if you set Food as the category and then use Groceries, Restaurants, Fast Food as tags? You’d just tag anything in the Food category with the appropriate tag. That way you’d have the budget for the category, and you could still differentiate spending within that category.

1 Like
3

Thanks for the suggestion. That’s an easy enough solution to implement, the issue with that though is seeing the breakdown in many sheets that don’t have tag functionality built in and rely on Groups and Categories. Savings Budget for example, or Profit and Loss.

4

True. I find both Tags Report and Transaction Tracker to be very handy and easy-to-use tools for keep track of spending by tags. But obviously that doesn’t work if you want everything on one sheet.

1 Like
5

How about using a Food Budget category to set the budget and then treat a budget of zero to mean no budget that just always reports zero available so it isn’t flagged over-budget?

Here’s a suggestion for the Monthly Budget sheet, where if the Category contains keyword Budget, available is also set to zero.

Categories sheet:
image

Monthly Budget sheet:

image
image752×190 13.9 KB

Monthly Budget cell H16 formula addition:
if((F16:F=0)+(ISNUMBER(FIND("Budget",B16:B))),0,

Cell H16 Before:

=arrayformula(if(isblank(B16:B),iferror(1/0),if(counta($N$16:$N)=2,if(row($G16:$G)-row($G$16)>=max($N$16:$N),F16:F-G16:G,G16:G-F16:F),if($Q$16="Income",G16:G-F16:F,F16:F-G16:G))))

Cell H16 After:

=arrayformula(if(isblank(B16:B),iferror(1/0),if((F16:F=0)+(ISNUMBER(FIND("Budget",B16:B))),0,if(counta($N$16:$N)=2,if(row($G16:$G)-row($G$16)>=max($N$16:$N),F16:F-G16:G,G16:G-F16:F),if($Q$16="Income",G16:G-F16:F,F16:F-G16:G)))))
1 Like
6

Thanks for the suggestion. Trying to figure out if I can integrate this into the Savings Budget.

7

I don’t use the Savings Budget, but I’ll take a peek at it :face_with_monocle:

1 Like
8

Here’s the equivalent/similar change to the Savings Budget.

image
image902×267 33.3 KB

Savings Budget cell H12 addition after IF(N12:N="Category",:
IF((E12:E=0)+(ISNUMBER(FIND("Budget",B12:B))),0,

Cell H12 Before:

=ARRAYFORMULA(IF(N12:N="Category",VLOOKUP(B12:B,{$AE$11:$AE,$AR$11:$AR},2,FALSE),IF(N12:N="Type",SUMIF($AF$11:$AF&$AH$11:$AH,B12:B,$AR$11:$AR),IF(ISBLANK(N12:N),IFERROR(1/0),SUMIF($AF$11:$AF&$AG$11:$AG&$AH$11:$AH,N12:N&B12:B,$AR$11:$AR)))))

Cell H12 After:

=ARRAYFORMULA(IF(N12:N="Category",IF((E12:E=0)+(ISNUMBER(FIND("Budget",B12:B))),0,VLOOKUP(B12:B,{$AE$11:$AE,$AR$11:$AR},2,FALSE)),IF(N12:N="Type",SUMIF($AF$11:$AF&$AH$11:$AH,B12:B,$AR$11:$AR),IF(ISBLANK(N12:N),IFERROR(1/0),SUMIF($AF$11:$AF&$AG$11:$AG&$AH$11:$AH,N12:N&B12:B,$AR$11:$AR)))))

To remove the sparkline in the PROGRESS column for BUDGET=0.
Cell G12 outer IF-block addition:
IF(E12=0,,

Cell E12 Before:

=IFERROR(
  IF(or(O12="Expense",N12="Expense",and(N12="Type",B12="EXPENSE")),
    IF(F12+1<=E12+D12,  
      SPARKLINE({if(isblank(F12),0,F12)},{"charttype","bar";"max",E12+D12;"color1","#2e86de"}), 
      SPARKLINE({max(0,E12+D12),F12-E12-D12},{"charttype","bar";"max",F12;"color1","#69c569";"color2","#de4c60"})),
    IF(or(O12="Income",N12="Income",and(N12="Type",B12="INCOME")),
      IF(F12<=E12-D12, 
        SPARKLINE({if(isblank(F12),0,F12),E12-D12-F12},{"charttype","bar";"max",E12-D12;"color1","#2e86de";"color2","#de4c60"}),
        SPARKLINE(F12,{"charttype","bar";"max",F12;"color1","#69c569"})),
    IFERROR(1/0))))

Cell E12 After:

=IFERROR(
  IF(E12=0,,
    IF(or(O12="Expense",N12="Expense",and(N12="Type",B12="EXPENSE")),
          IF(F12+1<=E12+D12,  
            SPARKLINE({if(isblank(F12),0,F12)},{"charttype","bar";"max",E12+D12;"color1","#2e86de"}), 
            SPARKLINE({max(0,E12+D12),F12-E12-D12},{"charttype","bar";"max",F12;"color1","#69c569";"color2","#de4c60"})),
          IF(or(O12="Income",N12="Income",and(N12="Type",B12="INCOME")),
            IF(F12<=E12-D12, 
              SPARKLINE({if(isblank(F12),0,F12),E12-D12-F12},{"charttype","bar";"max",E12-D12;"color1","#2e86de";"color2","#de4c60"}),
                  SPARKLINE(F12,{"charttype","bar";"max",F12;"color1","#69c569"})),
          IFERROR(1/0)))))

The cell E12 formula change would need to be copied down to the end of column E, since it is not an ARRAYFORMULA and cell has it’s own formula.

1 Like