При выполнении функции averageif произошла ошибка деление на ноль невозможно

I have looked around for the answer and can’t find it (even though lots of people are having problems with this) — if someone’s seen the answer somewhere, please let me know.

I’m doing a very very basic travel expenses sheet

Month by month, I need to go through a column and find a type of expense occurred that month — and then tally it up with all the expenses of the same type. For a monthly total.

Then i’ve got a column that needs to find out what was the average spend for that type of expense that month. So i can track month-on-month improvement

example — http://f.cl.ly/items/1i3Z0Q0m1V2F2h2B3c2b/Screen%20Shot%202014-12-09%20at%2018.28.47.png

The SUM bit is easy

SUMIF(D2:D300, 'Food', A2:A300)

so, go through ‘D‘ column, find ‘Food‘, and if you find it: give me the value for the same row on ‘A‘ Column, and add them all together..

The Averaging is a bit tricky

i tried

(H4*12)/365

Which basically multiplies the monthly value by 12 and divides it to find out the daily average for the year (or the yearly average?).

But that averages a single input from one month over a year (taking into account months in the future which are zero) therefore it brings the average off 🙁

What i want to find out is

‘What was the daily average ammount I spent getting Food, in January?’

the tricky bit, obviously, is because in the Expense type column there are values i have to discard, so i don’t average the food with the accomodation

so i tried

AVERAGEIF(D2:D19, 'Food', A2:A19)

which basically says go through ‘D‘ column, find ‘Food‘, and if you find it: give me the value for the same row on ‘A‘ Column, and average all those values..

which works great for the items i’ve spent money on, but — if i havent spent anything that month (let’s say on Tips) it gives me back an error rather than just ‘0‘

=AVERAGEIFS(Sheet1!$C:$C,Sheet1!$A:$A,D3,Sheet1!$B:$B,$A$2,Sheet1!$D:$D,$B$2,Sheet1!$D:$D,$B$3)

1 ответ

Лучший ответ

=query(Sheet1!A:D, "Select A, avg(C) where B ='"&A2&"' and D matches '"&textjoin("|", true, B2:B3)&"' group by A label avg(C) 'Average'", 1)

=AVERAGE(FILTER(Sheet1!$C:$C,Sheet1!$A:$A=D3,Sheet1!$B:$B=$A$2,( (Sheet1!$D:$D=$B$2) + (Sheet1!$D:$D=$B$3))))

Функция СРЗНАЧЕСЛИ (AVERAGEIF) в Excel используется для вычисления среднего арифметического по заданном диапазону данных и критерию.

Что возвращает функция

Возвращает число, обозначающее среднее арифметическое по заданному диапазону данных и критерию, указанному в качестве аргумента.

Синтаксис

=AVERAGEIF(range, criteria, [average_range]) — английская версия

=СРЗНАЧЕСЛИ(диапазон, условия, [диапазон_усреднения]) — русская версия

Аргументы функции

• range (диапазон) — диапазон ячеек, по которому будет осуществлена проверка на соответствие заданному критерию;
• criteria (условия) — критерий, по которому определяется какие значения из диапазона ячеек подходят для вычисления среднего арифметического;
• [average_range] ([диапазон_усреднения]) (Опционально) — диапазон ячеек, по которым функция осуществит вычисление среднего арифметического, при соответствии заданному критерию. Если этот аргумент не указан в формуле, функция производит вычисления по аргументу range(диапазон).

Дополнительная информация

• Пустые ячейки игнорируются при вычислении;
• Если в качестве критерия указана пустая ячейка, то функция воспринимает ее значение как «0»;
• Если ни одна ячейка из заданного диапазона не соответствует критерию, функция выдаст ошибку;
• Если диапазон ячеек пуст или содержит данные в текстовом формате, формула выдаст ошибку;
• Критерием может выступать число, выражение, ссылка на ячейку, текст или формула;
• Критерий, указанный в формате текста или логического/математического символа (=,+,-,/,*) следует указывать в двойных кавычках;
• Подстановочные знаки могут использоваться в качестве критерия.

Примеры использования функции СРЗНАЧЕСЛИ в Excel

Пример 1. Вычисляем среднее арифметическое по критерию

На примере выше функция проверяет список значений в диапазоне «А2:А6» на соответствие критерию «Андрей» и вычисляет по соответствующим критерию ячейкам среднее арифметическое в диапазоне ячеек «В2:В6».

Так как в диапазоне «А2:А6» указаны данные для двух ячеек «Андрей» — «62» и «19», то функция вычисляет среднее арифметическое — «40.5».

Пример 2. Используем подстановочные знаки в функции СРЗНАЧЕСЛИ

Вы можете использовать подстановочные знаки в критерии функции.

В Excel существует три подстановочных знака — ?, *, ~.

• знак «?» — сопоставляет любой одиночный символ;
• знак «*» — сопоставляет любые дополнительные символы;
• знак «~» — используется, если нужно найти сам вопросительный знак или звездочку.

На примере выше, функция проверяет список данных в диапазоне «А2:А6» на соответствие критерию с подстановочными знаками «*а*», который подразумевает любые данные содержащие букву «а». Так как в диапазоне данных «А2:А6» этому критерию соответствуют все имена кроме «Олег» => среднее арифметическое будет вычислено по диапазону ячеек B2:B6 (исключая ячейку B5) = «29.5».

Пример 3. Используем операторы сравнения в функции СРЗНАЧЕСЛИ

В случаях, когда вы не указываете аргумент average_range (диапазон_усреднения), она, автоматически, производит расчеты из заданного диапазона ячеек в аргументе range (диапазон).

На примере выше, функция определяет по заданному критерию «>19» какие ячейки из диапазона «B2:B6» больше числа «19» и вычисляет по ним среднее арифметическое. Важно, любые операторы следует указывать в двойных кавычках!