Converting formula to use spill method

Ludwig

Board Regular
Joined
Apr 7, 2003
Messages
97
Office Version
  1. 365
Platform
  1. Windows
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.

Excel Formula:
=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:

Excel Formula:
=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 :oops:) but if I can sort he one posted here I might then be able to resolve this other one.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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#)
 
Upvote 0
Here's another way that avoids the FILTER:

Book1
HUVWXYZAAABAEAKALAMAN
1
2abc
3AAABAEAKDateData?AmountFlag
4
51/1/2024x100Yes1/1/2024x100Yes-3-3
62/2/2024x200No2/2/2024x200No196196
7300300
85/5/2024400Yes5/5/2024400YesNo data yetNo data yet
910/15/2024x500No10/15/2024x500No475475
10
11
12DateAmountSubcategory
131/1/20241abc Electricity
141/5/20242abc Electricity
151/31/20243xyz Electricity
162/2/20244abc Electricity
175/10/20245abc Electricity
185/12/20246xyz Electricity
1910/1/20247abc Electricity
2010/2/20248abc Electricity
2110/3/20249xyz Electricity
2210/30/202410abc Electricity
Sheet1
Cell Formulas
RangeFormula
AA5:AB9AA5=IF(V5:V9="","",V5:V9)
AE5:AE9AE5=X5:X9
AK5:AK9AK5=IF(Y5:Y9="","",Y5:Y9)
AM5:AM9AM5=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:AN9AN5=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.
 
Upvote 0
.. 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:

Excel 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))
 
Upvote 0
Solution
Thanks for the solution :) I won't get to try it out until later in the weekend. Will post back when done.
 
Upvote 0
Brilliant!! Thanks for the formula, it's working as required now (y) 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.
 
Upvote 0

Forum statistics

Threads
1,224,568
Messages
6,179,591
Members
452,927
Latest member
whitfieldcraig

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top