How to Get the Average of First 5 Non-Blank Cells in a Column?

**southerk** · 03-24-2014, 07:56 PM

Hi - I'm brand new to the forum but know there is someone that can help..

I have a problem and I found a good explanation of something similar here:

http://www.excelforum.com/excel-gene...-a-column.html

In the original posting the following formula solves the problem (attached) TestData.xlsx

B1: =AVERAGE(INDEX(B3:B100,MATCH(TRUE,B3:B100<>0,0)):INDEX(B3:B100,MATCH(TRUE,B3:B100<>0,0)+4))

What I would like to return is the average of the 1st 5 values, but what if a number of cells after the first value were also blank

I hope this makes sense and hope you can help

Thanks

**Tony Valko** · 03-24-2014, 08:19 PM

Try this array formula**:

=AVERAGE(E3:INDEX(E3:E90,SMALL(IF(ISNUMBER(E3:E90),ROW(E3:E90)),5)-ROW(E3)+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.

**southerk** · 03-24-2014, 08:31 PM

Thank you Tony

Fantastic - I was having real trouble with this, seems simple now

thanks again

**Tony Valko** · 03-24-2014, 08:40 PM

You're welcome. Thanks for the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

**southerk** · 03-24-2014, 09:11 PM

Tony

Just one more thing - obviously I can replace the AVERAGE function with MIN/MAX etc.

Is there a function to enable me to take the 5th populated cell?

**Tony Valko** · 03-24-2014, 09:21 PM

Try this...

Data Range

	A	B	C
1	------	------	------
2	1		5
3
4
5	2
6	3
7
8
9	4
10
11
12
13	5
14	6
15	7

This array formula** entered in C2:

=INDEX(A:A,SMALL(IF(A2:A15<>"",ROW(A2:A15)),5))

** 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.

**southerk** · 03-24-2014, 09:47 PM

Tony

Great stuff - thanks for all your help

I can now work your responses into my target doc

Thanks again!!

**Tony Valko** · 03-24-2014, 10:01 PM

Good deal. Thanks for the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

How to Get the Average of First 5 Non-Blank Cells in a Column?

LinkBack

Thread Tools

Rate This Thread

Display

How to Get the Average of First 5 Non-Blank Cells in a Column?

Re: How to Get the Average of First 5 Non-Blank Cells in a Column?

Re: How to Get the Average of First 5 Non-Blank Cells in a Column?

Re: How to Get the Average of First 5 Non-Blank Cells in a Column?

Re: How to Get the Average of First 5 Non-Blank Cells in a Column?

Re: How to Get the Average of First 5 Non-Blank Cells in a Column?

Re: How to Get the Average of First 5 Non-Blank Cells in a Column?

Re: How to Get the Average of First 5 Non-Blank Cells in a Column?

Thread Information

Users Browsing this Thread

Similar Threads

Excel 2007 : How to Get the Average of First 5 Non-Blank Cells in a Column?

[SOLVED] Formula to Average column D:D that contains a combination of numbers, text and blank cells

Average the last 10 cells of a dynamic column but ignore blank cells & 0

[SOLVED] find blank cells in column and average the cells below

Average of next 6 non blank cells in a column.

Bookmarks

Bookmarks

Posting Permissions