宽容平均值（忽略#NA等） [英] Tolerant average (ignore #NA, etc.)

问题描述

我想计算一个范围内的平均值（图中的B1：B12或C1：C12），不包括：

1. 单元格不是数字，包括空字符串，没有内容的空白单元格， #NA ，文本等（B1 + B8：B12或C1 + C8：C12这里） / li>
   
2. 范围内的相应单元格（A1：A12）的单元格具有间隔之外的值（[7,35]）。这将进一步排除B2：B3或C2：C3。
   此时，列A中的单元格可能包含数字或没有内容。

我认为不可能使用任何内置的 AVERAGE 类功能。然后，我试着计算总和，计数和除数。当我有＃N / A 在范围内时，我可以计算（F2和F7），但不是总和（F3），例如

我该怎么做？

注意：

1. 列G显示F列中的公式。


2. 我无法过滤并使用 SUBTOTAL 。


3. B8：C8包含没有内容的空白单元格，B9：C9包含空字符串。


4. 我正在寻找（非用户定义的）公式，即非VBA 。

解决方案

您可以通过使用基于嵌套IF的数组公式来实现此目的至少部分标准。当 IF 解析为 FALSE 时，它不再处理语句的 TRUE 部分。

F2：F3中的数组公式是

  = SUM（IF（NOT ISNA（B2：B13）），（A2：A13> = 7）*（A2：A13 <= 35）*（B2：B13  - ）））
 = SUM（IF （B2：B13）），IF（B2：B13，（A2：A13> = 7）*（A2：A13 <= 35）* B2：B13）））
  

F7中的数组公式：F8是

< （A2：A13> = 7）*（A2：A13< = 35）*（C2：C13> = 7） （A2：A13> = 7）*（A2：A13> = 7）*（A2：A13> = 7） = 35）* C2：C13）））

数组公式需要用 Ctrl + 移 + Enter↵。一旦输入正确，就可以像任何其他公式一样填写。如果需要，可以像其他任何公式一样填充。

数组公式将计算负载对数增加为扩展的范围。尝试将多余的空白行保持最小，并避免使用完整的列引用。

I want to calculate the average over a range (B1:B12 or C1:C12 in the figure), excluding:

1. Cells not being numeric, including Empty strings, Blank cells with no contents, #NA, text, etc. (B1+B8:B12 or C1+C8:C12 here).
2. Cells for which corresponding cells in a range (A1:A12 here) have values outside an interval ([7,35] here). This would further exclude B2:B3 or C2:C3. At this point, cells in column A may contain numbers or have no contents.

I think it is not possible to use any built-in AVERAGE-like function. Then, I tried calculating the sum, the count, and divide. I can calculate the count (F2 and F7), but not the sum (F3), when I have #N/A in the range, e.g.

How can I do this?

Notes:

1. Column G shows the formulas in column F.
2. I cannot filter and use SUBTOTAL.
3. B8:C8 contain Blank cells with no contents, B9:C9 contain Empty strings.
4. I am looking for (non-user defined) formulas, i.e., non-VBA.

解决方案

You can accomplish this by using array formulas based upon nested IFs to provide at least part of the criteria. When an IF resolves to FALSE it no longer process the TRUE portion of the statement.

   

The array formulas in F2:F3 are,

=SUM(IF(NOT(ISNA(B2:B13)), (A2:A13>=7)*(A2:A13<=35)*(B2:B13<>"")))
=SUM(IF(NOT(ISNA(B2:B13)), IF(B2:B13<>"", (A2:A13>=7)*(A2:A13<=35)*B2:B13)))

The array formulas in F7:F8 are,

=SUM(IF(NOT(ISNA(C2:C13)), (A2:A13>=7)*(A2:A13<=35)*(C2:C13<>"")))
=SUM(IF(NOT(ISNA(C2:C13)), IF(C2:C13<>"", (A2:A13>=7)*(A2:A13<=35)*C2:C13)))

Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered correctly, they can be filled down like any other formula if necessary.

Array formulas increase calculation load logarithmically as the range(s) they refer to expand. Try to keep excess blank rows to a minimum and avoid full column references.

这篇关于宽容平均值（忽略#NA等）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！