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.

9

Hey! I have this exact same problem. I’m using Excel for Mac 16.69
The code in my cell AE3 is identical to Mark’s. But my cell shows a #CALC! error.

Excel says:
Error in Cell AE3

Nested Arrays
A value used in the formula is of the wrong data type.

In the associated page it says:
“Excel can’t calculate an array within an array. The nested array error occurs when you try to input an array formula that contains an array. To resolve the error, try removing the second array.”

Any ideas on how to proceed. The Spending trends sheet is the main purpose of Tiller for me! I would like to figure out how to make it work.

Thanks!

10

Hi @MaximumBob have you run all updates for Excel? Had you been using the Spending Trends sheet for some time or it’s never worked from the start?

11

Thanks so much for responding @twalane! I should have come back to report earlier.

Yes, this was fixed by updating Excel. My auto updater was not working and my version was about 1 year out of date. I am still curious why it was failing with the old version, but everything is working now.

2 Likes
12

Glad to hear it @MaximumBob . Somewhere between all the platforms something is tweaked and others have to keep up!