I'll try to explain what its doing for you step by step:
SCAN is a formula which is going to look at each value you specified in a range, and take that value into the LAMBDA formula to be worked on.
SCAN(0, .... [the 0 here is resetting a starter value for 'a' (or accumulator) to zero. We will use this accumulator later to count the number of weeks. We could put '-1' as a starting value - but more on that later.
SCAN(0, B6:K6 ... [B6:K6 is the range that we will look at, cell by cell, left to right. ]
So the first time we enter this SCAN loop, it picks up '500' as your closing stock from B6. It takes that into the LAMBDA formula as a value stored in 'v'.
SCAN(0,B6:K6,LAMBDA(a,v, ... so here, as we go into the LAMBDA formula, we are taking variables 'a' (=0) and 'v' (=500). Then we are going to perform some maths on those values.
SCAN(0,B6:K6,LAMBDA(a,v, a + IF(v>0,1,0))) - so the math we apply is to check if 'v' > 0 eg. do we have a positive Closing Stock left over this week. If yes, then we will add 1 to 'a' If no, we add '0' (or 'a' stays as it was.)
So the result of that formula first time around would be to put a '1' in cell B7. (seen in B10 in the below example picture)
Next, as its a SCAN formula, its going to look at the next cell in your defined range. So this time around, it's Cell C6, and that contains 390.
So its now asking if 390 is greater than 0, and if it is, then add another 1 to 'a'. This new value of 'a' (now = 2) will be put into cell C7.
So it repeats this process until it does all the range (B6 to K6), to give what would normally appear as this : (I have these in row 9 & 10 on my sheet for example purposes only)
| Book1 |
|---|
|
|---|
| A | B | C | D | E | F | G | H | I | J | K |
|---|
| 9 | Closing Stock | 500 | 390 | 270 | 440 | 500 | 350 | 190 | 20 | -160 | -350 |
|---|
| 10 | Forward Cover | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 8 | 8 |
|---|
|
|---|
So, as you look along the row, you can see it has counted 8 weeks before it stops (as yourClosing Stock is now below zero.
Clearly, '1' in cell B10 above is not what you want to see - as it is not 1 week of stock. You want to see the 8 value from the other end, so we use MAX().
=MAX(SCAN(0,B6:K6,LAMBDA(a,v,a+IF(v>0,1,0)))) This just gives us the maximum number from the range of values we just created with the scan/lambda.
=MAX(1 2 3 4 5 6 7 8 8 8) = 8 So, we get a result of 8 put into what would be Cell B7. The rest of the values are ignored, and thats why in the original post, you don't see the intermediate values above - as they've been kind of hidden/removed by the MAX() statement.
So when we copy this formula across the cells, we now get just one result per cell (= weeks of Forward Cover).
BUT, as you can see above, the week before (in cell I9) is a partial only, as it has 20pcs, so we need to subtract 1 from the end result to give us just "7" in Cell B7.
=MAX(SCAN(0,B6:K6,LAMBDA(a,v,a+IF(v>0,1,0))))-1 so I add the '-1' at the end.
(Here, we could have used instead of '0' as our starting accumulator, a "-1", as I mentioned earlier, as end result is the same..)
So here we get the table of results as shown below :
| Book1 |
|---|
|
|---|
| A | B | C | D | E | F | G | H | I | J | K |
|---|
| 9 | Closing Stock | 500 | 390 | 270 | 440 | 500 | 350 | 190 | 20 | -160 | -350 |
|---|
| 10 | Forward Cover | 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 | -1 | -1 |
|---|
|
|---|
But we can see the partial week 8 is at zero, and weeks 9 & 10 now show '-1' as that '-1' is always applied to the result, even if its zero.
So, to tidy up the weeks 9, 10, I simply wrap the entire formula with an IF statement, to check if the result is greater than 0. If yes, then we give the formula result as an output. If its not (ie. result is 0 or less), then we put a '0' result in.
