Calcule quando as contagens de volume são compensadas com base no valor diário recebido e no valor diário processado

O backlog no final de qualquer dia específico é a diferença entre o Volume Retornado (RV) cumulativo e o Volume Compensado (CV) cumulativo. O backlog no final de um dia é o backlog para o início do dia seguinte. A discussão abaixo considera pendências no início do dia.

Observe que um backlog nunca pode ser negativo, pois é impossível compensar em um dia qualquer coisa além do backlog daquele dia (início do dia) e seu RV.

Com um regime FIFO (primeiro a entrar, primeiro a sair), qualquer pendência de início de dia diferente de zero sempre conterá alguns itens do dia anterior e poderá conter itens de dias anteriores.

Se no início de hoje o backlog for diferente de zero e não for maior que o RV de ontem, então o item mais antigo do backlog não poderá ser mais antigo que ontem. Se fosse, então o FIFO implicaria que todo o RV de ontem teria que estar atrás dele na fila de pendências no início de hoje, levando à contradição de que o atraso teria que ser maior do que o RV de ontem. Como esse item mais antigo não pode ser de hoje (porque está na fila de pendências no início de hoje), segue-se que ele deve ser de ontem.

Argumentos semelhantes podem ser usados ​​para comparar o tamanho da lista de pendências do início do dia de hoje com a contagem regressiva de RV cumulativa a partir de (mas não incluindo) hoje.

Se o tamanho da lista de pendências no início de hoje for (digamos) b e hoje for o dia número d , então o item mais antigo da lista de pendências originou-se no dia d - k se o RV total entre os dias d - 1 e d - k for maior que ou igual a b enquanto aquele entre d -1 e d - k -1 é menor que b .

A seguinte expressão fornece a solução necessária

=LET(
backlog, SUM(B$2:B2) - SUM(C$2:C2),
d, ROW() - 1,
INDEX(
    A$2:A3,
    d - IF(backlog > 0,
           1 + SUM(N(backlog > 
           SCAN(0, INDEX(B$2:B2,SEQUENCE(1,d-1,d-1,-1)),LAMBDA(a,v,a+v)))),
           0)
      )
)

A fórmula, que deve ser aplicada na terceira linha da planilha e copiada para baixo, pressupõe:

• na definição de dque a LETlinha 1 da planilha contém os cabeçalhos e as datas, RVs e CVs estão nas linhas 2 em diante
• as datas estão na coluna A, RVs em Be CVs emC

Observe que , se estiver agrupado em a LAMBDAcomo no OP, existem 3 intervalos na fórmula A$2:A3e . O primeiro deles possui uma célula/linha adicional em comparação com os outros dois.B$2:B2C$2:C2

Na imagem abaixo, os backlogs são mostrados para fins ilustrativos, mas os valores mostrados na planilha não são utilizados na fórmula.

A fórmula de derramamento (veja meu comentário à minha resposta anterior) é

=LET(
    PD, A2:A10,
    RV, B2:B10,
    CV, C2:C10,
    BL, SCAN(0, VSTACK(0, DROP(RV, -1)), LAMBDA(a, v, a + v)) -
        SCAN(0, VSTACK(0, DROP(CV, -1)), LAMBDA(a, v, a + v)),
    seq, SEQUENCE(COUNT(PD) - 1, 1, 2, 1),
    MAP(seq,
        LAMBDA(d,
            INDEX(PD, d -
                IF(INDEX(BL, d) = 0, 0,
                    1 + SUM(N(INDEX(BL, d) >
                            SCAN(0,
                                INDEX(RV,SEQUENCE(1,d - 1, d - 1, -1)),
                                LAMBDA(a, v, a + v)
                            )))
                )
            )
        )
    )
)

Esta fórmula é inserida no segundo dia. (Para o primeiro dia, presume-se que o sistema começa vazio, sem atrasos no início do dia 1.)

Observe que dentro LAMBDAdos MAPretornos LAMBDAefetivos para qualquer dia d a data fornecida por INDEX(PD, d - n). An IFaninhado com the INDEXdetermina se o backlog no início do dia d é zero e, em caso afirmativo, n é definido como zero. Seria possível reverter a ordem INDEXe IFretornar apenas zero (em vez de uma data) em dias sem atrasos - o mesmo se aplica à versão sem derramamento da fórmula na resposta anterior.