# Adding Progress Bars to the Savings Budget

This post from @catpatriciamorgan got me thinking about adding progress bars to the Savings Budget.
So, I gave it a try in my personal spreadsheet…

If you’re interested in implementing this…

1. Insert a new column between `ACTUAL` and `AVAILABLE`. I called it `PROGRESS` in the header.
2. Insert this formula in `F8` and drag/expand it down to the bottom.
``````=IFERROR(
IF(or(N8="Expense",M8="Expense",and(M8="Type",B8="EXPENSE")),
IF(E8+1<=D8+C8,
SPARKLINE({if(isblank(E8),0,E8)},{"charttype","bar";"max",D8+C8;"color1","#2e86de"}),
SPARKLINE({D8+C8,E8-D8-C8},{"charttype","bar";"max",E8;"color1","#69c569";"color2","#de4c60"})),
IF(or(N8="Income",M8="Income",and(M8="Type",B8="INCOME")),
IF(E8<=D8-C8,
SPARKLINE({if(isblank(E8),0,E8),D8-C8-E8},{"charttype","bar";"max",D8-C8;"color1","#2e86de";"color2","#de4c60"}),
SPARKLINE(E8,{"charttype","bar";"max",E8;"color1","#69c569"})),
IFERROR(1/0))))
``````

Let me know if this works for you or if you have any improvements.

@randy

Thank you! This is so great! One thing though is that the formula needs there to be values for each category, but (in the case of the screenshot) there are no values for “Cash” in Budget and Actual because I did not budget any cash for this month. Any way to get the progress bar to be blank in months that I do not budget for cash?

Wrap the whole formula is an iferror( formulas)

2 Likes

@richl
Thank you!! That worked!

1 Like

Good fix, @richl. I made that change in the original solution post.

If people like the progress bars, we can add them to the master. Let me know.

2 Likes

The month kicked over to February and I noticed a bug in the Income section when rollovers are present. I have updated the formula in the original post. I recommend you update your sheets.

Please let me know if further changes are needed.

Hey everyone! I just wrote the progress bars into the Savings Budget master so now this feature is readily available to everyone.

I also added some new top-level sparkline headers similar to the ones in the Monthly Budget.

These changes are noted in a new Tiller Money Labs Roundup.

Best,
Randy

1 Like

Loving the updates, @randy! One thing I am noticing, however, is that the sparklines don’t appear to respect hidden categories, which is limiting their usefulness to me. I’ll take a look into it if I get a chance, but I wanted to throw that out there in case anyone else was running into the same issue.

Good catch, @cculber2. (I don’t use many hidden categories so I didn’t notice this.)
I think it is a relatively easy fix. Just need to touchup a few formulas in `J11:K28`.
I’ll add it to my list.

It’s kind of a fussy problem, @cculber2, because that `J11:K28` data range feeds both the sparklines and also the budget health calculations in `J1:K3`. (Previously, the section only fed budget health.)

Is it possible that hidden categories should be included in budget health but not the sparklines? (I’m glad, as someone who helped develop the budget health concept, I can ask you this…)

Is it possible that hidden categories should be included in budget health but not the sparklines?

My first thought is “no”, possibly on both counts, as I don’t actually put in budget amounts for those categories. I’m tracking them because they are real transactions, such as my employer’s Roth 403B contribution and personal contribution paycheck deduction, but they have no bearing on my actual budget. Similarly, my property taxes deduction from escrow shows on my mortgage account ledger and is a very real transaction, but it has no bearing on my budget because my escrow is funded by my monthly mortgage payment. There is value in knowing those numbers, but they don’t interact with my budget. Of course, investments and mortgages are probably some of the most complicated situations to consider, so take my thoughts with a grain of salt.

I just updated the Savings Budget master (to version 0.73), @cculber2.

1. I changed six formulas in the `J11:K28` range to respect the `Hide` setting for the header metrics values.
2. I noticed that the main Savings column has had the same issue. I updated the formula in `C12` to check the `Hide` setting when summing Group and Type savings in column `C`.

Let me know if you notice any other issues.

1 Like

Hey @randy, thanks for the update! I applied the new version and we’re getting close, but it looks like there are still a few tweaks necessary. I noticed that the `Actuals, Expense` formula in `K22` and `Actuals, Income` formula in `K23` weren’t using the Hide filtering logic, so I copied that into those `SUMPRODUCT()` formulas.

After I made that change, a more subtle issue was revealed. It seems that because of the way the above formulas are calculated, categories marked as `Debt` do not get summed properly as their `Period Actuals` in `AI11:AI` will always net out to \$0.00. This is creating yet another discrepancy between the sparkline and category rollup values for `Expense Actual`.

Sorry that my contributions seem to always end up pointing out errors. I really do appreciate everything you’re putting into these optimizations.

I did some more investigation, and it seems the solution was far simpler than I was imagining. I was able to update the `Actuals, Expense` formula in `K22` and the `Actuals, Income` formula in `K23` to sum the `Modified Actuals` in `AO11:AO` rather than the `Period Actuals` in `AI11:AI`. Making this change yielded the expected expense and income values in the sparkline.

Actuals, Expense, `K22`

`=sumproduct((\$AE\$11:\$AE="Expense"),(\$AG\$11:\$AG<>"Hide"),\$AO\$11:\$AO)`

Actuals, Income, `K23`

`=sumproduct((\$AE\$11:\$AE="Income"),(\$AG\$11:\$AG<>"Hide"),\$AO\$11:\$AO)`

On a side note, I do not recommend adjusting the formulas for `Actuals Prior to Period` in `P10` and `Actuals in Period` in `S10`, as it throws off the `ACTUAL` calculation in `E12`, and possibly elsewhere.

I also noticed a polarity issue for `Net Budgets` in `K13` for all periods. The formula is set to `Budget, Expense - Budget, Income` whereas the current period calculates it in `K21` as `Budget, Income - Budget, Expense`. I made a simple correction of swapping the terms.

Net Budgets, `K13`

`=K12-K11`

Thanks for digging into this, @cculber2.
The changes make sense.

I implemented them in just-published version 0.74.
Let me know if you find anything else requiring modifications to the master.