Spending trends issue in Excel

Microsoft Excel
1

I am new to Tiller and am having trouble with the Spending Trends worksheet in Excel. No information appears below the “Income and Expense Breakdown” line. Everything above that looks fine. The only error I can see in the sheet occurs in cell AE3 where it reports #CALC!. Other than that it looks as if all my transactions have been loaded into the sheet and have been selected for the period chosen (eg this week or month etc). Any help would be appreciated. Thanks.

2

Cell AE3 depends on the AF Type column. On your Categories sheet, I would check for a category with a missing Type or a Type not equal to Income, Expense, Transfer.

Here’s an example of what it should look like using sample data:

image
image743×426 22.4 KB

Here’s the formula in AE3:

=LET(
  TYPE_COLUMN,AF3#,
  MORE_SPACE,$AA$3,
  ROWS,SEQUENCE(COUNTA(INDEX(TYPE_COLUMN,,1)),1,0),
  SCAN(
    0,
    ROWS,
    LAMBDA(last_index,row,
      LET(
        type,OFFSET(TYPE_COLUMN,row,0,1,1),
        last_type,OFFSET(type,-1,0),
        Group,OFFSET(type,0,1),
        last_group,OFFSET(Group,-1,0),
        last_index+IFS(last_type<>type,IF(MORE_SPACE,IF(row=0,3,6),IF(row=0,2,4)),last_group<>Group,IF(MORE_SPACE,3,2),TRUE,1))
      )
  )
)
3

Thanks Mark,

I really appreciate your taking the time to look at this for me.

I checked the categories sheet and the Types seem to be in order. Also the formula in my AE3 is the same as yours. Unfortunately I’m not yet savvy enough to be able to understand these commands. I’m attaching a screen shot of the part of my spreadsheet analogous to yours. I notice that I have no entries in the columns AC and AD other than the headers. Perhaps this is part of the same problem?

Best,

Paul

Screen Shot 2024-03-28 at 6.16.55 PM.png1100×776 41 KB

4

Sure thing, Paul, sometimes it does require some detective work.

Is this with Microsoft 365? Does hovering over the #CALC! error reveal any additional info?

5

Yes, I am using Microsoft 365, but hovering over the error doesn’t seem to reveal anything. It looks as though my columns AF to AI are fine. I’ll try to understand what is the intended content of the columns AC to AE. This should be become clearer once I’ve got a better feeling for the SCAN and LAMBDA functions in excel.

Thanks again,

Paul

6

Okay, I’ll be interested in hearing what you learn. I think SCAN is a very new function.

Consider using Formulas > Evaluate Formula in your debug.

7

I just started debugging it and get a message that the problem arises because of nested arrays. I’m using excel ver 16.66.1 on a mac running the old Catalina OS 10.15.17. Is it possible that it’s just a matter of updating the operating system and the version of excel?

8

I would run certainly update to latest of both, although I don’t have a mac for testing that myself.