software

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.

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?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.quot;Dave Oquot; wrote:

gt; Assuming the six rows you want to average are in columns A-F, this
gt; formula works:
gt; =SUM(A2:F2)/SUMPRODUCT(--(ISNUMBER(A2:F2)))
gt;
gt; This adds columns A thru F (blanks and alphas are ignored), then counts
gt; and divides by the number of numeric entries in the range.
gt;
gt; Does that do it for you?
gt;
gt;

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(quot;a1quot;).Select 'change this starting address as appropriate

Do Until ActiveCell.Address = quot;$A$quot; amp; RowCount 1
For K = 0 To ColumnCount - 1
If IsNumeric(ActiveCell.Offset(0, K).Value) And
ActiveCell.Offset(0, K).Value lt;gt; quot;quot; 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(quot;evquot; amp; Selection.Row).Value = LineTotal
Range(quot;ewquot; amp; Selection.Row).Value = Elements
Range(quot;exquot; amp; Selection.Row).Formula = quot;=evquot; amp; Selection.Row amp; quot;/ewquot;
amp; Selection.Row

Elements = 0
LineTotal = 0
UpTo6 = 0

ActiveCell.Offset(1, 0).Select

Loop

End SubI'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) lt;= SMALL(IF(ISNUMBER(B1:IV1), COLUMN(B1:IV1),
9E 307), 6)), --(IF(ISNUMBER(B1:IV1), COLUMN(B1:IV1), 9E 307) lt; 9E 307),
B1:IV1) / MIN(6, COUNT(B1:IV1)), quot;N/Aquot;)In article gt;,
quot;Mike A. M.quot; gt; wrote:

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

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:IV1gt;0,COLUMN(
B1:IV1)-COLUMN(B1) 1)),MIN(COUNTIF(B1:IV1,quot;gt;0quot;),6)))gt;0,B1: INDEX(B1:IV1,SM
ALL(IF(ISNUMBER(B1:IV1),IF(B1:IV1gt;0,COLUMN(B1:IV1)-COLUMN(B1) 1)),MIN(COU
NTIF(B1:IV1,quot;gt;0quot;),6)))))

or

