Geek Answers Handbook

Tolerance Mean (ignore #NA, etc.)

I want to calculate the average value over the range (B1: B12 or C1: C12 in the figure), with the exception of:

  • Cells are not numeric, including blank lines, blank cells with no content,, #NAtext, etc. (B1 + B8: B12 or C1 + C8: C12 here).
  • Cells for which the corresponding cells in the range (A1: A12 here) have values ​​outside the interval (here [7.35]). This further excludes B2: B3 or C2: C3. At this point, the cells in column A may contain numbers or may not contain content.

I think it is impossible to use any built-in function AVERAGE. Then I tried to calculate the amount, account and split. I can calculate the counter (F2 and F7), but not the sum (F3) when I have #N/Aa range, for example

How can i do this?

Notes:

  • Column G shows the formulas in column F.
  • I can not filter and use SUBTOTAL.
  • B8: C8 contain empty cells without content, B9: C9 contain empty lines.
  • I am looking for (non-user) formulas, i.e. non-vba.

enter image description here

+4
source share
3 answers

You can accomplish this using array formulas based on nested IFs to provide at least some of the criteria. When FALSEIF resolves , it no longer processes part of the TRUE statement .

AVERAGE over ERRORs

Array formulas in F2: F3:

=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)))

Array formulas in F7: F8:

=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)))

Ctrl + Shift + Enter↵. , , .

, (), , . .

+4

fooobar.com/questions/987214/...:

Excel 2010 AGGREGATE .

=AGGREGATE(1, 6, A1:A5)

AGGREGATE excluding errors

+3

"NA" :

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

Ctrl Shift Enter↵.

, , . "" *; , , TRUE FALSE 1 0 , , , TRUE/FALSE >0. : 7 35 ( ), .

enter image description here

; AVERAGE SUM COUNT :

=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))

although a more concise formula can also be used for counting:

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

The same formulas can be used to calculate the average amount / amount / count of your "empty" column. Here I just dragged them into one column on the right (column G), which means that all instances are B2:B13steel C2:C13.

+1
source

More articles:


All Articles