average on qty
 

I am trying to make a function that makes the average of number of inputs
based on a bigger input qty array, to keep dividing by the same number l need
to throw out of the sum the lowest index to divide by the same number, this
is part of the code I have but I don't know how to make an array output, it
does not work.

Function my_avg(data(), j As Integer) As Double

' Function passes an array of data(), and returns an array of data after
' calculates my_avg, which is the sum of data divided by number of data
' my_avg begins with a sum of one data up to a max of j, if data input is
' higher than j, division will be done to j, and sum will throw out the
' lower index.

my_avg = 0

For i = 0 To UBound(data)
If i < 15 Then
my_avg = (my_avg + data(i)) / (i + 1)
If i = 15 Then
my_avg = (my_avg + data(i)) / j
If i 15 Then
my_avg = (my_avg + data(i) - data(i - 15)) / j
End If
Next i
End Function

--
Tajin

average on qty
 

I'm not sure exactly what you are trying to do but I can tell you that you
aren't returning an ARRAY of data, you are simply returning one value. I
don't know what you are doing with the value "my_avg" in the calling routine,
but if you want it to return it as an array I think you have to declare the
function like this:

Function my_avg(data(), j As Integer) As Double()

ReDim my_avg(UBound(data))

For i = 0 To UBound(data)
If i < 15 Then
my_avg(i) = (my_avg + data(i)) / (i + 1)
If i = 15 Then
my_avg(i) = (my_avg + data(i)) / j
If i 15 Then
my_avg(i) = (my_avg + data(i) - data(i - 15)) / j
End If
Next i

End Function


"Tajin" wrote:

I am trying to make a function that makes the average of number of inputs
based on a bigger input qty array, to keep dividing by the same number l need
to throw out of the sum the lowest index to divide by the same number, this
is part of the code I have but I don't know how to make an array output, it
does not work.

Function my_avg(data(), j As Integer) As Double

' Function passes an array of data(), and returns an array of data after
' calculates my_avg, which is the sum of data divided by number of data
' my_avg begins with a sum of one data up to a max of j, if data input is
' higher than j, division will be done to j, and sum will throw out the
' lower index.

my_avg = 0

For i = 0 To UBound(data)
If i < 15 Then
my_avg = (my_avg + data(i)) / (i + 1)
If i = 15 Then
my_avg = (my_avg + data(i)) / j
If i 15 Then
my_avg = (my_avg + data(i) - data(i - 15)) / j
End If
Next i
End Function

--
Tajin

average on qty
 

