excel planilha média com priorização

Question

Preciso de ajuda para escrever fórmulas de excel (ou qualquer solução de excel) para resumir com prioridades.

Eu tenho dados de transação assim:

Date  Unit  Price  Buy/Sell 
7/11   3     10     Buy 
7/12   2      9     Sell
7/13   4      8     Buy 
7/14   3     11     Sell

Eu quero calcular o preço médio de compra para unidades na mão e não para todas as unidades compradas. Eu tinha 2 unidades na mão, me lembro do FIFO, então a média seria de $ 8.

3 unidades a $ 10 e 4 unidades a $ 8 compradas, e as primeiras 5 unidades foram vendidas.

Alguma idéia para resolver este problema no excel?

    

Answer 1

Não há uma maneira simples de fazer isso com funções de planilha, porque você precisa acompanhar uma lista de comprimentos arbitrários de preços de compra, que muda com o tempo.

Supondo que o número de unidades é um número inteiro positivo, posso fazê-lo com uma coluna auxiliar. Digamos que os dados estejam nas colunas A-D e a coluna auxiliar seja E.

Em E2, coloque =IF(D2="Buy",E1&REPT(C2&",",B2),MID(E1,1+FIND("*",SUBSTITUTE(E1,",","*",B2)),32767))

Em seguida, no final, a célula final conterá uma string de texto que você precisa transformar em uma fórmula =AVERAGE() usando um dos métodos em link

Infelizmente, é provável que você encontre o limite de comprimento da célula, que é 32768 caracteres.