Статистика в Excel, включая измененные текстовые значения

Question

У меня есть таблица в Excel (2013), которая выглядит примерно так:

V1    V2    V3     V4
2.1   300   40.5   6.89
6.9   204   <0.02  <0.001
<0.3  96    6.5    1.2

Для каждого из столбцов с V1 по V4 я хотел бы сделать некоторые основные статистические данные, такие как среднее, максимальное и минимальное значения.

Однако я также хотел бы включить значения с символом < . Но эти ячейки интерпретируются как текст и поэтому исключаются из формул.

Как я могу рассчитать это без необходимости вручную / полуавтоматически удалять символ < ? Мне также нравится избегать копирования всего на новый лист, так как это может привести к ошибкам при вводе новых значений.

Я пробовал разные утверждения IF, но не смог обойтись, что они работают по клеткам.

Answer 1

Чтобы сделать это без помощников, используйте --SUBSTITUTE() в качестве массива:

--SUBSTITUTE($A$2:$D$4,"<","")

Итак, чтобы получить среднее значение:

=AVERAGE(--SUBSTITUTE($A$2:$D$4,"<",""))

Чтобы получить медиану:

=MEDIAN(--SUBSTITUTE($A$2:$D$4,"<",""))

При этом формулы должны быть введены как формулы массива. Для этого После ввода формулы нажмите Ctrl-Shift-Enter вместо Enter, Tab или щелкнув ячейку. Если все сделано правильно, Excel поместит {} вокруг формулы.