Tenho de duas a seis datas para eventos do passado e, com base no spread médio de um para o outro, preciso prever quando o próximo evento ocorrerá.
Na captura de tela, quero essencialmente tirar a média de ( C4-D4),( D4-E4),( E4-F4) e pular ( F4-G4), pois está em branco. Em seguida, quero adicionar o número médio de dias ao valor mais recente ( C4) para derivar ( A4), a próxima ocorrência prevista.
Quero ter uma fórmula B4que apresente a média de dias e pule o cálculo se uma ou ambas as células estiverem em branco.
Tentei Max-Min/CountIf:
=IFERROR((MAX(C4:G4)-MIN(C4:G4))/COUNTA(C4:G4),"")
Mas sempre surge um número muito baixo, no caso de row 5, 159quando deveria ser 214, e row 6deveria ser 337. Quando tentei usar AVERAGEentre as datas, não consegui dias, consegui a data média.
Responder1
Sua fórmula deve subtrair 1 do denominador, porque são as diferenças que você deseja contar, e não os números reais.
=IFERROR((MAX(C4:G4)-MIN(C4:G4))/(COUNTA(C4:G4)-1),"")
Se você quiser pular a coluna auxiliar:
=IFERROR(MAX(C4:G4) + (MAX(C4:G4)-MIN(C4:G4))/(COUNTA(C4:G4)-1),"")
Você também pode usar o FORCAST:
=FORECAST(0,C4:G4,ROW($1:$5))
Ou mesmo INTERCEPT:
=INTERCEPT(C4:G4,ROW($1:$5))
Esses dois usam a tendência e não a média, portanto, chegarão a um valor diferente se as diferenças variarem muito.
Responder2
A resposta de Scott Craner cobre a tarefa feita na pergunta, prevendo a próxima data com base no intervalo médio. Também sugere uma alternativa de uso de uma tendência. Essa poderia ser uma abordagem melhor ou pior, dependendo do que os dados significam. Esta resposta focará na diferença para que os leitores possam aplicar o tipo adequado de solução.
A pergunta e a resposta de Scott são usadas (Max - Min)/(interval count)para encontrar o intervalo médio. Tudo bem, mas para ilustrar o efeito, calcularei os intervalos e trabalharei com eles, porque isso facilita a visualização em um gráfico. Usarei os dados da linha 6 porque essa é a primeira linha com cinco valores. Então esses dados ficam assim.
O intervalo estimado entre o quinto e o sexto evento, na coluna C, dará a data do evento 6. Se você plotar os intervalos, eles ficarão assim:
O intervalo médio é assim:
A média é a mesma em qualquer ponto, é apenas um valor, neste caso 225.5. Se você adicionar isso à última data, obterá uma próxima ocorrência projetada em 7/13/2019.
Aqui está o problema. Você está registrando um processo que segue um padrão ou algo quase aleatório? Eventos aleatórios não seguem um padrão previsível de altos e baixos a cada evento sucessivo, como dentes de serra. Eles incluem séries de observações na mesma direção. Existem testes estatísticos para avaliar a probabilidade de um padrão se os dados forem realmente aleatórios, mas os cérebros das pessoas estão programados para ver padrões, de modo que os padrões nos dados são frequentemente considerados significativos. Os padrões de dados são como manchas de tinta de Rorschach, as pessoas projetam neles um significado que pode não existir de fato.
Se você estiver investigando padrões, poderá examinar os dados e decidir se deseja testar o que parece ser um padrão. Mas se você espera que os dados sejam aleatórios ou deseja uma estimativa imparcial do próximo evento, não deve começar com a suposição de um padrão. Se você usar cegamente uma linha de tendência, é isso que você está fazendo. Trabalhar com a média nesta situação, conforme proposto na pergunta, é o caminho a percorrer.
Veja este exemplo. Olhando para os dados, seu cérebro tenta convencê-lo de que os dados estão seguindo uma curva. Parece estar a aumentar em geral, embora a curva pareça estar a estabilizar. Então, na ausência de qualquer outra informação, qual seria a melhor forma de ajustar o padrão? Aqui está o que acontece se você projetar o próximo intervalo com base em ajustes sucessivamente de ordem superior.
Um ajuste de primeira ordem é uma linha reta, o que você obtém com uma tendência simples:
Isso percebe os valores como geralmente crescentes e estima que o próximo intervalo será 259.5. Um ajuste de segunda ordem é assim:
Isso vê o último intervalo como um ponto alto e estima que o próximo intervalo será menor 232. Um ajuste de terceira ordem é o mais alto que você pode atingir com quatro intervalos e tem a seguinte aparência:
Uma linha de terceira ordem será perfeita para quatro pontos. Ele encontra vários pontos de inflexão e acaba subindo após o último ponto, estimando 253para o próximo intervalo.
Portanto, dependendo do tipo de linha que você acha que melhor representa o processo subjacente que está gerando o "padrão", o próximo evento pode variar de 7/13/2019a 8/16/2019.
Estender qualquer uma dessas “tendências” para prever o sétimo evento daria resultados ainda mais variados. Esses resultados são com cinco pontos de dados. Mesmo que você acredite que os dados seguem um padrão, não são muitos dados para estimar. Com ainda menos pontos de dados, como acontece com muitas linhas de dados, qualquer forma de estimativa é arriscada. Se você tiver motivos para acreditar que os dados seguem um padrão, e seus dados geralmente se ajustam a esse padrão, usar uma linha de tendência com o formato apropriado (ou seja, tipo de fórmula) provavelmente lhe dará a "melhor" estimativa, mas nesse caso Nesse caso, use um intervalo de confiança em vez de, ou além de, uma estimativa pontual. Isso pelo menos lhe dará uma ideia de quão longe você pode estar.
Tenha em mente que qualquer forma de linha de tendência pressupõe que existe um padrão subjacente e que esse padrão está sendo refletido nos dados. Se realmente existe um padrão, alguns pontos de dados geralmente não são suficientes para estimá-lo. Mas pode não haver nenhum padrão, apenas uma sequência casual de observações. Nesse caso, estimar com base no padrão pode levá-lo a uma direção arbitrária, introduzindo erros substanciais em sua projeção.
Mas também há outra possibilidade. Muitas coisas seguem um ciclo. As observações podem, na verdade, fazer parte de um padrão, mas apenas um pequeno trecho de um padrão. Neste exemplo, essas observações poderiam fazer parte de um ciclo de décadas que se parece com uma onda senoidal. Essas observações podem refletir com precisão a aproximação da crista do ciclo, de modo que o padrão subsequente pode estar descendo em vez de subir (semelhante ao ajuste de segunda ordem, acima). Portanto, mesmo que o padrão seja real, é perigoso extrapolar para fora do intervalo dos dados sem saber algo sobre o processo subjacente ao padrão.