Average of the first 5 populated cells in Column A?

[rockford]

I have cell values AND empty cells in column A that could be anywhere within column A (column range from A1 to around A1000 - but maximum range can change) but the cell placements and the column range changes often, and so I would just need a formula or vba code for the: "Average of the first 5 populated cells in column A".

I had a look as AverageIF, CountA, OFFSET, but just cannot work out how it would be possible.

If someone could help I would really appreciate it.

Regards,

 

[XOR LX]

Hi,

Try this array formula**:

=AVERAGE(IF(ROW(A1:A100)<=SMALL(IF(A1:A100<>"",ROW(A1:A100)),5),A1:A100))

Obviously adjust the range (A1:A100) appropriately. Note that inserting rows within this range will most likely cause incorrect results. If that is something you envisage having to do, let me know and I will amend the formula accordingly.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

 

[Snakehips]

rockford,

Maybe if you are happy to introduce a 'helper' column?

I have used B but it could be any out of the way otherwise unused column and could be hidden if you wish.

Copy helper formula down as far as you need.

Average is in D1

Excel 2007
	A	B	C	D
1		0		8
2		0
3	5	1
4		1
5		1
6	10	2
7		2
8		2
9		2
10		2
11		2
12	15	3
13		3
14		3
15	5	4
16		4
17		4
18		4
19	5	5
20		5
21		5
22	100	6
23		6
24		6
25	55	7
26		7
Sheet2

Cell Formulas
Range	Formula
B1	=COUNTA(A$1:A1)
D1	=AVERAGEIF(B1:B4985,"<6",A1:A3985)

Hope that helps.

 

[Snakehips]

Revision....

Using XOR's excellent lead I think you will need...

Excel 2007
	A	B	C	D
1				8
2
3	5
4
5
6	10
7
8
9
10
11
12	15
13
14
15	5
16
17
18
19	5
20
21
22	100
23
24
25	55
26
27
28
Sheet2

Cell Formulas
Range	Formula
D1	{=SUM(IF(ROW(A1:A100)<=SMALL(IF(A1:A100<>"",ROW(A1:A100)),5),A1:A100))/5}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[WarPigl3t]

Too easy.

Sub Macro1()
c = 0
i = 2 [COLOR=#008000]'The loop begins in cell A2 because I assumed you have headers.  If A1 is part of your dataset and not a header, then change this 2 into a 1.[/COLOR]
output = 0
Do Until c = 5
    If Range("A" & i).Value <> "" Then
    output = output + Range("A" & i).Value
    c = c + 1
    End If
i = i + 1
Loop
Range("B2").Value = output / 5 [COLOR=#008000]'output is placed into B2 so change B2 to whatever cell you want the average to go into.[/COLOR]
End Sub

 

[rockford]

Hi and thanks.

XOR LX I've tried your's but I'm getting a #NUM!
Snakehips I've tried your second post on my workbook, but also getting #NUM!. I've also tried to put your table in exactly in a new blank workbook, but with that formula D1 is giving me #NUM! again.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

Either...

=AVERAGE(IF(ISNUMBER(A1:A35),IF(ROW(A1:A35)<=SMALL(IF(ISNUMBER(A1:A35),
  ROW(A1:A35)),5),A1:A35)))

Or...

=AVERAGE(IF(ISNUMBER(A1:A35),IF(ROW(A1:A35)<=SMALL(IF(ISNUMBER(A1:A35),
  ROW(A1:A35)),MIN(5,COUNT(A1:A35))),A1:A35)))

Adjust the range to suit.

 

[rockford]

WarPiglet - worked exactly as needed, without having to worry about the max column range. Thanks a lot.

 

[WarPigl3t]

Or you can turn the code I just gave you into a function.

Function avgTop5Macro()
c = 0
i = 2
output = 0
Do Until c = 5
    If Range("A" & i).Value <> "" Then
    output = output + Range("A" & i).Value
    c = c + 1
    End If
i = i + 1
Loop
avgTop5Macro = output / 5
End Function

Then type =avgTop5Macro() into whatever cell you want.

 

[rockford]

Ah - I didn't know about the CTRL SHIFT ENTER, so those formulas now work too.

Thanks all.