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.

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

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
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.

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

Don Guillett wrote:
Does each column that has values have exactly 3 values each?

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
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.

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


with @tiscali.co.uk


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

Don Guillett wrote:
Does each column that has values have exactly 3 values each?

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
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.

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

"Sandy Mann" wrote:

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


with @tiscali.co.uk


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

Don Guillett wrote:
Does each column that has values have exactly 3 values each?

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
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.

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(D5:D40,SMALL(IF(B5:B40<"",ROW(B5:B4 0-ROW(B5)+1),3)))

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


daddylonglegs wrote:
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

"Sandy Mann" wrote:

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


with @tiscali.co.uk


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

Don Guillett wrote:
Does each column that has values have exactly 3 values each?

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
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.