Calcula a média geométrica no Excel

Question

Eu tenho alguns dados de rede de e-mail no Excel como um edgelist, o que significa que tenho colunas Vertex1, Vertex2 e N colunas de propriedades dessa borda, como quantos e-mails foram enviados de uma pessoa para outra. Para cada linha nos dados, Vertex1 é a fonte de uma mensagem e Vertex2 é o destino, portanto, as bordas são direcionadas. Aqui estão alguns dados de amostra

Vertex1 Vertex2 nMessages
Bob     Cindy   12
Cindy   Bob     3
Bob     Mike    11
Cindy   Mike    1

Estou tentando calcular uma média geométrica da forma

gm = sqrt[(# of edges ij)*(# of edges ji)]

Então gm para Bob e Cindy é gm = sqrt [(mensagens de Bob para Cindy) * (mensagens de Cindy para Bob)] ou sqrt (12 * 3) = 6. Existe uma maneira de fazer disso uma fórmula em Excel?

    

Answer 1

Eu posso fazer isso com uma coluna auxiliar. (com o Excel 2007 ou superior, eu não preciso da coluna auxiliar, posso postar essa resposta se você preferir)

Supondo que seus dados de exemplo estão em A, B e C:

em D2, coloque =A2&B2 e copie os dados abaixo

Em E1, coloque o primeiro nome | na E2, coloque o segundo nome
no E3, coloque a fórmula =SQRT(SUMIF(D2:D5,E1&E2,C2:C5)*SUMIF(D2:D5,E2&E1,C2:C5))

o SumIf verifica os nomes, primeiro na ordem indicada, e soma o número de emails, e o segundo, na ordem inversa, e adiciona o número.

Observe que isso dará zero se os e-mails forem apenas em uma direção ou se nenhum email for entre uma pessoa ou outra.

    

Answer 2

Sem uma coluna auxiliar e para todas as versões xl

=GEOMEAN(SUMPRODUCT(--(A1:A4="Bob"),--(B1:B4="Cindy"),C1:C4),SUMPRODUCT(--(A1:A4="Cindy"),--(B1:B4="Bob"),C1:C4))