Really Strange Problem with Monthly Analysis Template

[kkinderen]

This seems like real odd behavior to me. It seems the way I have my categories set up breaks the Monthly Analysis template. Let me see if I can explain.

I have the following categories setup:
Category, Group, Type, Hide
Bank Fees, Banking, Expense,
Card Payment, Banking, Transfer, Hide
Interest, Banking, Income,
Investment, Banking, Expense,
Other Income, Banking, Income,
Cash Back, Banking, Income,
Transfers, Banking, Transfer, Hide

This breaks Monthly Analysis:

Notice the cash flow is zero and Other Income looks like a subtotal row and the numbers are wrong.

If I change the categories setup to the following:
Category, Group, Type, Hide
Bank Fees, XYZ, Expense,
Card Payment, Banking, Transfer, Hide
Interest, Banking, Income,
Investment, XYZ, Expense,
Other Income, Banking, Income,
Cash Back, Banking, Income,
Transfers, Banking, Transfer, Hide

Then things start to look normal to me:

Now things look normal and match what I can manually determine from my transactions.

No idea why regrouping Bank Fees and Investment groups fix things. Am I doing my category sheet wrong or is the Monthly Analysis template not working right?

I’d appreciate any help.

[KyleT]

Hi @kkinderen - This issue reminds me of another post that I assisted for the Live P&L report in Excel here. I suspect you have what they might consider an “edge case” where your categories are set-up in such a way that they fall into an arrangement that the formula in cell X7 is having some trouble handling properly. I was able to recreate this with categories set-up in such a way that not only is there a same Group with both Income and Expense categories, but that same Group transitions from Income to Expense in Col Z. The screenshot might help explain what I mean. If you don’t have that type of data layout, it could be something different, but I still think the key may lie within cell X7 because it controls how those categories get distributed down the rows. The reason I speculate this specific case is because the report does work for Groups that have both Income and Expense as long as they are not at that transition line in Col Z.

In that previously linked post I was able to suggest a formula that fixed the issue at hand. I tried reusing that work because, to be honest, those formulas are not for the faint of heart and they take awhile to unravel. Unfortunately, a direct reuse of that fix is not possible here.

By simply changing even just the first Banking group to perhaps “Banking-Inc” for income, the issue should go away. Effectively the same thing you did by changing to XYZ.

Maybe Tiller or someone else inclined to take on the challenge could try to bulletproof that formula for this case.

[KyleT]

I might add that if your data structure in Cols Z-AB is indeed as described previously, another thing you could do if you want to keep your Group name unchanged is to add a placeholder Group to your Categories sheet like “Not Banking” in the following screenshot. This breaks that same Group at the transition issue and should resolve the display. Then you could collapse the row(s) in the report with that placeholder.

[kkinderen]

Hi Kyle. I’m sorry for the delayed response. I’ve decided after a number of issues with Excel that it is just too fragile for me and I’m going back to Google Sheets.

I was really hoping Excel would work out for me because it seems to have better capabilities for creating reports and seems to have a lot better tools. I just keep running into odd problems.

Thanks for your time and detailed explanation.