Suponha que eu tenha uma coluna de 10 números e quero saber qual é a média das somas de cada número com cada outro número (exceto ele mesmo). Portanto, seria a média da soma de, por exemplo, A1 + A2, A1 + A3 ...., A8 + A10, A9 + A10.
Qual é o caminho mais rápido para fazer isso? Meu método atual de consertar uma constante de célula e iterar através de todas as outras células parece incrivelmente tedioso. O outro método melhor que encontrei é criar uma tabela passando por cada uma delas com referências relativas. Existe uma maneira de fazer isso em uma célula embora?
Insira uma linha na parte superior e transponha os números para ela. Em seguida, usando uma declaração IF , você pode criar uma matriz de todas as somas.
Coloque esta fórmula na célula B2 e copie-a para o canto inferior da matriz.
=IF($A2=B$1, "", $A2+B$1)
Os resultados ...
Acredito que isso seja simplesmente (novamente, supondo que os valores estejam em A2: A9):
=2*SUM(A2:A9)/COUNT(A2:A9)
Falando matematicamente, por exemplo:
((a+b)+(a+c)+(a+d)+(b+c)+(b+d)+(c+d))/6
é igual a:
(3a+3b+3c+3d)/6
qual é:
3(a+b+c+d)/6
ou seja:
(a+b+c+d)/2
Atenciosamente
Para obter a média das somas de cada par (exceto cada número consigo mesmo), você pode fazer isso com uma única fórmula se recriar a tabela transposta de CharlieRB dentro da fórmula como esta :
=AVERAGE(IF(ROW(A2:A9)>TRANSPOSE(ROW(A2:A9)),A2:A9+TRANSPOSE(A2:A9)))
confirmado com CTRL + SHIFT + ENTER
Portanto, TRANSPOSE(A2:A9) fornece a coluna transposta e, em seguida, A2:A9+TRANSPOSE(A2:A9) fornece uma matriz da soma de todos os pares possíveis. A função IF exclui "pares invertidos" (por exemplo, exclui A5+A4 porque A4+A5 já está incluído) e os pares que são cada número somado a si mesmo e, em seguida, AVERAGE function fornece o resultado necessário
Se o intervalo for uma linha de dados em vez de uma coluna, será necessário usar 2 x COLUMN functions no lugar das ROW functions
Nota: os pares excluídos são apenas especificamente cada célula com ele mesmo , isso não exclui a possibilidade de dois números iguais serem somados, então se A2 = A7 então você ainda tem o par A2+A7 incluído (mas não A2+A2 ou A7+A7 ou% invertidoA7+A2)