Sum Until Value is Met with Conditions

[Dorbs]

Hi, I am looking for some help is a problem I have not been able to crack. I am normally pretty good at manipulating formulas from other posts to save asking questions, but this has got me stumped.
I am trying to sum up expected item receipts until the value I need is met/exceeded and return the reference number, or “Not Planned” if the volume I require is greater that the planned receipts. The kicker is that the data contains multiple items from multiple warehouses all mixed together. This data is likely to be refreshed daily and the tables in my example will be built dynamically with Excel365 array formulas.
I am using the formula below but cannot get it to work with any conditions.

=OFFSET($E$2,VALUE((ROWS(OFFSET($E$2,0,0,MAX(INDEX((SUBTOTAL(9,OFFSET($E$2, 0,0,ROW($E$2:$E$13),1))<(E17*-1))*ROW($E$2:$E$13),,))+1,1))-1)),-4)

I am trying to avoid writing a custom function and or using helper columns.
Any help would be greatly appreciated. The expected results are in the grey column in the attached example sheet.

 

[p45cal]

Have a look at column I in the attached. I'm not sure I've got the logic right so this formula isn't fully developed

 

[Dorbs]

This is brilliant, I knew there would be new functions in 365 that would provide a solution, but I could not find them in my search. Sending a subset of the table to be analysed was also a big change to my approach. Thank you for your help, and quick response.

 

[Ajju1234]

Have a look at column I in the attached. I'm not sure I've got the logic right so this formula isn't fully developed

can i ask you something ?
how did you pot right sign in column J

 

[p45cal]

Conditional formatting:

 

[Peter Bartholomew]

This solution is very much work-in-progress and requires tidying up and documenting. My aim (in part achieved) is to create a solution using a named Lambda function that spills from a single cell. An awkward feature of building the accumulations is that several running totals are required. My strategy was to first identify every distinct combination of warehouse code and item and then use REDUCE/VSTACK to analyse each subgroup before combining the solutions. The XLOOKUP between the running totals links each stock ID to a requirement ID. The final XLOOKUPs use the resulting table to order the result by that of the purchase table and also looks up the associated delivery date.

The magenta bordered cell contains the only formula.

 

[Dorbs]

Thanks Peter,

I was just about to ask about how to make the solution into a dynamic array (an afterthought to the original question). I feel this formula takes the array formula almost to the point of programming. I have also not tried a Let formula inside a Let formula. I am learning a lot from it.

 

[Peter Bartholomew]

I would go further and say that the Excel formula can amount to full-on programming (functional programming at that). The challenge is that it is embedded in a traditional spreadsheet environment in which many users are in denial that their data analysis activity is actually programming.

A interesting thing about returning to one's own work after an intervening period of time is that one becomes acutely aware of the need for additional documentation!

 

[Dorbs]

I did notice some notations in the Name Manager, again something I did not realise could be done.

 

[Dorbs]

I would go further and say that the Excel formula can amount to full-on programming (functional programming at that). The challenge is that it is embedded in a traditional spreadsheet environment in which many users are in denial that their data analysis activity is actually programming.

A interesting thing about returning to one's own work after an intervening period of time is that one becomes acutely aware of the need for additional documentation!

Hi Peter, I know I am asking you to go back to this work again. I have been pulled away from this work but am back to it now. I have spent some time working through your solution and understanding it. I have worked through all the functions and got/understand the subList array, but I am struggling with where or what defines "priorList" and "index". Are you able to explain these for me?

Thanks in advance

 

[Peter Bartholomew]

The worksheet formula calls
IdentifyDistinctItemsλ(ItemID, WarehouseID)
to get the distinct pairings of ItemID and WarehouseID. The next step is to run through each such pairing, picking up entries from both the input and output tables by matching them using the cumulative quantities. This is done by calling
MatchIDsλ(distinctItems)(priorList, index)
There are two Lambda functions so the parameters are passed as two strings. distinctItems is just the pairs calculated above but priorList and index are variables defined to accept the parameters passed by REDUCE. index is simply a counter which is used to extract the pairs of values from distinctItems pair at a time and priorList is an accumulation of all the results calculated within REDUCE to that point.

