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

M

Mike A. M.

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
 
D

Dave O

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?
 
M

Mike A. M.

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.
 
D

Dave O

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
 
J

JE McGimpsey

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")
 
D

Domenic

Different interpretation...

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

=AVERAGE(IF(B1:INDEX(B1:IV1,SMALL(IF(ISNUMBER(B1:IV1),IF(B1:IV1>0,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:IV1>0,COLUMN(B1:IV1)-COLUMN(B1)+1)),MIN(COU
NTIF(B1:IV1,">0"),6)))))

or

=AVERAGE(SUBTOTAL(9,OFFSET(B1:IV1,,SMALL(IF(ISNUMBER(B1:IV1),IF(B1:IV1>0,
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!
 
M

Mike A. M.

Thanks JE
--
Mike A. M.


JE McGimpsey said:
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")
Click to expand...
 
M

Mike A. M.

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.
 
M

Mike A. M.

Thanks Domenic
--
Mike A. M.


Domenic said:
Different interpretation...

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

=AVERAGE(IF(B1:INDEX(B1:IV1,SMALL(IF(ISNUMBER(B1:IV1),IF(B1:IV1>0,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:IV1>0,COLUMN(B1:IV1)-COLUMN(B1)+1)),MIN(COU
NTIF(B1:IV1,">0"),6)))))

or

=AVERAGE(SUBTOTAL(9,OFFSET(B1:IV1,,SMALL(IF(ISNUMBER(B1:IV1),IF(B1:IV1>0,
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!
Click to expand...
 
Top