Converting formula to use spill method

[Ludwig]

I have the following formula which works correctly. Cells it refers to are actually generated by a spill formula, so I'd like to have this one work with "spill" as well. I'm doing it so I don't need to add a new date row to the table, but rather have the results automatically spilled down the worksheet.

=IFS( $AA5 = "", "",
OFFSET( $AA5, 0, 1, 1, 1 ) = "", "no data yet",
TRUE, ROUND( 0 - SUM( FILTER(tbTransactions[Amount], ( YEAR( $AA5 ) =  YEAR( tbTransactions[Date] ) ) *
                                                     (MONTH( $AA5 ) = MONTH( tbTransactions[Date] ) ) *
                                                     (tbTransactions[Subcategory] = $H$2 & " Electricity"), 0 ) )
                 + IF( $AK5 = "Yes", 0,  $AE5 )+ N( "add calc'd home est if not included already" ),
             2 )
)

Where $AA5 is the "spill" version showing the required months (ddd-yy format), and $AK5 determines if the $s shown in $AE5 are in the tbTransactions table & if not then add them in within the formula.
$AK5 and $AE5 are also spill formulas which spill down for as many rows as there are in the $AA5 spill result.


I converted it, as I have done to others, by adding what I thought are the necessary "#"s on the end of the relevant cell references into the following:

=IFS( $AA5# = "", "",
OFFSET( $AA5#, 0, 1, 1, 1 ) = "", "no data yet",
TRUE, ROUND( 0 - SUM( FILTER(tbTransactions[Amount], ( YEAR( $AA5# ) =  YEAR( tbTransactions[Date] ) ) *
                                                     (MONTH( $AA5# ) = MONTH( tbTransactions[Date] ) ) *
                                                     (tbTransactions[Subcategory] = $H$2 & " Electricity"), 0 ) )
                 + IF( $AK5# = "Yes", 0,  $AE5# )+ N( "add calc'd home est if not included already" ),
             2 )
)

This process has worked for other columns that now spill correctly, but for this one it results in "#N/A" instead of the relevant dollar total. Even if I omit the first if test parameters [$AA5# = "", "",], it still fails.

What do I need to do to it to get it to spill with the correct values? Or is it just not possible?

PS I have another spill formula that fails (in this case, with wrong result ) but if I can sort he one posted here I might then be able to resolve this other one.

 

[StephenCrump]

The problem is with the FILTER function, which you can get around by using BYROW. Try:

=IF(AA5#="","",IF(AB5#="","no data yet",BYROW(AA5#,LAMBDA(r,($AK5<>"Yes")*$AE5-ROUND(SUM(FILTER(tbTransactions[Amount],(EOMONTH(r,0)=EOMONTH(--tbTransactions[Date],0))*(tbTransactions[SubCategory]=$H$2&" Electricity"))),0)))))

(I have assumed AB5# is the same size as AA5#)

 

[Eric W]

Here's another way that avoids the FILTER:

Book1
	H													U	V	W	X	Y	Z	AA	AB			AE						AK	AL	AM	AN
1
2	abc
3															AA	AB	AE	AK		Date	Data?			Amount						Flag
4
5															1/1/2024	x	100	Yes		1/1/2024	x			100						Yes		-3	-3
6															2/2/2024	x	200	No		2/2/2024	x			200						No		196	196
7																	300							300
8															5/5/2024		400	Yes		5/5/2024				400						Yes		No data yet	No data yet
9															10/15/2024	x	500	No		10/15/2024	x			500						No		475	475
10
11
12															Date	Amount	Subcategory
13															1/1/2024	1	abc Electricity
14															1/5/2024	2	abc Electricity
15															1/31/2024	3	xyz Electricity
16															2/2/2024	4	abc Electricity
17															5/10/2024	5	abc Electricity
18															5/12/2024	6	xyz Electricity
19															10/1/2024	7	abc Electricity
20															10/2/2024	8	abc Electricity
21															10/3/2024	9	xyz Electricity
22															10/30/2024	10	abc Electricity
Sheet1

Cell Formulas
Range	Formula
AA5:AB9	AA5	=IF(V5:V9="","",V5:V9)
AE5:AE9	AE5	=X5:X9
AK5:AK9	AK5	=IF(Y5:Y9="","",Y5:Y9)
AM5:AM9	AM5	=IFS(AA5#="","", AB5#="","No data yet", 1,ROUND(IF(AK5#="Yes",0,AE5#)- SUMIFS(tbTransactions[Amount], tbTransactions[Date],">"&EOMONTH(AA5#+0,-1), tbTransactions[Date],"<="&EOMONTH(AA5#+0,0), tbTransactions[Subcategory],H2&" Electricity"),2))
AN5:AN9	AN5	=IFS(AA5#="","", COUNTIF(OFFSET(AA5#,SEQUENCE(ROWS(AA5#),,0),1,1,1),""),"No data yet", 1,ROUND(IF(AK5#="Yes",0,AE5#)- SUMIFS(tbTransactions[Amount], tbTransactions[Date],">"&EOMONTH(AA5#+0,-1), tbTransactions[Date],"<="&EOMONTH(AA5#+0,0), tbTransactions[Subcategory],H2&" Electricity"),2))
Dynamic array formulas.

Like Stephen, I also assumed that column AB is a Spill formula, and the AM5 formula takes that into account. But you didn't say column AB is a Spill formula. If it isn't, then the AN5 formula can handle that. The COUNTIF(OFFSET structure is a bit awkward, but it works.

 

[StephenCrump]

The COUNTIF(OFFSET structure is a bit awkward, but it works.

.. as would: OFFSET(AA5#,,1)=""

 

[Eric W]

.. as would: OFFSET(AA5#,,1)=""

Ugh! I tried several versions of that and didn't get it to work. That's much better. Here's the updated formula:

=IFS(AA5#="","",
       OFFSET(AA5#,,1)="","No data yet",
       1,ROUND(IF(AK5#="Yes",0,AE5#)-
                         SUMIFS(tbTransactions[Amount],
                                        tbTransactions[Date],">"&EOMONTH(AA5#+0,-1),
                                        tbTransactions[Date],"<="&EOMONTH(AA5#+0,0),
                                        tbTransactions[Subcategory],H2&" Electricity"),2))

 

[Ludwig]

Thanks for the solution I won't get to try it out until later in the weekend. Will post back when done.

 

[Ludwig]

Brilliant!! Thanks for the formula, it's working as required now The use of EOMONTH and using the "filtering" ability of SUMIFS seems to have been the key.

The original formula was built up over time, and I should have realised the messy SUM (.. FILTER( ...) ) was silly. I can see now what needs fixing in the other formula - replace SUM(.. FILTER ... ) ). Don't know why I ever used that construct!

Thanks again for all your help, much appreciated.

 

[Eric W]

Glad we could help!