CODEWITHSUNDEEP
excel
Rachael Moir
Rachael Moir

Reputation: 37

Skip any NA values and take average

I have columns of data that start with NA in the first 1-6 cells (depending on which column - see picture for example).

Data

I'd like a formula that searches from the top for the first non-"NA" cell and takes the average of the six cells after the last NA.

Upvotes: 2

Views: 196

Answers (2)

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3823

If you only want the first 6 cells, and NA can be from 1-6 cells down from the header, I suggest you use OFFSET to create a dynamic range which only covers that area - and then you can take an AVERAGE of those cells.

OFFSET works by starting at a given point, then moving up/down, left/right a given number, and going for a given width/height. In your case, assuming you want a formula to count the cells in column A starting after the last NA & going for 6 cells, try this [Assume that the header is in row 2, and this cell goes in A100 or whatever the bottom of your data is, and can then be dragged right]:

=AVERAGE(OFFSET(A3, COUNTIF(A3:A99,"NA"),0,6,1))

This OFFSET formula assumes first a starting spot of A2, and uses a COUNTIF function to find how many rows contain NA there are - this is how many rows down the range needs to start at. It then moves 0 columns to the right, and goes for a height of 6 rows and a width of 1 column. The AVERAGE of this range should only count the 6 rows after the last presence of NA.

Upvotes: 1

Excel Hero
Excel Hero

Reputation: 14764

For column B you can use this formula:

=AVERAGE(OFFSET(B1,MATCH(TRUE,ISNUMBER(B1:B999),)-1,,6))

IMPORTANT: This is an array formula and must be confirmed with Ctrl+Shift+Enter.

Change the B's for averaging other columns.

Note: if your data extend further down the sheet that row 999 then change the 999 in the formula to a suitable row number.

Upvotes: 1

Related Questions