Adding Progress Bars to the Monthly & Yearly Budget templates

Google Sheets Show & Tell
1

@randy, How difficult would it be to add similar functionality to the Yearly Budget in the Foundation Template? I see that it is already included in the Monthly Budget view.

A specific use case is that if one has expenses which are paid sporadically, and/or which vary widely from month to month, a graphical representation showing expense/income progress could be very helpful.

I know I could repurpose your solution from the Savings Budget, but I wonder if others would find it useful in Foundation for the reason I mention?

Thanks!

2

I took a crack at this today, @tom.kermode.

Monthly Budget

Just drop this formula in E16 and expand it downward in the Monthly Budget sheet. 
=IFERROR(
  IF(IFERROR(VLOOKUP(row(B16)-Row(B$16), {$P$16:$P,$Q$16:$Q},2, FALSE),
      IFERROR(VLOOKUP(row(B16)-Row(B$16), {$O$16:$O,$Q$16:$Q},2, FALSE),
        IFERROR(VLOOKUP(row(B16)-Row(B$16), {$N$16:$N,$Q$16:$Q},2, FALSE),"")))="Expense",
    IF(G16+1<=F16,  
      SPARKLINE({if(isblank(G16),0,G16)},{"charttype","bar";"max",F16;"color1","#2e86de"}), 
      SPARKLINE({F16,G16-F16},{"charttype","bar";"max",G16;"color1","#69c569";"color2","#de4c60"})),
    IF(IFERROR(VLOOKUP(row(B16)-Row(B$16), {$P$16:$P,$Q$16:$Q},2, FALSE),
      IFERROR(VLOOKUP(row(B16)-Row(B$16), {$O$16:$O,$Q$16:$Q},2, FALSE),
        IFERROR(VLOOKUP(row(B16)-Row(B$16), {$N$16:$N,$Q$16:$Q},2, FALSE),"")))="Income",
      IF(G16<=F16, 
        SPARKLINE({if(isblank(G16),0,G16),F16-G16},{"charttype","bar";"max",F16;"color1","#2e86de";"color2","#de4c60"}),
        SPARKLINE(G16,{"charttype","bar";"max",G16;"color1","#69c569"})),
    IFERROR(1/0))))

Yearly Budget

Insert a new column to the right of column A. Insert drop this formula in B7 and expand it downward in the Yearly Budget sheet. 
  IF(IFERROR(VLOOKUP(row(B7)-Row(B$7), {'Monthly Budget'!$P$16:$P,'Monthly Budget'!$Q$16:$Q},2, FALSE),
      IFERROR(VLOOKUP(row(B7)-Row(B$7), {'Monthly Budget'!$O$16:$O,'Monthly Budget'!$Q$16:$Q},2, FALSE),
        IFERROR(VLOOKUP(row(B7)-Row(B$7), {'Monthly Budget'!$N$16:$N,'Monthly Budget'!$Q$16:$Q},2, FALSE),"")))="Expense",
    IF(D7+1<=C7,  
      SPARKLINE({if(isblank(D7),0,D7)},{"charttype","bar";"max",C7;"color1","#2e86de"}), 
      SPARKLINE({C7,D7-C7},{"charttype","bar";"max",D7;"color1","#69c569";"color2","#de4c60"})),
    IF(IFERROR(VLOOKUP(row(B7)-Row(B$7), {'Monthly Budget'!$P$16:$P,'Monthly Budget'!$Q$16:$Q},2, FALSE),
      IFERROR(VLOOKUP(row(B7)-Row(B$7), {'Monthly Budget'!$O$16:$O,'Monthly Budget'!$Q$16:$Q},2, FALSE),
        IFERROR(VLOOKUP(row(B7)-Row(B$7), {'Monthly Budget'!$N$16:$N,'Monthly Budget'!$Q$16:$Q},2, FALSE),"")))="Income",
      IF(D7<=C7, 
        SPARKLINE({if(isblank(D7),0,D7),C7-D7},{"charttype","bar";"max",C7;"color1","#2e86de";"color2","#de4c60"}),
        SPARKLINE(D7,{"charttype","bar";"max",D7;"color1","#69c569"})),
    IFERROR(1/0))))

Took a little longer than expected but I think it will work.
My testing was limited since I don’t use these sheets so let me know if they aren’t behaving.

Cheers,
Randy

1 Like
4

Thanks Randy! Works great!

5

Did the function of the progress bars change recently? I have some expense items where my Savings is over my monthly budget. I feel like in the past that would be a solid green progress bar. When I had negative savings I would see a red bar. Now, I only see a bar appear when I have an Actual, except in Income categories where bars appear right away…except for one category that has no bar at all??

I refreshed the sheet and it is still like this.

6

We have not changed this template in a while.

7

Thanks Randy. I guess it was just because I was starting a new month with no actuals.