Is it possible to adjust the % calculation on the “Monthly Budget” tab of the foundations template? Currently the template will display the % of your total budget allocated for each expense sub-category, but I’d like to have this calculation based on actual expenses to see how close I am to 50/30/20 goals.
Hi @rankine.3, I think I have a solution for you. I haven’t tested it much, so let me know if it works.
Replace the formula in cell D16 with the following:
=ARRAYFORMULA(if((row(B16:B)-Row(B$16)<max($N$16:$N)) + isna(match(row(B16:B)-Row(B$16), $O$16:$O,0)),iferror(1/0),iferror(sumif($T16:$T&$U16:$U,if(row($G16:$G)-row($G$16)>=max($N$16:$N),“Expense”,“Income”)&B16:B,$W$16:$W)/sumif($T$16:$T,“Expense”,$W$16:$W))))
And replace the formula in E16 with:
=ARRAYFORMULA(if(ISBLANK(D16:D),iferror(1/0),“of actuals”))
You can change that “of actuals” to say whatever makes most sense to you. Hope this helps!
2 Likes
Hey Matt, thanks so much for the suggestion here!
These formulas are a bit above my skill level but I see what you were going for moving the current equation over to be based on actual spending. For some reason, I got an error on both of these cells using your recommendation.
Although, you pointing out that the table was pulling from D16 and E16 was enough for me to find a fix. I removed both of those equations and just put a basic division problem in column D for each category of spending (necessity, discretionary, and savings) over total expenses. This got me the numbers I was looking for without disrupting any other formulas from what I can tell.
@rankine.3 glad you found a solution! I think I was able to duplicate the error you were seeing. When you copy and paste the formula, for some reason Google Sheets doesn’t like the way the quotation marks (") are formatted. If you’re interested in trying my formulas again I think I fixed it with the formatting below:
=ARRAYFORMULA(if((row(B16:B)-Row(B$16)<max($N$16:$N)) + isna(match(row(B16:B)-Row(B$16), $O$16:$O,0)),iferror(1/0),iferror(sumif($T16:$T&$U16:$U,if(row($G16:$G)-row($G$16)>=max($N$16:$N),"Expense","Income")&B16:B,$W$16:$W)/sumif($T$16:$T,"Expense",$W$16:$W))))
=ARRAYFORMULA(if(ISBLANK(D16:D),iferror(1/0),"of actuals"))
If that doesn’t work, after copying and pasting the formula into the cell just go in and manually delete and replace the quotation marks.
If you don’t feel the need to try these out again though, no problem! Glad you were able to make it work for you.
1 Like
@matt Thank you for the formulas. I was trying to do the same thing this evening and stumbled upon this thread. Worked exactly as intended.
1 Like
Randy recently did a write up on this 
@heather thanks! It’s one of those small issues that is easy to fix but difficult to diagnose.
1 Like
I was inspired by this, and made one for the Savings Budget sheet as well. You can do Budget or Actual.