跳到主要内容

如何平均忽略Excel中的错误值的单元格?

当您应用AVERAGE函数来计算包含一些错误值的单元格区域的平均值时,您将得到错误结果。 下一篇文章将讨论如何平均忽略Excel中的错误的单元格。

平均单元格忽略使用数组公式的错误值


箭头蓝色右气泡 平均单元格忽略使用数组公式的错误值

以下便捷的数组公式可以帮助您计算排除错误的单元格的平均值。 请这样做:

1。 输入此数组公式: =平均(IF(ISERROR(A1:C6),“”,A1:C6)),请参见屏幕截图:

文档平均跳过错误1

2。 然后按 Ctrl + Shift + Enter 按键,您将获得平均结果,如下图所示:

文档平均跳过错误1

笔记:

1.除了上述公式外,这里还有另一个公式也可以为您提供帮助: =平均(IF(ISNUMBER(A1:C6),A1:C6)),请记住按 Ctrl + Shift + Enter 键。

2.在上述公式中, A1:C6 是您要计算的数据范围,您可以根据需要进行更改。


相关文章:

如何在Excel中平均绝对值?

如何在Excel中仅对正数或负数求平均?

如何在Excel中平均忽略零的数据范围?

如何在Excel中没有最大值和最小值的情况下计算平均值?

最佳办公生产力工具

热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

kte选项卡201905


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (13)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thanks!!! This helped me a lot!
This comment was minimized by the moderator on the site
Hi, there is a simpler way using the "AGGREGATE" function. This is a generic function which allows one to calculate a number of simple statistics (eg. average, minimum, st. dev) ignoring error values or other types of values (e.g. zeros, hidden etc)=.
For average you would type:
=AGGREGATE(1,6,A1:C6)
NB: In this formula Option 1 is to calculate average while Option 6 is to ignore error values.
This comment was minimized by the moderator on the site
Excellent! Easy and functional. Thanks.-
This comment was minimized by the moderator on the site
It works,

Thanks.
This comment was minimized by the moderator on the site
This website is such a lifesaver.
This comment was minimized by the moderator on the site
=SUMIF(A1:C6,">=0")/COUNT(A1:C6)
This comment was minimized by the moderator on the site
Try this =SUMIF(I13:I18,">=0")/COUNT(I13:I18)
This comment was minimized by the moderator on the site
This was very useful for me. Thanks!
This comment was minimized by the moderator on the site
I think it should be =SUMIF(I13:I18,">=0")/COUNTIF(I13:I18,">=0")
This comment was minimized by the moderator on the site
If you have Excel 2010 and above use AGGREGATE FUNCTION i.e AGGREGATE(1,7,A1:C 6)
This comment was minimized by the moderator on the site
You saviour :-). Time i spent online to find alternative formulas..thanks
This comment was minimized by the moderator on the site
J6:J11=AVERAGE(IF(ISERROR(A1:C6),"",A1:C6)), DOESNT WORKK1!!1
This comment was minimized by the moderator on the site
=AVERAGE( IF(ISERROR(A1:C 6),"",A1:C6)) It works! Thank you!!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations