Percentage of spend down

I would like to be able to see on my monthly budgets page, a percentage column that tells me how much is remaining in each category. Essentially the available column divided by the budget column. But adding a column has thrown off other hidden formulas in the past.

Similarly, I would like to be able to see what percentage of the yearly budget is remaining for each category. While at the same time, being able to compare it to the budgeted spend down by month. So rather than just seeing you have X amount available, you would be able to tell that if you had followed each monthly budget closely you would have Y amount available.

Does anyone have a sheet like this or able to help me develop one?

1 Like

Hi @ben.oas - I notice that the Monthly Budget template has a column I which is kind of a spacer column and therefore would be free and available to enter a % Remain column like you’re looking for. Here’s an example formula that you could insert into cell I14:

={"% REMAIN";ARRAYFORMULA(IF(H15:H150<>"",IF(F15:F150=0,"-",FIXED(H15:H150/F15:F150*100,0)),""))}

If a line item has a zero budget which I assume to be something that is not being actively budgeted, it places a “-” there. You could change that to whatever you wanted including an empty string. If you have more than 150 rows, you would also need to adjust the ranges to have no ending row reference or a reference greater than 150.

Another thing I considered was two conditional formatting rules to make the values stand out as green for under budget and red for over budget:

CleanShot 2024-03-18 at 14.04.04

Something else that you could think about would be to normalize the data so that something over budget on the expense side goes to a value of 0 remaining, and something exceeding the budget on the income side goes to a value of 100. The updated formula would be:

=LET(remain, ARRAYFORMULA(IF(H15:H150<>"",IF(F15:F150=0,"-",FIXED(H15:H150/F15:F150*100,0)),"")),
{"% REMAIN / USED";ARRAYFORMULA(IF(remain<>"",IF(remain="-","",IF(VALUE(remain)<0,0,IF(VALUE(remain)>100,100,remain))),""))})

By inserting one more column, you could then add a sparkline to make things more visual using this formula:


An example result would look something like this screenshot:

For the Yearly Budget template, I believe that you could do a similar thing at least for the Yearly % Remain. A good place to enter a column should be between D and E. Then in cell E6, you could enter a similar formula:

={"% REMAIN";ARRAYFORMULA(IF(D7:D150<>"",IF(B7:B150=0,"-",FIXED(D7:D150/B7:B150*100,0)),""))}

I’m not completely sure about what you would like to see for the last part of your description. I think you’re looking to see the monthly % Remain values on the Yearly Budget sheet. This could be done by inserting a 4th column into each monthly section on that sheet, but I’m not sure whether it will mess with the stability of the existing formulas. Perhaps a safer move might be to create a 12-column grid beside the new ColE that was created and enter similar formulas to calculate the % Remain for each month. Then you could perhaps view a trend more easily across that compact grid. I could try to offer more help if that seems of interest and if you need any help.

1 Like

Hi @ben.oas I’d be happy to help you with this if you’d like some customized help 1:1. I am listed in the Tiller Financials Professionals directory if you’d like to reach out: Caroleen Verly, Untangle Your Money, LLC - Tiller