Need Help with Update to Budget Plan

Google Sheets
21

Thanks for sharing, I haven’t considered adding a Split column!

1 Like
22

I found another column that could use a custom array for the VLOOKUP so that it can handle column inserts: AU3 (1st Occurrence) should be changed to:

={"1st Occurance";MAP(
   AF4:AF,
   LAMBDA(
      description,
      IFERROR(IF(
         ISTEXT(description),
         IF(
            VLOOKUP(description,{B4:B,F4:F},2,FALSE)<>"",
            IF(VLOOKUP(description,{B4:B,F4:F},2,FALSE)>=M2,VLOOKUP(description,{B4:B,F4:F},2,FALSE),EDATE(VLOOKUP(description,{B4:B,F4:F},2,FALSE),12)),
            $M$2
         ),
         IFERROR(1/0)
      ),"")
   )
)}

I haven’t been able to figure out a more resilient formula for AV3 (Occurrence). I thought I was close with the following, but I couldn’t figure out how to turn it into an ARRAYFORMULA :confounded::

=IF(AU4<> OFFSET(AU4,-1,0),1,offset(AU4,-1,1)+1)
1 Like
23

Nice catch @brettanicus , I’m also not sure about what an arrayformula would look like there.

24

Another idea to help speed up processing is using a single array formula on the Categories sheet rather than pasting a formula in each of the cells, e.g.:

=LET(
  categories_,OFFSET(A1,1,MATCH("Category",A1:1,0)-1,200,1),
  months_,OFFSET(A1,0,COLUMN()-1,1,COLUMNS(A1:1)-COLUMN()+1),
  MAP(categories_,LAMBDA(cat_,
    IF(ISBLANK(cat_),IFERROR(1/0),
      MAP(months_,LAMBDA(month_,
        IFERROR(SUMIF('Budget Plan'!E4:E203,cat_,OFFSET('Budget Plan'!L4:L203,0,MATCH(DATEVALUE(month_),'Budget Plan'!M3:X3,0))),0)))))))

The instructions would be to delete all the $ values in your Categories sheet and paste the above in row 2 below the first month.

1 Like
25

I just tried it and it works great. I may update the documentation for all versions of Budget Plan to use this as it’s easier to setup, and if the performance is better, bonus!

1 Like
26

While setting up my Budget Plan, I used blank rows to separate my budget items into groups and to leave space for additional items. This resulted in $0 totals in rows M-X for the last # budget items, where # was the number of blank rows. The problem is the formula in cell AF3, which generates the raw list of budget item descriptions. The height of the array of descriptions (in the OFFSET formula) is determined by COUNTA(B4:B). Since COUNTA doesn’t count blanks, the height of the array will be short by the number of blank cells in B4:B. Switching from OFFSET + COUNTA to FILTER ought to fix it:

={"Description"; ARRAYFORMULA(
   TRIM(
      TRANSPOSE(
         SPLIT(
            QUERY(
               IFERROR(REPT(
                  FILTER(B4:B,B4:B<>"")&"♥",
                  FILTER(J4:J,B4:B<>"")
               )),
               ,
               999^99
            ), 
            "♥"
         )
      )
   )
)}
27

To allow the sheet to be sorted using the dropdown menus in the column headers, cut and paste all the external references into the top 3 rows, like so:

image
image1920×169 14.5 KB

(And unmerge the cells in column AE.)

Not pretty, but it’s in the hidden area, so maybe it doesn’t matter.

28

Two caveats, which may be dealbreakers:

  1. If the categories sheet is sorted, the array formula won’t work anymore because it will move with its row.
  2. I think this formula may end up being slower! Still testing, but it seems the nested MAPs are significantly slower than filling the array with individual formulas.

Apologies for the wrong turn!

29

I appreciate the update, I won’t rush to make any changes to the template! Glad to hear anything you find out. This template is a performance hog, so any opportunities to speed it up would benefit everyone!