How to calculate expected numbers after expected lead time

Question

I have a table with different product and dates. Each product has an expected lead time from the time that it gets produced to the time that it gets approved for sale. I would like to capture the expected "approved" items by day, something like in the following table.

day	product	produced quantity	Expected approval Lead time (days)	approved quantity
1	A	20	2	0
2	A	22	2	0
3	A	23	1	20
4	A	19	1	45
5	A	15	1	19
next period	A	-	-	15
1	B	5	1	0
2	B	3	1	5
3	B	3	2	3
4	B	12	1	0
5	B	8	2	15
next period	B	-	-	8

I tried looking for something similar here in stackoverflow and online, but could not find anything.

Answer 1

You need a helper column for that. The helper column calculates the day of approval using

=IFERROR(D:D+A:A,"-")

Then you can calculate the approved quantity using

=IF(ISNUMBER(A:A),SUMIFS(C:C,B:B,B:B,E:E,A:A),SUMIFS(C:C,B:B,B:B,E:E,">"&MAXIFS(A:A,B:B,B:B)))

In Office 365 this can be translated to the following:

in E2 use:

=IFERROR(A2:A13+D2:D13,"-")

and in F2 use:

=LET(a,A2:A13,b,B2:B13,c,C2:C13,d,D2:D13,e,E2:E13,IF(ISNUMBER(e),SUMIFS(c,b,b,e,a),SUMIFS(c,b,b,e,">"&MAXIFS(a,b,b))))

Answer 2

This could also be used:

=LET( range,    A2:D13,
      product,  INDEX(range,,2),   day,  INDEX(range,,1),   lead,  INDEX(range,,4),   prod,  INDEX(range,,3),
      finished, IFERROR(day+lead,0),
      p,        TOROW(product)=product,
      q,        IFERROR(--prod,0),
      qty,      MMULT(p*(TOROW(finished)=day),q),

IF(ISERROR(--day),
   MMULT(--p,q)-MMULT(--p,qty),
   qty))

finished sums the day + lead time.

qty sums the produced qty q for finished matching the day day.

because you used text in the day column, it'll never match a date. I therefore built an error-checker to check day being a value. If it errors, it sums all produced qty for given product minus the sum of the qty for given product (remainder). If it doesn't error it will give the result of qty.

Answer 3

Here is a VBA solution although it's probably pointless since Peh already provided one that works with built in functions.

Simply select the worksheet with your data and run the macro. If your example is representative, it should work. I assumed all items are approved at the end of the period, regardless of how many days their approval takes.

Sub approvedItems()
    Dim arr() As Variant, lr As Integer, rng As Range
    
    With ActiveSheet
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
    End With
    
    Set rng = Range("A2:E" & lr)
    arr = rng
    
    Dim periodCount As Integer
    periodCount = UBound(arr) / 6
    
    For k = 1 To UBound(arr)
        arr(k, 5) = 0
    Next k
    
    Dim rowNo As Integer
    For j = 1 To periodCount
        For i = j * 6 - 5 To j * 6 - 1
            rowNo = i + arr(i, 4)
            If rowNo > j * 6 Then rowNo = j * 6
            arr(rowNo, 5) = arr(rowNo, 5) + arr(i, 3)
        Next i
    Next j

    
    rng = arr
End Sub