Average of the first X non blank cells

**agbagb0** · 04-20-2013, 10:15 AM

Say, I have a cell that has a number X (A1 = 5). Then I have an column B that has numbers, some of them are blanks. I need to calculate the average of the first X non blank numbers. How do I do that - Thanks in advance.

Example :
Cell A1 has 5.
Column B has,
1
2
Blank (actually blank, not the word)
3
Blank
4
5
Blank
Blank
6
7
8

Since the A1 has 5, I would only take the first 5 non blank values, (1, 2, blank, 3, blank, 4, blank, 5) and calculate the average as 15/ 5 = 3.

Thank you!

**AZ-XL** · 04-20-2013, 11:06 AM

a1 cell equal 5 (you can change it)
your numbers from b1 cell

formula for answer

=AVERAGEIF(OFFSET(B1,0,0,SMALL(IF(ISNUMBER(B1:B12),ROW(B1:B12),""),A1),1),"<>0") Ctrl+Shift+enter

here is the file

**daddylonglegs** · 04-20-2013, 11:14 AM

If you want the formula to work in Excel 2003 then perhaps just use AVERAGE, i.e.

=AVERAGE(OFFSET(B1,0,0,SMALL(IF(ISNUMBER(B1:B12),ROW(B1:B12)),A1)))

**Tony Valko** · 04-20-2013, 11:15 AM

Assuming there will always be X numbers to average.

Array entered**:

=AVERAGE(B1:INDEX(B1:B20,SMALL(IF(ISNUMBER(B1:B20),ROW(B1:B20)),A1)-ROW(B1)+1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**agbagb0** · 04-20-2013, 11:49 AM

Originally Posted by Tony Valko

Assuming there will always be X numbers to average.

Array entered**:

=AVERAGE(B1:INDEX(B1:B20,SMALL(IF(ISNUMBER(B1:B20),ROW(B1:B20)),A1)-ROW(B1)+1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

You guys are awesome.

Thanks everyone - AZ-XL (solution in a file - great!) , daddylonglegs and Tony Valko! All the solutions have worked.

Tony Valko - Thanks for the tip on how to enter array formulas ! I got an error and I was wondering how to fix it!

Solved - how NOT to enter array formula - Post3206212.jpg

**Tony Valko** · 04-20-2013, 03:47 PM

Good deal. Thanks for the feedback!

**Sconz1227** · 01-06-2021, 07:31 PM

What if There isnt X numbers to average? How would i get to average whatever it maxes at. So i want to average 12, 24, 48 and i some instances there isnt enough to fill that requirement, but i would still like it to average what ever number is available

Thanks

**FDibbins** · 01-07-2021, 01:46 AM

Originally Posted by Sconz1227

What if There isnt X numbers to average? How would i get to average whatever it maxes at. So i want to average 12, 24, 48 and i some instances there isnt enough to fill that requirement, but i would still like it to average what ever number is available

Thanks

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread