Average only first number of actual values

[kbasarab]

Hey everyone, first let me say this group is great. I've found a lot
of great resources here. I'm working on a spreadsheet and have a 3
columns with values down the rows. Not every row has values in it
though. I want to be able to get the average of the first 9 values I
run across.

Say Row 1 has three values in it, row 2 has no values, row 3 has three
values, row 4 has three values etc...

I want to return an average of the first 9 values. I know I can do a
count to see how many fields have values but am not sure how to combine
things to get the average of the first 9 values.

Thanks in advance and let me know if that came off confusing.

 

[Don Guillett]

Does each column that has values have exactly 3 values each?

 

[kbasarab]

Yes if a row has values then it has 3 values (one per column) but there
could be a row without any values.

 

[Sandy Mann]

I'm no substitute for Don but would a simple User Defined Function do?

If so then try:

Function Average9(StartHere As Range)
Application.Volatile
Dim rRow As Long
Dim x As Long
Dim ThisRow As Long
Dim Start As Long
Dim Col As Long

rRow = 0
Start = StartHere.Row
Col = StartHere.Column

For x = Start To Rows.Count
If Cells(x, Col).Value <> "" Then rRow = rRow + 1
If rRow = 3 Then
ThisRow = x
Exit For
End If
Next x

Average9 = Application.Average(Range(Cells(Start, Col), Cells(ThisRow,
Col + 3)))

End Function

Enter the function as =Average9(C12) where C12 is the top left-hand cell of
the data that you want to average. The function assumes that the columns
are contiguous.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

For a formula approach, assuming your data is in columns A, B and C and your
first 3 rows of values are within the first 100 rows.....

=SUM(A1:INDEX(C1:C100,SMALL(IF(A1:A100<>"",ROW(A1:A100-ROW(A1)+1),3)))

confirmed with CTRL+SHIFT+ENTER

 

[kbasarab]

For some reason that didn't work Daddy. I'm getting a formula error
but it won't say where. I changed it to fit where the data exists at
starting at B5 and ending at D40

=SUM(B5:INDEX(D540,SMALL(IF(B5:B40<>"",ROW(B5:B40-ROW(B5)+1),3)))

I"m also entering it using CTRL+SHIFT+ENTER.

 

[Biff]

It's missing a " ) ", also, you wanted the AVERAGE, right?

=AVERAGE(B5:INDEX(D540,SMALL(IF(B5:B40<>"",ROW(B5:B40)-ROW(B5)+1),3)))

Make sure you enter it as an array! CTRL,SHIFT,ENTER (not just ENTER)

Biff

 

[kbasarab]

Thanks to everyone. I got it working although not exactly sure how it
works but it is working. I did have to change it to grab 7 rows
instead of three but it works great. Thanks again.

 

[kbasarab]

Just in addition to this I wanted to thank everyone again. I released
the first version of the spreadsheet I was trying to find the answer to
this for and its available below if anyone is interested in seeing it.
It is a spreadsheet to keep track of individual bowling statistics for
a team of bowlers. Thanks again for how great everyone is here and I'm
going to try and come by more often and help others as well.

http://www.kbasarab.com/blog//index.php?entryid=141