=AVERAGE(SUBTOTAL(9,OFFSET(B1:IV1,,SMALL(IF(ISNUMB ER(B1:IV1),IF(B1:IV1gt;0,
COLUMN(B1:IV1)-COLUMN(B1))),ROW(INDIRECT(quot;1:quot;amp;MIN(COUNTIF(B1:IV1, quot;gt;0quot;),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 gt;,
quot;Mike A. M.quot; gt; wrote:

gt; Row - 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 .................
gt; Data xx 30 31 32 44 xx xx xx 32 30 xx 21 xx xx xx xx ..........
gt; xx xx xx xx xx 32 xx 12 xx xx xx xx xx xx xx xx xx xx
gt; ................
gt; xx xx 21 31 21 xx xx xx xx xx 34 34 34 54 21 23 35 ...............
gt;
gt; where xx is possibly blank,zero, alpha etc.
gt; Some rows will have less than six values and some more
gt; Those with less than six I just want the average
gt; Those with more than 6 I want the average of the first 6
gt; There could be up to 150 entries in each row with various inputs.
gt;
gt; thanks
gt; Mike A. M.

Thanks JE
--
Mike A. M.quot;JE McGimpseyquot; wrote:

gt; I'm *sure* there's an easier way, but this is a quick and dirty way
gt; (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
gt;
gt; =IF(COUNT(B1:IV1), SUMPRODUCT(--(IF(ISNUMBER(B1:IV1),
gt; COLUMN(B1:IV1), 9E 307) lt;= SMALL(IF(ISNUMBER(B1:IV1), COLUMN(B1:IV1),
gt; 9E 307), 6)), --(IF(ISNUMBER(B1:IV1), COLUMN(B1:IV1), 9E 307) lt; 9E 307),
gt; B1:IV1) / MIN(6, COUNT(B1:IV1)), quot;N/Aquot;)
gt;
gt;
gt; In article gt;,
gt; quot;Mike A. M.quot; gt; wrote:
gt;
gt; gt; I want average the first six numbers in a series of rows.
gt; gt; The series may include blanks, zeros, alpha charachters.
gt; gt; There may not be six numbers in the series
gt; gt; If there are less than six I still want the average , if there are more than
gt; gt; six, I just want to average the first six.
gt; gt; I have tried UDF methods and VB code, but can't get the right result.
gt; gt; Any sane suggestions?
gt; gt; Thnaks
gt;

Dave O

Spot on, this seems to work fine (there are some strange quot;additionalquot;
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.quot;Dave Oquot; wrote:

gt; I could only figure this out with VBA code. This is adjustable for the
gt; number of expected columns and rows, but then the results are hardcoded
gt; into column EV, the total for the line, EW, the number of elements, and
gt; EX, the formula the derives the average.
gt;
gt; Sub Avg6()
gt; Dim LineTotal As Double
gt; Dim Elements As Byte
gt; Dim UpTo6 As Byte
gt; Dim ColumnCount As Byte
gt; Dim RowCount As Byte
gt; Dim K As Byte
gt;
gt; ColumnCount = 150 'adjust as necessary
gt; RowCount = 2 'adjust as necessary
gt;
gt; Range(quot;a1quot;).Select 'change this starting address as appropriate
gt;
gt; Do Until ActiveCell.Address = quot;$A$quot; amp; RowCount 1
gt; For K = 0 To ColumnCount - 1
gt; If IsNumeric(ActiveCell.Offset(0, K).Value) And
gt; ActiveCell.Offset(0, K).Value lt;gt; quot;quot; Then
gt; Elements = Elements 1
gt; LineTotal = LineTotal ActiveCell.Offset(0, K).Value
gt; UpTo6 = UpTo6 1
gt; If UpTo6 = 6 Then GoTo Found6:
gt; End If
gt; Next K
gt;
gt; Found6:
gt; Range(quot;evquot; amp; Selection.Row).Value = LineTotal
gt; Range(quot;ewquot; amp; Selection.Row).Value = Elements
gt; Range(quot;exquot; amp; Selection.Row).Formula = quot;=evquot; amp; Selection.Row amp; quot;/ewquot;
gt; amp; Selection.Row
gt;
gt; Elements = 0
gt; LineTotal = 0
gt; UpTo6 = 0
gt;
gt; ActiveCell.Offset(1, 0).Select
gt;
gt; Loop
gt;
gt; End Sub
gt;
gt;

Thanks Domenic
--
Mike A. M.quot;Domenicquot; wrote:

gt; Different interpretation...
gt;
gt; Assuming that you want to exclude zero values as well, try...
gt;
gt; =AVERAGE(IF(B1:INDEX(B1:IV1,SMALL(IF(ISNUMBER(B1:I V1),IF(B1:IV1gt;0,COLUMN(
gt; B1:IV1)-COLUMN(B1) 1)),MIN(COUNTIF(B1:IV1,quot;gt;0quot;),6)))gt;0,B1: INDEX(B1:IV1,SM
gt; ALL(IF(ISNUMBER(B1:IV1),IF(B1:IV1gt;0,COLUMN(B1:IV1)-COLUMN(B1) 1)),MIN(COU
gt; NTIF(B1:IV1,quot;gt;0quot;),6)))))
gt;
gt; or
gt;
gt; =AVERAGE(SUBTOTAL(9,OFFSET(B1:IV1,,SMALL(IF(ISNUMB ER(B1:IV1),IF(B1:IV1gt;0,
gt; COLUMN(B1:IV1)-COLUMN(B1))),ROW(INDIRECT(quot;1:quot;amp;MIN(COUNTIF(B1:IV1, quot;gt;0quot;),6)
gt; ))),,1)))
gt;
gt; Both formulas need to be confirmed with CONTROL SHIFT ENTER, not just
gt; ENTER. The latter includes volatile functions and will prolong the
gt; re-calculation process.
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; quot;Mike A. M.quot; gt; wrote:
gt;
gt; gt; Row - 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 .................
gt; gt; Data xx 30 31 32 44 xx xx xx 32 30 xx 21 xx xx xx xx ..........
gt; gt; xx xx xx xx xx 32 xx 12 xx xx xx xx xx xx xx xx xx xx
gt; gt; ................
gt; gt; xx xx 21 31 21 xx xx xx xx xx 34 34 34 54 21 23 35 ...............
gt; gt;
gt; gt; where xx is possibly blank,zero, alpha etc.
gt; gt; Some rows will have less than six values and some more
gt; gt; Those with less than six I just want the average
gt; gt; Those with more than 6 I want the average of the first 6
gt; gt; There could be up to 150 entries in each row with various inputs.
gt; gt;
gt; gt; thanks
gt; gt; Mike A. M.
gt;