Average first n numbers in a range (there may be less than n numbe
 

I want average the first six numbers in a series of rows.
The series may include blanks, zeros, alpha charachters.
There may not be six numbers in the series
If there are less than six I still want the average , if there are more than
six, I just want to average the first six.
I have tried UDF methods and VB code, but can't get the right result.
Any sane suggestions?
Thnaks
--
Mike A. M.

Average first n numbers in a range (there may be less than n numbe
 

Assuming the six rows you want to average are in columns A-F, this
formula works:
=SUM(A2:F2)/SUMPRODUCT(--(ISNUMBER(A2:F2)))

This adds columns A thru F (blanks and alphas are ignored), then counts
and divides by the number of numeric entries in the range.

Does that do it for you?

Average first n numbers in a range (there may be less than n n
 

Dave O, I have not made clear the environment

the data may look like this

Row - 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 .................
Data xx 30 31 32 44 xx xx xx 32 30 xx 21 xx xx xx xx ..........
xx xx xx xx xx 32 xx 12 xx xx xx xx xx xx xx xx xx xx
.................
xx xx 21 31 21 xx xx xx xx xx 34 34 34 54 21 23 35 ...............

where xx is possibly blank,zero, alpha etc.
Some rows will have less than six values and some more
Those with less than six I just want the average
Those with more than 6 I want the average of the first 6
There could be up to 150 entries in each row with various inputs.

thanks
Mike A. M.


"Dave O" wrote:

Assuming the six rows you want to average are in columns A-F, this
formula works:
=SUM(A2:F2)/SUMPRODUCT(--(ISNUMBER(A2:F2)))

This adds columns A thru F (blanks and alphas are ignored), then counts
and divides by the number of numeric entries in the range.

Does that do it for you?

Average first n numbers in a range (there may be less than n n
 

I could only figure this out with VBA code. This is adjustable for the
number of expected columns and rows, but then the results are hardcoded
into column EV, the total for the line, EW, the number of elements, and
EX, the formula the derives the average.

Sub Avg6()
Dim LineTotal As Double
Dim Elements As Byte
Dim UpTo6 As Byte
Dim ColumnCount As Byte
Dim RowCount As Byte
Dim K As Byte

ColumnCount = 150 'adjust as necessary
RowCount = 2 'adjust as necessary

Range("a1").Select 'change this starting address as appropriate

Do Until ActiveCell.Address = "$A$" & RowCount + 1
For K = 0 To ColumnCount - 1
If IsNumeric(ActiveCell.Offset(0, K).Value) And
ActiveCell.Offset(0, K).Value < "" Then
Elements = Elements + 1
LineTotal = LineTotal + ActiveCell.Offset(0, K).Value
UpTo6 = UpTo6 + 1
If UpTo6 = 6 Then GoTo Found6:
End If
Next K

Found6:
Range("ev" & Selection.Row).Value = LineTotal
Range("ew" & Selection.Row).Value = Elements
Range("ex" & Selection.Row).Formula = "=ev" & Selection.Row & "/ew"
& Selection.Row

Elements = 0
LineTotal = 0
UpTo6 = 0

ActiveCell.Offset(1, 0).Select

Loop

End Sub

Average first n numbers in a range (there may be less than n numbe
 

I'm *sure* there's an easier way, but this is a quick and dirty way
(array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=IF(COUNT(B1:IV1), SUMPRODUCT(--(IF(ISNUMBER(B1:IV1),
COLUMN(B1:IV1), 9E+307) <= SMALL(IF(ISNUMBER(B1:IV1), COLUMN(B1:IV1),
9E+307), 6)), --(IF(ISNUMBER(B1:IV1), COLUMN(B1:IV1), 9E+307) < 9E+307),
B1:IV1) / MIN(6, COUNT(B1:IV1)), "N/A")


In article ,
"Mike A. M." wrote:

I want average the first six numbers in a series of rows.
The series may include blanks, zeros, alpha charachters.
There may not be six numbers in the series
If there are less than six I still want the average , if there are more than
six, I just want to average the first six.
I have tried UDF methods and VB code, but can't get the right result.
Any sane suggestions?
Thnaks

Average first n numbers in a range (there may be less than n n
 

Different interpretation...

Assuming that you want to exclude zero values as well, try...

=AVERAGE(IF(B1:INDEX(B1:IV1,SMALL(IF(ISNUMBER(B1:I V1),IF(B1:IV10,COLUMN(
B1:IV1)-COLUMN(B1)+1)),MIN(COUNTIF(B1:IV1,"0"),6)))0,B1: INDEX(B1:IV1,SM
ALL(IF(ISNUMBER(B1:IV1),IF(B1:IV10,COLUMN(B1:IV1)-COLUMN(B1)+1)),MIN(COU
NTIF(B1:IV1,"0"),6)))))

or

=AVERAGE(SUBTOTAL(9,OFFSET(B1:IV1,,SMALL(IF(ISNUMB ER(B1:IV1),IF(B1:IV10,
COLUMN(B1:IV1)-COLUMN(B1))),ROW(INDIRECT("1:"&MIN(COUNTIF(B1:IV1, "0"),6)
))),,1)))

Both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER. The latter includes volatile functions and will prolong the
re-calculation process.

Hope this helps!

In article ,
"Mike A. M." wrote:

Row - 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 .................
Data xx 30 31 32 44 xx xx xx 32 30 xx 21 xx xx xx xx ..........
xx xx xx xx xx 32 xx 12 xx xx xx xx xx xx xx xx xx xx
................
xx xx 21 31 21 xx xx xx xx xx 34 34 34 54 21 23 35 ...............

where xx is possibly blank,zero, alpha etc.
Some rows will have less than six values and some more
Those with less than six I just want the average
Those with more than 6 I want the average of the first 6
There could be up to 150 entries in each row with various inputs.

thanks
Mike A. M.

Average first n numbers in a range (there may be less than n n
 

Thanks JE
--
Mike A. M.


"JE McGimpsey" wrote:

I'm *sure* there's an easier way, but this is a quick and dirty way
(array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=IF(COUNT(B1:IV1), SUMPRODUCT(--(IF(ISNUMBER(B1:IV1),
COLUMN(B1:IV1), 9E+307) <= SMALL(IF(ISNUMBER(B1:IV1), COLUMN(B1:IV1),
9E+307), 6)), --(IF(ISNUMBER(B1:IV1), COLUMN(B1:IV1), 9E+307) < 9E+307),
B1:IV1) / MIN(6, COUNT(B1:IV1)), "N/A")


In article ,
"Mike A. M." wrote:

I want average the first six numbers in a series of rows.
The series may include blanks, zeros, alpha charachters.
There may not be six numbers in the series
If there are less than six I still want the average , if there are more than
six, I just want to average the first six.
I have tried UDF methods and VB code, but can't get the right result.
Any sane suggestions?
Thnaks

Average first n numbers in a range (there may be less than n n
 

Dave O

Spot on, this seems to work fine (there are some strange "additional"
outputs, but I think I have a way of cleaning up the code to work
specifically on the data set I have.
Many thanks

-
Mike A. M.


"Dave O" wrote:

I could only figure this out with VBA code. This is adjustable for the
number of expected columns and rows, but then the results are hardcoded
into column EV, the total for the line, EW, the number of elements, and
EX, the formula the derives the average.

Sub Avg6()
Dim LineTotal As Double
Dim Elements As Byte
Dim UpTo6 As Byte
Dim ColumnCount As Byte
Dim RowCount As Byte
Dim K As Byte

ColumnCount = 150 'adjust as necessary
RowCount = 2 'adjust as necessary

Range("a1").Select 'change this starting address as appropriate

Do Until ActiveCell.Address = "$A$" & RowCount + 1
For K = 0 To ColumnCount - 1
If IsNumeric(ActiveCell.Offset(0, K).Value) And
ActiveCell.Offset(0, K).Value < "" Then
Elements = Elements + 1
LineTotal = LineTotal + ActiveCell.Offset(0, K).Value
UpTo6 = UpTo6 + 1
If UpTo6 = 6 Then GoTo Found6:
End If
Next K

Found6:
Range("ev" & Selection.Row).Value = LineTotal
Range("ew" & Selection.Row).Value = Elements
Range("ex" & Selection.Row).Formula = "=ev" & Selection.Row & "/ew"
& Selection.Row

Elements = 0
LineTotal = 0
UpTo6 = 0

ActiveCell.Offset(1, 0).Select

Loop

End Sub

Average first n numbers in a range (there may be less than n n
 

Thanks Domenic
--
Mike A. M.


"Domenic" wrote:

Different interpretation...

Assuming that you want to exclude zero values as well, try...

=AVERAGE(IF(B1:INDEX(B1:IV1,SMALL(IF(ISNUMBER(B1:I V1),IF(B1:IV10,COLUMN(
B1:IV1)-COLUMN(B1)+1)),MIN(COUNTIF(B1:IV1,"0"),6)))0,B1: INDEX(B1:IV1,SM
ALL(IF(ISNUMBER(B1:IV1),IF(B1:IV10,COLUMN(B1:IV1)-COLUMN(B1)+1)),MIN(COU
NTIF(B1:IV1,"0"),6)))))

or

=AVERAGE(SUBTOTAL(9,OFFSET(B1:IV1,,SMALL(IF(ISNUMB ER(B1:IV1),IF(B1:IV10,
COLUMN(B1:IV1)-COLUMN(B1))),ROW(INDIRECT("1:"&MIN(COUNTIF(B1:IV1, "0"),6)
))),,1)))

Both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER. The latter includes volatile functions and will prolong the
re-calculation process.

Hope this helps!

In article ,
"Mike A. M." wrote:

Row - 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 .................
Data xx 30 31 32 44 xx xx xx 32 30 xx 21 xx xx xx xx ..........
xx xx xx xx xx 32 xx 12 xx xx xx xx xx xx xx xx xx xx
................
xx xx 21 31 21 xx xx xx xx xx 34 34 34 54 21 23 35 ...............

where xx is possibly blank,zero, alpha etc.
Some rows will have less than six values and some more
Those with less than six I just want the average
Those with more than 6 I want the average of the first 6
There could be up to 150 entries in each row with various inputs.

thanks
Mike A. M.