## Function to find average of data in a column

Creating a macro - Writing a Script - Using the API

### Function to find average of data in a column

Hi

I'd like to know how to create a function average of a section of data in column A.
For example, a call of  function ComputeAverage(4,10) would find the average of the cells from A4 to A10

This is the code I've written:
Function ComputeAverage(firstRow As Long, _
lastRow As Long) As Double

Dim i as Long
For i = 2 to 23
firstRow = ActiveSheet.Cells(i,1) + firstRow
Next i
Dim j as Long
For j = 2 to 23
lastRow = ActiveSheet.Cells(j,1) + lastRow
Next j
ComputeAverage = (firstRow+lastRow)/(j-i)
End Function

This code doesn't work...So can somebody help me?
OpenOffice 4.1.3
axljerry123

Posts: 2
Joined: Tue Apr 18, 2017 2:16 pm

### Re: Function to find average of data in a column

Code: Select all   Expand viewCollapse view
`Function ComputeAverage(firstRow As Long, lastRow As Long) As Double Dim i as Long   For i = 2 to 23      firstRow = ActiveSheet.Cells(i,1) + firstRow   Next i Dim j as Long   For j = 2 to 23      lastRow = ActiveSheet.Cells(j,1) + lastRow   Next j ComputeAverage = (firstRow+lastRow)/(j-i)End Function`

This code seems as a MS VBA code snippet.
The StarBasic+API functions are not compatibble with the MS VBA.
You must rewrite your MS VBA macrso functions based on the API functions of the AOO/LO.

And why you used two "For..." cycles to compute the average value of ONE one dimensional cell range?
Why you used constans parameters for the FOR statement, when you need compute a value between two variable row numbers?
Maybe I misunderstood it: you want compute the average of the values in one column between the rows given by the input parameters?
And why you need a macro for computing a simple average value? There is a cell function named AVERAGE() for this task.

Code: Select all   Expand viewCollapse view
`Function ComputeAverage(firstRow As Long, lastRow As Long) As Double Dim i as Long Dim MySum az Double Dim oDoc as object Dim oSheet as object Dim oCell as object   MySum = 0   For i = FirstRow to LastRow      oCell = "put here the API functions to get the actual cell based on the row numbers and the actual sheet, and the actual document..."      MySum = MySum + oCell.Value   Next i ComputeAverage = MySum/(LastRow-FirstRow) End Function`

You MUST study the API functions for control the documents, rows, cells by your macro code. See the free books: Andrew Pitonyak's books will give you excellent descriptions and examples. And you can find similar examples in this Forum too.
https://forum.openoffice.org/en/forum/v ... f=5&t=7915
http://www.pitonyak.org/oo.php
https://forum.openoffice.org/en/forum/v ... 20&t=88415
Last edited by Zizi64 on Fri Apr 21, 2017 5:35 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO4.4.7, LO5.4.6 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.0.2 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 6472
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: Function to find average of data in a column

Your class mate was here already: https://forum.openoffice.org/en/forum/v ... 20&t=88415 and he was not satisfied with my working average function in 4 lines.

If you want somebody to do your VB(A) homework, then you are on the wrong forum for two reasons:
1) This office suite has a limited capability to interprete/translate VBA. I would not say that it supports support VBA actually.
2) We are not here to do anybody else's homework.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x

Villeroy
Volunteer

Posts: 25158
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany