Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Среднее значение при наличиинулевых значений по нескольким условиям
 
Добрый день. Прошу Вас помочь решить вопрос с определением среднего значения по строчному массиву чисел в пределах 20 % от минимального.
с уважением
 

Вроде все просто =AVERAGEIF(A3:E3;"<="&MIN(A3:E3)*1,2) или если угодно то =SUMIF(A3:E3;"<="&MIN(A3:E3)*1,2)/COUNTIF(A3:E3;"<="&MIN(A3:E3)*1,2) , но что по вашему 20% от минимального? Почему 115 вдруг не попало  в эти 20%. Может  расчет процентов за последнее время как-то изменлся, но как не считаю, получается 120 и 121.2 или среднее значение не должно превысить 20% от минимального?

Изменено: БМВ - 4 Мар 2018 18:01:44
 
Прошу прощения, не уточнил сразу. Количество чисел, входящих в 20%, не должно быть более трех. Поэтому, 115 не вошло в расчет среднего значения.
с уважением
 
А 121 из второй строки почему не учили? В ячейке С4 условие выполняется:
=C4<=МИН($A$4:$E$4)*1,2
Изменено: Bema - 4 Мар 2018 17:32:56
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
Тогда почему не вошло 121 во второй строке, или минимум - это минимальное из всех данных, а не минимальное в строке?

Изменено: БМВ - 4 Мар 2018 17:52:49
 
Поправил пример.
 

=SUMPRODUCT((SMALL(A3:E3;ROW($A$1:$A$3))<=MIN(A3:E3)*1,2)*SMALL(A3:E3;ROW($A$1:$A$3)))/SUMPRODUCT(--(SMALL(A3:E3;ROW($A$1:$A$3))<=MIN(A3:E3)*1,2))

 
Прошу прощения, "121" входило в 20% от минимального значения по строке. Поэтому, в уточненном примере это число заменил.
с уважением
 
Огромное Вам спасибо за помощь и Ваши знания. Все великолепно работает. Всего Вам хорошего. Удачи.
С уважением.
 
Еще вариант:
=СРЗНАЧ(ЕСЛИ(НАИМЕНЬШИЙ(A3:E3;{1;2;3})<=МИН(A3:E3)*1,2;НАИМЕНЬШИЙ(A3:E3;{1;2;3})))
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
Цитата
Bema написал:
Еще вариант:
Я б чечтно сказал, что более простой и правильный вариант. Я в паралель с просмотром кино не впихнул массив в AVERAGEIF :-) и не продолжил, забыв что AVERAGE по иному работает :-(
 
P.S. По пробовал развить таблицу и копировать формулу. Но что-то не получилось. Прошу подсказать,что я делаю не правильно. Файл прикладываю.
спасибо
 

УУУУ, Все правильно, но и сложная и более простая формула от Bema, не справится. Пустые ячейки надо игнорировать.

Изменено: БМВ - 4 Мар 2018 19:45:02
 
Можно ли решить этот вопрос, если в строке (такое бывает редко) одно значение.
спасибо
 
Паразитируя на формуле от  Bema, массивная
=AVERAGE(IF(IFERROR(SMALL(A3:E3;{1;2;3});FALSE)<=MIN(A3:E3)*1,2;IFERROR(SMALL(A3:E3;{1;2;3});FALSE)))
или
=AVERAGE(IF(IFERROR(SMALL(A3:E3;{1;2;3});1=0)<=MIN(A3:E3)*1,2;IFERROR(SMALL(A3:E3;{1;2;3});1=0)))
подрезав FALSE

И снова 121 у вас
Изменено: БМВ - 4 Мар 2018 19:59:25
 
Спасибо Вам огромное. Работает замечательно на все случаи. Завтра применю на работе. За ошибки прошу прощения просмотрел (у нас уже поздно - первый час ночи).
Еще раз спасибо и всего хорошего.
С уважением.
 
Добрый день. В продолжении темы прошу помочь разрешить ситуацию, когда в некоторых ячейках строк попадают нули или пустые ячейки. Пример 2 прикладываю. С уважением
 
Цитата
arkadiy написал:
Пример 2 прикладываю.
И куда Вы его приложили ;) ?
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
Добрый вечер. В продолжении темы: определения среднего значения по строчному массиву чисел в пределах 20% от минимума, прошу помочь разрешить ситуацию, когда в некоторых ячейках строк попадают нули. Нулевые значения на должны приниматься в расчет среднего значения.
 
Ничего лучше не придумал:
=СРЗНАЧ(ЕСЛИ(НАИМЕНЬШИЙ(ЕСЛИ(A3:E3<>0;A3:E3;9^9);ЕСЛИ(СЧЁТЕСЛИ(A3:E3;">0")>=3;{1;2;3};СТРОКА(СМЕЩ($A$1;;;СЧЁТЕСЛИ(A3:E3;">0")))))<=МИН(ЕСЛИ(A3:E3<>0;A3:E3))*1,2;НАИМЕНЬШИЙ(ЕСЛИ(A3:E3<>0;A3:E3;9^9);ЕСЛИ(СЧЁТЕСЛИ(A3:E3;">0")>=3;{1;2;3};СТРОКА(СМЕЩ($A$1;;;СЧЁТЕСЛИ(A3:E3;">0")))))))
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
Большое спасибо Вам за Вашу помощь. Очень благодарен. Вы очень помогли мне и не только мне, т.к. нашему отделу Вашей формулы очень не хватало.
Спасибо Вам.
С Уважением.
 
arkadiy, пожалуйста. Но формула конечно такая себе. Как минимум от СМЕЩ можно избавиться и я даже знаю как (с помощью двух ИНДЕКСов ), но все никак не научусь их применять. Может кто-то из профи предложит другой вариант. Было бы интересно. Рад, что помог Вам.
Цитата
arkadiy написал:
нашему отделу Вашей формулы очень не хватало.
Что за  отдел? Чем занимаетесь? ;)  
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
Добрый день, Bema. Наш отдел - отдел снабжения предприятия. Ваша формула очень пригодится в при расчетах средней цены товара в закупочной документации, особенно, когда перечень товара длинный. Спасибо, что откликнулись на просьбу по написанию формулы. Очень Вам благодарен.
Спасибо Вам.
С уважением
 
arkadiy, пожалуйста. Рад, что смог помочь.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
arkadiy, Здравствуйте.
На всякий случай попробуйте такой вариант
=СРЗНАЧ(ИНДЕКС(АГРЕГАТ(15;6;1/(1/A3:E3);СТОЛБЕЦ(A3:ИНДЕКС(3:3;ПРОСМОТР(СЧЁТЕСЛИМН(A3:E3;">0";A3:E3;"<="&АГРЕГАТ(15;6;1,2/(1/A3:E3);1));{1;2;3}))));)
Но для excel2010 и выше, как я знаю
Изменено: Akropochev - 15 Мар 2018 17:51:42
 
Без нулей, так без нулей
=AVERAGE(IF(IFERROR(SMALL(IF(A7:E7>0;A7:E7);{1;2;3});FALSE)<=MIN(IF(A7:E7>0;A7:E7))*1,2;IFERROR(SMALL(IF(A7:E7>0;A7:E7);{1;2;3});FALSE)))

Bema, что то усложнили слишком., на сей раз я буду упрощать :-)
Изменено: БМВ - 16 Мар 2018 07:38:12 (пример ниже содержит и этот вариант.)
 
Цитата
БМВ написал:
Bema, что то усложнили слишком.
БМВ, и так и сяк крутил, ну и вот что получилось. Перемудрил не то слово.
Цитата
на сей раз я буду упрощать :-)
1:1
Изменено: Bema - 15 Мар 2018 21:52:32
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
БМВ,А у меня все равно короче и не массивная)
Но чует моё сердце, что эту прекрасную задачку можно решить очень оригинально...Пару дней поищу в финансовых функциях Excel
Изменено: Akropochev - 16 Мар 2018 00:09:28
 
Akropochev, Короче - да, хоть мне и трудно сравнивать, ибо англоязычные варианты почти всегда короче и даже если я заменю FALSE на 1=0 , то 6 в AGGREGATE заменяет целую конструкцию.
Можно тогда и так, но тоже {}
=AVERAGE(IFERROR(AGGREGATE(15;6;IF(1/(1/A7:E7)<=AGGREGATE(15;6;IF(A7:E7>0;A7:E7);1)*1,2;A7:E7);{1;2;3});FALSE))
подумал что if тоже лишние при положительных значениях
=AVERAGE(IFERROR(AGGREGATE(15;6;A3:E3/(1/(1/A3:E3)<=AGGREGATE(15;6;1/(1/A3:E3);1)*1,2);{1;2;3});FALSE))
ну и
=AVERAGE(IFERROR(AGGREGATE(15;6;A7:E7/(1/(1/A7:E7)<=AGGREGATE(15;6;1/(1/A7:E7);1)*1,2);{1;2;3});1=0))

Универсальнее - пока нет, ну наверно в 2018 2007 и более древних версий осталось мало, но встречаются
Массивность - ну разве что ввод не массивный, а так ....
Если финансовые позволят - то только рад буду ибо решение с другим подходом всегда интересное. Мы  тут не на соревновании и делимся приемами и вариантами, отмечаем чужие ошибки и свои  и учимся друг у друга.
Изменено: БМВ - 16 Мар 2018 19:26:22
 
Добрый день. Спасибо Вам за желание помогать не знакомым людям. Ваша готовность поделиться знаниями и умением отзывается уважением и благодарностью.
Большое Вам спасибо
Желаю Вам всего хорошего.
С Уважением
Страницы: 1
Читают тему (гостей: 1)
Наверх