The functions
cumulativeMatchesλ(ReqTbl, distinctItem, 5)
cumulativeMatchesλ(StockTbl, distinctItem, 4)
use SCAN to accumulate the quantity in the chosen column once the dataset is filtered to return only the specific item.

The attached file has the intermediate calculations for distinctItems and stackedList displayed. The remaining steps are relatively straightforward XLOOKUPS and a VSTACK. As I mentioned before, I would consider these formulas to be fairly 'full on' programming.

 

[Dorbs]

The worksheet formula calls
IdentifyDistinctItemsλ(ItemID, WarehouseID)
to get the distinct pairings of ItemID and WarehouseID. The next step is to run through each such pairing, picking up entries from both the input and output tables by matching them using the cumulative quantities. This is done by calling
MatchIDsλ(distinctItems)(priorList, index)
There are two Lambda functions so the parameters are passed as two strings. distinctItems is just the pairs calculated above but priorList and index are variables defined to accept the parameters passed by REDUCE. index is simply a counter which is used to extract the pairs of values from distinctItems pair at a time and priorList is an accumulation of all the results calculated within REDUCE to that point.

The functions
cumulativeMatchesλ(ReqTbl, distinctItem, 5)
cumulativeMatchesλ(StockTbl, distinctItem, 4)
use SCAN to accumulate the quantity in the chosen column once the dataset is filtered to return only the specific item.

The attached file has the intermediate calculations for distinctItems and stackedList displayed. The remaining steps are relatively straightforward XLOOKUPS and a VSTACK. As I mentioned before, I would consider these formulas to be fairly 'full on' programming.

Thank you for taking the time to explain this. I am still getting my head around when a LAMBDA needs variables defined to be populated versus when values are provided to the LAMBDA variables. Are there rules around this to help with syntax and reading functions?

 

[Peter Bartholomew]

The must be references somewhere, but since my learning has been by trial and error, this illustrates my understanding.
Assume we wish to calculate the duration between start and end and convert to hours or minutes.

Anonymous Lambda with formula and parameters
= LAMBDA(x,y,z, (z-y)*x)(conversion, start, end)

Named Lambda definition
Durationλ
= LAMBDA(x,y,z, (z-y)*x)

Named Lambda with parameters
= Durationλ(conversion, start, end)

If you wanted to use a Lambda helper function for some of the parameters then one might split the parameter string by using a second Lambda.

Anonymous Lambda with closure
= LAMBDA(x, LAMBDA(y,z, (z-y) *x))(conversion)(start,end)

Named Lambda definition
Duration2λ
= LAMBDA(x, LAMBDA(y,z, (z-y) *x))

Named Lambda with parameters
= Duration2λ(conversion)(start,end)

Named Lambda with parameters provided by helper function
= MAP(start, end, Duration2λ(conversion))

That should at least give you something to explore.

 

[Dorbs]

Thanks so much Peter. I have not found any information that lays it out this clearly.
I am sure there are times when the double LAMBDA would come into its own, and it would make sense to use MAP, but I find the syntax of the single LAMBDA is so much easier to follow.

 

[Peter Bartholomew]

Yes, the syntax of the single LAMBDA function is much easier to interpret. Yet I believe the original Lambda calculus (due to Alonso Church) was based upon functions in their 'Curried' form (named in honour of Haskell Curry) in which a sequence of functions of one variable are shown to be equivalent to a function of multiple variables. That is

Duration3λ
= LAMBDA(x, LAMBDA(y, LAMBDA(z, (z-y) *x)))

used as
= Duration3λ(conversion)(start)(end)

The key is that the parameters are accepted left to right rather than as they would were each step treated as a nested formula using parentheses.

When using helper functions it might be useful to develop the formula as an anonymous LAMBDA and, only once you have a working formula, to introduce the named Lambda function to 'pretty it up'.

Other 'tricks' include passing a Lambda function as a parameter to another function, returning a function as the result of a calculation and using functions (Thunks) that require a null parameter string to convert them from a function back to an array.