Valores médios no excel com símbolo

Question

Eu tenho colunas de dados analíticos de laboratório no Excel, algumas células contêm o < "<" símbolo e alguns não. Preciso de uma fórmula que diga ao excel para calcular a média das colunas, mas para usar 1/2 o valor para as células que contêm o < símbolo. Alguma ideia? insira a descrição da imagem aqui

    

Answer 1

Se você quiser fazer sem a coluna auxiliar, a fórmula a seguir fornecerá a média da coluna A:

=SUMPRODUCT(--SUBSTITUTE(A1:A12,"<","")*(1/2+1/2*(LEFT(A1:A12,1)<>"<")))/COUNTA(A1:A12)

    

Answer 2

Primeiro, aqui está uma fórmula para normalizar os valores, porque qualquer valor que comece com '<' será considerado um valor de string, que o Excel não pode calcular ou fazer qualquer outra operação matemática.

Crie um intervalo de segmentação, com o mesmo número de linhas e colunas que o intervalo de dados de origem; Seu exemplo tem uma matriz de 12 linhas por 4 colunas, então o destino também será 12x4. Coloque essa fórmula em cada célula do intervalo de destino, de modo que essa operação seja executada em todas as células do intervalo de destino, de A1 a D12:

= SE (ISNUMBER (SEARCH ("<", A1 )), DIREITA ( A1 , LEN ( A1 ) - 1 ) / 2, A1 )

irá olhar para a célula A1.

Se o seu valor começar com '<' então ele vai retirar o '<' e dividir o valor numérico restante por 2.

Se o seu valor não começar com '<', essa fórmula retornará seu valor original.

Uma vez que você tenha uma matriz de valores exclusivamente numéricos, abaixo da linha 12, calcule a média de cada coluna com = MÉDIA ( X 1: X 12) onde X é a designação da coluna. Aqui está uma captura de tela do resultado:

    

Answer 3

Isto é um pouco desajeitado e assume que você não se importa com uma folha extra em sua pasta de trabalho. Se sua tabela estiver em A1:D4 on Sheet1

Em Sheet2 , use a seguinte fórmula:

=IF(LEFT(Sheet1!A1,1)="<",RIGHT(Sheet1!A1,LEN(Sheet1!A1)-1)/2,Sheet1!A1)

Em seguida, copie para baixo e para corresponder às dimensões da tabela em Sheet1 .

Volte no Sheet1 , use =AVERAGE(Sheet2!A1:A12) e copie para cada coluna

( Sheet2 pode estar oculto, se necessário, não afetará as fórmulas)