Tolerant average (ignore #NA, etc.)

问题:

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.

回答1:

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.

回答2:

From https://stackoverflow.com/a/30242599/2103990:

Providing you are using Excel 2010 and above the AGGREGATE function can be optioned to ignore all errors.

=AGGREGATE(1, 6, A1:A5)

        

回答3:

You can get the average of your "NA" column values in one fairly simple formula like this:

=AVERAGE(IF(
            (
             ($A$2:$A$13>=$F$2)*
             ($A$2:$A$13<=$F$3)*
             ISNUMBER(B2:B13)
                             )>0,
                                 B2:B13))

entered as an array formula using CtrlShiftEnter↵.

I find this to be a very clear way of writing it, because all your conditions are lined up next to each other. They're "and'ed" using the mathematical operator *; this of course converts TRUE and FALSE values to 1's and 0's, respectively, so when the and'ing is done, I convert them back to TRUE/FALSE using >0. Note that instead of hard-coding your thresholds 7 and 35 (hard-coding literals is usually considered bad practice), I put them in cells.

Same logic for your sum and your count; just replace AVERAGE with SUM and COUNT, respectively:

=SUM(IF((($A$2:$A$13>=$F$2)*($A$2:$A$13<=$F$3)*ISNUMBER(B2:B13))>0,B2:B13))
=COUNT(IF((($A$2:$A$13>=$F$2)*($A$2:$A$13<=$F$3)*ISNUMBER(B2:B13))>0,B2:B13))

though a more succinct formula can also be used for the count:

=SUM(($A$2:$A$13>=$F$2)*($A$2:$A$13<=$F$3)*ISNUMBER(B2:B13))

The same formulas can be used to average/sum/count your "blank" column. Here I just drag-copied them one column to the right (column G), which means that all instances of B2:B13 became C2:C13.