Average of the first 5 populated cells in Column A?

R

rockford

New Member
Joined
Aug 3, 2014
Messages
20
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,
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
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>
 
Upvote 0
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
ABCD
108
20
351
41
51
6102
72
82
92
102
112
12153
133
143
1554
164
174
184
1955
205
215
221006
236
246
25557
267
Sheet2
Cell Formulas
RangeFormula
B1=COUNTA(A$1:A1)
D1=AVERAGEIF(B1:B4985,"<6",A1:A3985)


Hope that helps.
 
Upvote 0
Revision....

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

Excel 2007
ABCD
18
2
35
4
5
610
7
8
9
10
11
1215
13
14
155
16
17
18
195
20
21
22100
23
24
2555
26
27
28
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0
Too easy.
Code: 
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
Control+shift+enter, not just enter:

Either...
Rich (BB code): 
=AVERAGE(IF(ISNUMBER(A1:A35),IF(ROW(A1:A35)<=SMALL(IF(ISNUMBER(A1:A35),
  ROW(A1:A35)),5),A1:A35)))
Or...
Rich (BB code): 
=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.
 
Upvote 0
WarPiglet - worked exactly as needed, without having to worry about the max column range. Thanks a lot.
 
Upvote 0
Or you can turn the code I just gave you into a function.
Code: 
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
How to get average of percentages in a column with filtered cells and excluding zero
Replies
2
Views
150
Fluff
Fluff
E
  • Solved
Average Last 5 Numbers in Column, Excluding "#N/A"
Replies
5
Views
460
kevin9999
K
A
  • Solved
Exclude columns in listbox on userform except first two columns & last column
2
Replies
16
Views
320
abdelfattah
A
B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
167
bh24524
B
F
  • Solved
How to get rolling average of first 5 non-ZERO values in a column
Replies
7
Views
495
lrobbo314
lrobbo314

Forum statistics

Threads
1,214,389
Messages
6,119,232
Members
448,879
Latest member
VanGirl

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top