You are right, thanks, but still does not work. This is what I am trying to
achieve.
Let's say we have on A1 to A20 some data I want to average, and I want to
average on 4, my function will be inserted on B1 to B20, cell B1 has same
value as A1, B2 has (A1+A2)/2, B3 has (A1+A2+A3/3, B4 has ( A1+A2+A3+A4)/4,
B5 has (A2+A3+A4+A5)/4, .... B20 has (A17+A18+A19+A20)/4, I made some
correction to the code to adapt to qty of 4, this number is the same for the
whole array output

Function my_avg(data(), j As Integer) As Double()

ReDim my_avg(UBound(data))

For i = 0 To UBound(data)
If i < j - 1 Then
my_avg(i) = (my_avg + data(i)) / (i + 1)
If i = j - 1 Then
my_avg(i) = (my_avg + data(i)) / j
If i j - 1 Then
my_avg(i) = (my_avg + data(i) - data(i - j)) / j
End If
Next i
End Function




"Charlie" wrote:

I'm not sure exactly what you are trying to do but I can tell you that you
aren't returning an ARRAY of data, you are simply returning one value. I
don't know what you are doing with the value "my_avg" in the calling routine,
but if you want it to return it as an array I think you have to declare the
function like this:

Function my_avg(data(), j As Integer) As Double()

ReDim my_avg(UBound(data))

For i = 0 To UBound(data)
If i < 15 Then
my_avg(i) = (my_avg + data(i)) / (i + 1)
If i = 15 Then
my_avg(i) = (my_avg + data(i)) / j
If i 15 Then
my_avg(i) = (my_avg + data(i) - data(i - 15)) / j
End If
Next i

End Function


"Tajin" wrote:

I am trying to make a function that makes the average of number of inputs
based on a bigger input qty array, to keep dividing by the same number l need
to throw out of the sum the lowest index to divide by the same number, this
is part of the code I have but I don't know how to make an array output, it
does not work.

Function my_avg(data(), j As Integer) As Double

' Function passes an array of data(), and returns an array of data after
' calculates my_avg, which is the sum of data divided by number of data
' my_avg begins with a sum of one data up to a max of j, if data input is
' higher than j, division will be done to j, and sum will throw out the
' lower index.

my_avg = 0

For i = 0 To UBound(data)
If i < 15 Then
my_avg = (my_avg + data(i)) / (i + 1)
If i = 15 Then
my_avg = (my_avg + data(i)) / j
If i 15 Then
my_avg = (my_avg + data(i) - data(i - 15)) / j
End If
Next i
End Function

--
Tajin

average on qty
 

Ok, well here's how I did it
===============================

Dim DataIn(20) As Double
Dim DataOut() As Double

my_avg DataIn, DataOut, 4

===============================
Sub my_avg(DataIn() As Double, DataOut() As Double, nAvg As Long)
'
' declare a Sub since we are not returning my_avg to the calling routine
'
' I prefer to use Long everywhere even if I "know" I will not need it
' (never have to worry about integer overflow if something changes,
' i.e. all of a sudden I'm processing larger quantities of data
' than I originally planned for)
'
Dim ipos As Long
Dim lpos As Long
Dim nItems As Long
Dim dAvg As Double
Dim RunningTotal As Double
'
' I prefer to use one-based arrays, that way element(1) equates to Row1
' etc., makes more sense to me and I'm less likely to make a coding error
' (put Option Base 1 in the General Declarations section)
'
nItems = UBound(DataIn)
ReDim DataOut(nItems) ' or ReDim DataOut(1 To nItems)
'
' set up to process the array elements from first position to
' last position (where first position is either 1 or last position
' minus nAvg plus one)
'
ipos = 1
RunningTotal = 0
'
' set the dAvg divisor to be 1, 2, 3, etc. but not greater than nAvg
' and increment the beginning pointer when the ending pointer moves
' beyond nAvg
'
For lpos = 1 To nItems
If lpos <= nAvg Then
dAvg = lpos
Else
dAvg = nAvg
ipos = ipos + 1
End If
'
' accumulate the running total but remove prior values as they "fall off"
' the beginning of the array
'
RunningTotal = RunningTotal + DataIn(lpos)
If ipos 1 Then RunningTotal = RunningTotal - DataIn(ipos - 1)
'
' calc average for this element
'
DataOut(lpos) = RunningTotal / dAvg
Next lpos
'
End Sub
===============================
This routine will return an array containing the desired values. You can
then fill your cells, or you can modify the sub to directly write to the
cells.


"Tajin" wrote:

You are right, thanks, but still does not work. This is what I am trying to
achieve.
Let's say we have on A1 to A20 some data I want to average, and I want to
average on 4, my function will be inserted on B1 to B20, cell B1 has same
value as A1, B2 has (A1+A2)/2, B3 has (A1+A2+A3/3, B4 has ( A1+A2+A3+A4)/4,
B5 has (A2+A3+A4+A5)/4, .... B20 has (A17+A18+A19+A20)/4, I made some
correction to the code to adapt to qty of 4, this number is the same for the
whole array output

Function my_avg(data(), j As Integer) As Double()

ReDim my_avg(UBound(data))

For i = 0 To UBound(data)
If i < j - 1 Then
my_avg(i) = (my_avg + data(i)) / (i + 1)
If i = j - 1 Then
my_avg(i) = (my_avg + data(i)) / j
If i j - 1 Then
my_avg(i) = (my_avg + data(i) - data(i - j)) / j
End If
Next i
End Function




"Charlie" wrote:

I'm not sure exactly what you are trying to do but I can tell you that you
aren't returning an ARRAY of data, you are simply returning one value. I
don't know what you are doing with the value "my_avg" in the calling routine,
but if you want it to return it as an array I think you have to declare the
function like this:

Function my_avg(data(), j As Integer) As Double()

ReDim my_avg(UBound(data))

For i = 0 To UBound(data)
If i < 15 Then
my_avg(i) = (my_avg + data(i)) / (i + 1)
If i = 15 Then
my_avg(i) = (my_avg + data(i)) / j
If i 15 Then
my_avg(i) = (my_avg + data(i) - data(i - 15)) / j
End If
Next i

End Function


"Tajin" wrote:

I am trying to make a function that makes the average of number of inputs
based on a bigger input qty array, to keep dividing by the same number l need
to throw out of the sum the lowest index to divide by the same number, this
is part of the code I have but I don't know how to make an array output, it
does not work.

Function my_avg(data(), j As Integer) As Double

' Function passes an array of data(), and returns an array of data after
' calculates my_avg, which is the sum of data divided by number of data
' my_avg begins with a sum of one data up to a max of j, if data input is
' higher than j, division will be done to j, and sum will throw out the
' lower index.

my_avg = 0

For i = 0 To UBound(data)
If i < 15 Then
my_avg = (my_avg + data(i)) / (i + 1)
If i = 15 Then
my_avg = (my_avg + data(i)) / j
If i 15 Then
my_avg = (my_avg + data(i) - data(i - 15)) / j
End If
Next i
End Function

--
Tajin

average on qty
 

Give this function a try. It must be "array entered" in the worksheet. For
example, select cells B1:B20 and then type the formula: =myavg(A1:A20,4)
Finish the entry with: Ctrl Shift Enter

The function only works for a single column of source data and the formulas
must be entered down a column and not across a row to get your desired
result.

Troy


Function myavg(DataRange As Range, MaxN As Long) As Variant
Dim lngR As Long
Dim lngI As Long
Dim dblNext As Double
Dim dblSum As Double
Dim vArr() As Variant

'Verify that the DataRange is a single column.
If DataRange.Columns.Count 1 Then
'Return the "#N/A" error to the cells.
myavg = CVErr(xlErrNA)
Exit Function
End If

lngR = DataRange.Rows.Count
For lngI = 1 To lngR
ReDim Preserve vArr(1 To lngI)
dblNext = DataRange(lngI, 1).Value
dblSum = dblSum + dblNext
If lngI <= MaxN Then
vArr(lngI) = dblSum / lngI
Else
dblSum = dblSum - DataRange(lngI - MaxN, 1).Value
vArr(lngI) = dblSum / MaxN
End If
Next lngI

myavg = Application.Transpose(vArr)
End Function


"Tajin" wrote in message
...
You are right, thanks, but still does not work. This is what I am trying
to
achieve.
Let's say we have on A1 to A20 some data I want to average, and I want to
average on 4, my function will be inserted on B1 to B20, cell B1 has same
value as A1, B2 has (A1+A2)/2, B3 has (A1+A2+A3/3, B4 has (
A1+A2+A3+A4)/4,
B5 has (A2+A3+A4+A5)/4, .... B20 has (A17+A18+A19+A20)/4, I made some
correction to the code to adapt to qty of 4, this number is the same for
the
whole array output

Function my_avg(data(), j As Integer) As Double()

ReDim my_avg(UBound(data))

For i = 0 To UBound(data)
If i < j - 1 Then
my_avg(i) = (my_avg + data(i)) / (i + 1)
If i = j - 1 Then
my_avg(i) = (my_avg + data(i)) / j
If i j - 1 Then
my_avg(i) = (my_avg + data(i) - data(i - j)) / j
End If
Next i
End Function