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:
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:
={"Sparkline";MAP(J15:J150,ARRAYFORMULA(100-(J15:J150)),LAMBDA(range1,range2,IF(range1<>"",SPARKLINE({VALUE(range2),VALUE(range1)},{"charttype","bar";"color1","#EE5253";"color2","#69C569"}),"")))}
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.
