Рассчитать среднее геометрическое в Excel

Question

У меня есть некоторые данные сети электронной почты в Excel в качестве пограничного списка, что означает, что у меня есть столбцы Vertex1, Vertex2, а затем N столбцов свойств этого края, например, сколько сообщений электронной почты было отправлено от одного человека другому. Для каждой строки в данных Vertex1 является источником сообщения, а Vertex2 является целью, поэтому ребра направлены. Вот некоторые примеры данных

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

Я пытаюсь вычислить среднее геометрическое значение формы

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

Так что gm для Боба и Синди это gm = sqrt [(сообщения от Боба к Синди) *(сообщения от Синди к Бобу)] или sqrt(12 * 3) = 6. Есть ли способ сделать эту формулу в Excel?

Answer 1

Я могу сделать это с помощью вспомогательной колонки. (в Excel 2007 или выше мне не нужен вспомогательный столбец, я могу опубликовать этот ответ, если хотите)

Предполагая, что ваш пример данных находится в A, B и C:

в D2 положите =A2&B2 и скопируйте данные

В E1 поставить имя
в е2 поставь второе имя
в E3 введите формулу =SQRT(SUMIF(D2:D5,E1&E2,C2:C5)*SUMIF(D2:D5,E2&E1,C2:C5))

SumIf проверяет имена, сначала в указанном порядке, и складывает количество электронных писем, а затем в обратном порядке и суммирует количество.

Обратите внимание, что это даст ноль, если электронные письма идут только в одном направлении, или если никакие электронные письма не идут между любым человеком.

Answer 2

Без вспомогательного столбца и для всех версий xl

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