Page 1 of 1
Function to find average of data in a column
Posted: Fri Apr 21, 2017 4:53 am
by axljerry123
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?
Re: Function to find average of data in a column
Posted: Fri Apr 21, 2017 6:26 am
by Zizi64
Code: Select all
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
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.
viewtopic.php?f=5&t=7915
http://www.pitonyak.org/oo.php
viewtopic.php?f=20&t=88415
Re: Function to find average of data in a column
Posted: Fri Apr 21, 2017 2:49 pm
by Villeroy
Your class mate was here already:
viewtopic.php?f=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.