Adding Percentages Column to Savings Budget

Google Sheets
1

Hi. I’m looking for a way to add a Percentage column to the Savings Budget sheet. That is, I’d like each cell to hold the actual spending in that category as a percentage of actual income. I tried what seemed to be the simplest solution: add a column with a simple percentage formula in it, but the numbers it was generating in the cells weren’t right. Any Google Sheets/Savings Budget wizards have any suggestions on how to do this? Thanks.

1 Like
2

I too like to see percentage and would like to see it across the board, especially in the yearly and monthly budgets.

3

Could you share the formula you attempted?

4

Quite literally the simplest possible:

(Category Spend / Total Spend) * 100

Where Category Spend and Total Spend were simply the cell locations in the Savings Budget:

For example: (F25/F12)*100

5

Maybe something like this?

Insert a column after the ACTUAL column F.
Column G becomes the PERCENT column.

This is with sample data and the income section isn’t very interesting for testing the formula in that section.

image
image632×747 71.3 KB

Cell G10 formula:

={"PERCENT";
 LET(incomeTotal, INDEX(B10:F,XMATCH("INCOME",B10:B),XMATCH("ACTUAL",B10:F10)),
     expenseTotal, INDEX(B10:F,XMATCH("EXPENSE",B10:B),XMATCH("ACTUAL",B10:F10)),
  ARRAYFORMULA(IFERROR(
    IF((P11:P="Income") + (O11:O="Income") + ((O11:O="Type") * (B11:B="INCOME")),
      F11:F/incomeTotal,
      IF((P11:P="Expense") + (O11:O="Expense") + ((O11:O="Type") * (B11:B="EXPENSE")),
        F11:F/expenseTotal,
      IFERROR(1/0))))
  )
)}

Instead of multiply by 100, use the percent number format for the entire column.

2 Likes
6

Thanks for this, @Mark.S. Out camping for the day and night, but I’ll give this a shot when I get home tomorrow. Appreciate you taking the time.

7

Brilliant! It worked. Thanks so much.

1 Like
8

Hi, @Mark.S. If you have a moment, could I ask you to recommend what I hope is a slight modification? I realized that what I really want is category budget as a percentage of actual income, not actual spending. In other words, Column E as a percentage of F12 in the example above. I wish I understood the formula you provided well enough to do it myself, but I can’t seem to figure it out. Any suggestion? Thanks.

9

Sure thing. Since you only care about percent of total income and the fact that that value will always be in cell F12, that simplifies the formula.

I think the cell G10 formula could be:

={"PERCENT";ARRAYFORMULA(IFERROR(IF(ISNUMBER(E11:E),E11:E/$F$12,)))}

where the $F$12 absolute reference locks every calculation to that one cell.

4 Likes
10

Perfect. Thanks, again. This is the chef’s kiss on top of my system.

3 Likes
11

Is there any way to add a column like this to the live profit and loss sheet? It would be helpful if it generated an extra column like this, margined to income. So it would be the expenses margined to that….

12

That should maybe be a separate topic :slight_smile:

1 Like