Average only first number of actual values
 

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.

Average only first number of actual values
 

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.

Average only first number of actual values
 

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.

Average only first number of actual values
 

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.

Average only first number of actual values
 

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.

Average only first number of actual values
 

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.

Average only first number of actual values
 

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

=AVERAGE(B5:INDEX(D5:D40,SMALL(IF(B5:B40<"",ROW(B 5:B40)-ROW(B5)+1),3)))

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

Biff

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

Average only first number of actual values
 

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.


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

=AVERAGE(B5:INDEX(D5:D40,SMALL(IF(B5:B40<"",ROW(B 5:B40)-ROW(B5)+1),3)))

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

Biff

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

Average only first number of actual values
 

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


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


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

=AVERAGE(B5:INDEX(D5:D40,SMALL(IF(B5:B40<"",ROW(B 5:B40)-ROW(B5)+1),3)))

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

Biff

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