Средняя функция, возвращающая значение DIV0 Despie Non-Zero в ячейке

У меня есть столбец положительных и отрицательных значений, которые не могут быть усреднены и для которых функция СУММ всегда возвращает 0, а функция AVERAGE всегда возвращает ошибку DIV0. Я попробовал очистить и обрезать ячейки и удостоверился, что ячейки отформатированы по номеру (я также попробовал формат учета, чтобы увидеть, будет ли это иметь значение). Кто-нибудь знает, почему это может произойти?

Например, значения могут быть:

-1.10 -2.30 4.00 -2.15 3.25

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

Скажем, у вас есть диапазон, такой как A1: A10, который, как представляется, имеет значения, но SUM равен нулю, а AVERAGE возвращает # DIV / 0!

Обычно это означает, что значения являются Text. Чтобы усреднить диапазон, попробуйте:

 =SUMPRODUCT(--(A1:A10))/COUNTA(A1:A10)

Альтернативный подход состоял бы в том, чтобы преобразовать значения в истинные числа, а затем использовать AVERAGE ()

EDIT # 1

Выберите данные, вызывающие проблемы, и запустите следующий макрос:

 Sub CleanData() Dim r As Range Dim v As String Dim t As String Dim L As Long For Each r In Selection v = r.Text If v <> "" Then t = "" L = Len(v) For i = 1 To L ch = Mid(v, i, 1) If ch Like "[0-9]" Or ch = "-" Or ch = "." Then t = t & ch End If Next i r.Clear r.Value = CDbl(t) End If Next r End Sub

Макросы очень просты в установке и использовании:

ALT-F11 открывает окно VBE
ALT-I ALT-M открывает новый модуль
вставьте содержимое и закройте окно VBE

Если вы сохраните книгу, макрос будет сохранен вместе с ней. Если вы используете версию Excel позже 2003 года, вы должны сохранить файл как .xlsm, а не .xlsx

Чтобы удалить макрос: