Is this macro's purpose achievable with a regular Calc function?

[Pum]

Basically I have been scratching my head trying to accomplish the following:

I have a table full of data, and I want to calculate the highest average of all the rows of that table

So for example if I had

1    4    5    7
3    4    2    6
2    8    1    5
6    3    4    1

I would need to calculate the 4 averages, one per row, and then get the highest one.

(So the averages would be 4.25 , 3.75 , 4 , 3.5 ; and it would choose 4.25 for being the highest)

Keep in mind that the table is periodically getting new data so I cant check a fixed set of rows (I can check a large set of rows and just ignore blank rows)

Also I know a simple solution would be to create a column which would hold all the averages and just get the highest one from there, but the idea is not to create more tables because I need to do way more operations than the average I used as an example here.

Since I couldn’t come up with a solution, I looked into macros and got this (all the variables and declarations are excluded from the paste):

Value = -1000
i = 0
data = range.GetDataArray() // range is a Cell range
while data(i)(0) <> ""
	CurrValue = svc.callFunction("Average",data(i)) // svc is createUnoService("com.sun.star.sheet.FunctionAccess")
	if Value < CurrValue Then
		Value = CurrValue
	End if
	i = i + 1
WEnd

AFX = ThisComponent.Sheets(0).getCellRangeByName("AF1")
AFX.Value = Value

This works, but I want to know if this result would be achievable with built-in Calc functions.

Thanks.

[Lupp]

INDEX(Matrix;RowNumber;) called for array-evaluation can return a single row, but no function can return an array of rows. That’s a fundamental restriction. Whatever a function returns to a sheet is either a scalar or a 2D-array. Also the “single row” in fact is an array (1 To 1, 1 To n). And there is no standard function to calculate averages or whatever statistical measure rowwise.
You can force rowwise calculation by user functions as exemplified below.
(Precalculated arrays are passed1-based to the functions.)

Function rowwiseStat(pArray, pStatFct As String)
fa = createUnoService("com.sun.star.sheet.FunctionAccess")
Dim r()
rowwiseStat = r
If NOT IsArray(pArray) Then Exit Function
ur = Ubound(pArray, 1)
uc = Ubound(pArray, 2)
Redim r(1 To ur, 1 To 1)
For i = 1 To ur
iRow = fa.callFunction("INDEX", Array(pArray, i, ))
Select Case Ucase(pStatFct)
  Case "AVERAGE"
    n = fa.callFunction("COUNT", Array(iRow))
    If n = 0 Then 
      r(i, 1) = "<empty>"
    Else
      r(i, 1) = fa.callFunction("AVERAGE", Array(iRow))
    End If
  Case Else REM unknown. Insert additional case handling if needed.
    Redim r()
    rowwiseStat = r
    Exit Function
End Select
Next i
rowwiseStat = r
End Function