Formula required on which week value surpass the requirement.

[premjeetgupta]

Hello,
I need assistance with a formula to determine which week values exceed the requirement.
I have attached an excel file for reference; formula help is required for column Y, highlighted in orange.

pls help.

 

[p45cal]

In a cell in row 3:

=IF($X3>0,"Not Required",XLOOKUP(-$X3,SCAN(0,$A3:$W3,LAMBDA(a,b,a+b)),$A$2:$W$2,"Need to order",1))

In another cell in row 3:

=IFERROR(IF($X3<0,SUM(OFFSET($A3,0,0,,XMATCH(-$X3,SCAN(0,$A3:$W3,LAMBDA(a,b,a+b)),1))),""),"")

See attached in columns AB & AC.

 

[premjeetgupta]

In a cell in row 3:

=IF($X3>0,"Not Required",XLOOKUP(-$X3,SCAN(0,$A3:$W3,LAMBDA(a,b,a+b)),$A$2:$W$2,"Need to order",1))

In another cell in row 3:

=IFERROR(IF($X3<0,SUM(OFFSET($A3,0,0,,XMATCH(-$X3,SCAN(0,$A3:$W3,LAMBDA(a,b,a+b)),1))),""),"")

See attached in columns AB & AC.

Hi @p45cal I sincerely appreciate your time.
This formula is not working when I try to apply it to my original sheet. I've got to comprehend the Lambda formula. In order for me to use this formula in my original reference workbook, could you kindly explain how it functions at least how to use this part - LAMBDA(a,b,a+b)

 

[p45cal]

The lambda goes with SCAN.
Put this in a cell in row 3:
=SCAN(0,$A3:$W3,LAMBDA(a,b,a+b))
and you'll get cumulative totals of A3:W3.
That's the basis, then a lookup (XLOOKUP) is used to find the first value greater than your (negative) target value.

 

[premjeetgupta]

Thank you, @p45cal.
I find it difficult to understand this formula.
Can you help with non-dynamic array formula? Thanks.

 

[p45cal]

If you are finding it difficult to follow Scan/Lambda then I'm not sure you'll find this any easier.
=IF($X3>0,"Not Required",XLOOKUP(-$X3,SUBTOTAL(9,OFFSET($A3,,,,COLUMN($A3:$W3))),$A$2:$W$2,"Need to order",1))
If you're using it in older versions of Excel you might need to array-enter this formula with Ctrl+Shift+Enter, rather than the usual plain Enter.

Edit: This formula still uses XLOOKUP so it won't work in older versions. I couldn't easily find a way to give the correct result withou XLOOKUP. Why don't you try to get your head round Scan/Lambda - it's easier in the long run.

Edit2: try:
=IF($X3>0,"Not Required",IFERROR(INDEX($A$2:$W$2,MATCH(TRUE,SUBTOTAL(9,OFFSET($A3,,,,COLUMN($A3:$W3)))>=-X3,0)),"Need to order"))
Array-enter if necessary.