Update Arrayformula in 'Monthly Budget' and 'Monthly Analysis' to Accomodate Additional "Types"

Get Help Ask Anything
#1

I’d like to track money I move to “Savings” as a separate ‘Type’ from “Income” and “Expense.” When I add this new “Savings” ‘Type’ the table created by the array Formula in B16 on the ‘Monthly Budget’ and A30 in the ‘Monthly Analysis’ isn’t quite working. Could you please help with the following problem areas:

  1. ‘Monthly Analysis’ A30 Arrayformula returns “#N/A” for the savings ‘Group’.

  2. ‘Monthly Budget’ B16 Arrayformula returns Income and Savings ‘Categories’ under the same ‘Savings’ type.

  3. Additionally, can you shed light on what’s generating the formatting for the table generated by the arrayformula (i.e. black ‘Type’, grey ‘Group’, and white ‘Categories’)?

#2

Hi @ANBreen,

You may want to check out the Savings Budget dashboard from Tiller Money Labs. It allows you to track savings and has a separate “Track” column in Categories for “Savings”

The Labs team has on deck at some point to update the Monthly Analysis sheet to also show “Savings” from the Savings Budget workflows.

#3

Thanks Heather, the Savings Budget template isn’t quite what I’m looking for. I’d like to track savings as a separate type expense from income and expenses. Effectively, I label transfers from checking to savings as savings, and from checking to retirement or college accounts as savings - I’d like to capture that under its own header with the array formula, and not grouped under the expense header.

#4

Hi @ANBreen,
I can answer part of your questions.

  1. ‘Monthly Budget’ B16 Arrayformula returns Income and Savings ‘Categories’ under the same ‘Savings’ type.

I don’t believe the Monthly Budget sheet is set-up to handle Types other than Income and Expense. It will break with additional types.

  1. Additionally, can you shed light on what’s generating the formatting for the table generated by the arrayformula (i.e. black ‘Type’, grey ‘Group’, and white ‘Categories’)?

Select the empty box above row 1 and to the left of column A in the Monthly Budget sheet to select the entire sheet, then select Format -> Conditionally formatting. You will then see the conditional format rules for the entire sheet.

From there, you can inspect the individual formatting rules that generate the black, grey and white formatting. They make use of custom formulas and NOT() and ISNA() and MATCH() functions. If the custom formula is TRUE, the custom format is applied to the the row.

Jon

#5

Thanks Jon,

Thank you for the pointers on the formatting. I’ll continue to play with the array formulas in the category layout columns to allow additional types.

Appreciate the prompt feedback!