AVERAGEIFS values that are not NA

Question

I have the following AVERAGEIFS that averages column I grouped by the values in column B

= AVERAGEIFS(B:B, A:A, A:A)

Problem is, sometimes, the column B sometimes contains an NA() error. How do I filter these out of the equation?

I tried to do a filter

= AVERAGEIFS(ARRAYFORMULA(IF(ISNUMBER(B:B),B:B)), A:A,A:A)

but this gives me an error.

Here is some sample data:

* A    *  B  *
| Fred |  8  |
| Bill |  9  |
| Jane |  NA |
| Jane |  9  |
| Fred |  NA |
| Bill |  5  |
| Fran |  9  |
| Fred |  4  |
| Jane |  1  |

What I want is to add a column C which shows next to each row, the person's average not counting the NAs.

* A    *  B  * C *
| Fred |  8  | 6 |
| Bill |  9  | 7 |
| Jane |  NA |   |
| Jane |  9  | 5 |
| Fred |  NA |   |
| Bill |  5  | 7 |
| Fran |  9  | 9 |
| Fred |  4  | 6 |
| Jane |  1  | 5 |

As for the blank spots, I don't care much for what happens there - it can be an error, or a blank, so long as the NAs don't affect the average I don't care.

Answer 1

Option 1 - IFNA() with Array

I don't really understand what your AVERAGEIFS is actually supposed to be doing, but all you need to do to fix this is to use the IFNA() along with an array formula (Ctrl + Shift + Enter when entering the formula):

= AVERAGEIFS(IFNA(B:B,""), A:A, A:A)

This turns NA() values into blanks in the data you're handling.

---

Option 2 - Clean your Data

This would normally be my option 1, but I'm aware it's not what you've asked for, and so I've demoted it to 2...

Obviously, all the above formula does is replace NA() with blank values. Alternatively, why not just initially have your cells calculating as blanks? This will make your columns look nicer too.

Whatever calculation you have in your base cells, either change the NA() option to return "" instead, or wrap the whole calculation in IFNA([insert calculation here],"")

Answer 2

A not so smart way will be adding a new hidden column (say column D) with:

=IF(ISNUMBER(B:B),B:B,0)

Then your =AVERAGEIFS(D:D,A:A,A:A) will work as intended:

Fred    8   4
Bill    9   7
Jane    NA  3.333333333
Jane    9   3.333333333
Fred    NA  4
Bill    5   7
Fran    9   9
Fred    4   4
Jane    1   3.333333333