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,

 

[rockford]

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

What if there is no Count of 5 available? Sometimes there's not more than 5 populated fields available, sometimes just 4, or 3 etc. - if in these cases would just need to work out the average of the 4, or average of the 3 etc.

I just tried the above code when there is only 4 populated fields, but getting VB error "Run Time error 1004 Application defined or object defined error"

 

[WarPigl3t]

You can use this sub routine in a button.

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 or i = 1000000     
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 / c [COLOR=#008000]'output is placed into B2 so change B2 to whatever cell you want the average to go into.[/COLOR] 
End Sub

But I would advise using this function because you can just type the function name into a cell and it will run the code.
For example: In cell B2, type =avgTop5Macro()
Or you can change the name of the macro to something you like.

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

 

[WarPigl3t]

Which one do you prefer?

 

[rockford]

Very helpful, thank you again.

I prefer turning it into a function.

 

[WarPigl3t]

Well what that code does is find the first 5 rows that are not blank and get the average of them. If there is less then 5 rows, then it will continue to search the column all the way down to A1000000. I'm sure that the code runs fast even though it has to run a loop all the way into the 1000000 mark. But I know how we can reduce the time it takes to process.

If you can guarantee that column B will never have a blank in between two populated rows, for example:

	A	B
1	5	data
2
3	2	data

<tbody>
</tbody>

So if you can guarantee that that will never happen and that there will always be a value in the B cell, then you should use this code instead. That way it saves processing time.

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

The only difference is what I highlighted in Red

 

[Aladin Akyurek]

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

Thanks all.

Also the formulas in post #7?