Média por mês dentro de vários intervalos de datas sob medida

Question

Eu tenho dados por mês a partir de janeiro de 2013 - > Outubro 2017 como tal

MONTH   MY DATA
Jan-13  45.0
Feb-13  23.0
.   
.   
.   
Oct-17  98.4

Desejo calcular a média dos dados por mês, para períodos específicos e não contíguos. POR EXEMPLO. Janeiro de 2013 - > Março de 2013 e outubro de 2016 - > Maio de 2017.

Acredito que isso pode ser feito usando funções IF com OR, para abranger vários intervalos de datas, e AND, para abranger duas datas dentro de cada intervalo, para retornar uma matriz (ao entrar como uma fórmula de matriz) usando a média na matriz retornada.

Com meus dados em B5: C62 e meus intervalos de datas personalizados configurados como:

Period 1 Start: S2
Period 1 End: T2
Period 2 Start: S3
Period 2 End: T3

E meus meses de calendário em P6: P17, eu entro na minha fórmula como

=AVERAGEIF($B$5:$B$62,P6,IF(OR(AND($B$5:$B$62>=$S$2,$B$5:$B$62<=$T$2),
AND($B$5:$B$62<=$S$3,$B$5:$B$62>=$T$3)),$C$5:$C$62,"ERROR"))

Eu tenho um valor de #VALUE! erro, embora eu não consiga entender por quê. Tanto quanto eu posso dizer a minha cláusula IF deve estar retornando o "average_range" para a fórmula AVERAGEIF para executar em.

Qual é o erro na minha fórmula ou na minha abordagem?

    

Answer 1

Em sua fórmula, a parte IF () não retornará uma matriz quando houver um AND () como parte do teste lógico. Você pode verificar isso usando a ferramenta de depuração interna para fórmulas do Excel: destaque uma parte da fórmula na barra de fórmulas e pressione F9 .

Mas existe uma maneira de calcular a média que você deseja. No Excel, a multiplicação de valores lógicos converte valores de Verdadeiro / Falso em 1 e 0, que podem então ser manipulados posteriormente. Como exemplo, esta declaração

=(A2:A25>=D2)*(A2:A25<=E2)+(A2:A25>=D3)*(A2:A25<=E3)

retorna a matriz

{0;0;0;1;1;1;1;0;0;0;0;0;0;0;0;0;1;1;1;1;1;1;0;0}

dos dados na tabela abaixo. Aqui a multiplicação age como um AND lógico e a adição é quase como um OR lógico. (@ Máté Juhász pode falar aqui para explicar por que não é uma OR: -)

Esta matriz pode ser vista como uma "máscara" da coluna A com 1s correspondente aos dois intervalos de datas especificados pelas datas de início e término.

Agora, uma fórmula IF () pode usar essa matriz para gerar a lista de valores que você deseja avaliar:

IF(((A2:A25>=D2)*(A2:A25<=E2)+(A2:A25>=D3)*(A2:A25<=E3)),B2:B25)

Isto dá o array

{FALSE;FALSE;FALSE;42.9;82.3;90.5;15.6;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;70.8;85.5;19.2;85.4;21.3;55.7;FALSE;FALSE}

que agora pode ser alimentado para AVERAGE ().

Portanto, uma solução para o seu problema é uma fórmula semelhante a esta (matriz), em D6:

=AVERAGE(IF(((A2:A25>=D2)*(A2:A25<=E2)+(A2:A25>=D3)*(A2:A25<=E3)),B2:B25))

Altere os intervalos e os valores de data personalizados para se adequarem à sua situação.

Acélulaabaixode"Cheque" contém apenas um cálculo manual da média usando

=AVERAGE(B5:B8,B18:B23)

Espero que isso ajude e boa sorte.