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