I want to lose Array3 as a variable and make x = to the #
of cells in Array1 as long as Array1 and Array2 have the
same number of cells. Also I am only using a single
column for Array1 and Array2. This is like sumproduct
function in excel only different.

Function Deconvoloution(Array1 As Range, Array2 As Range,
Array3 As Integer)

Dim x As Integer
Dim y As Integer
Dim Final
Final = 0
x = Array3
y = 1

Do Until x = 0
Final = Final + (Array1(y) * Array2(x))
x = x - 1
y = y + 1
Loop

Deconvoloution = Final
End Function

Since Array1 is a one column range its number of elements (rows) would be
equal to Array1.Rows.Count. So I think you can use that as x instead of
passing Array3.

--
Jim Rech
Excel MVP

Josh,

Like Jim said, use the Range object's row counting capability, and then you
can ditch the Array3 variable.

Function Deconvoloution(Array1 As Range, Array2 As Range)
Dim x As Integer
Dim y As Integer
Dim Final

If Array1.Rows.Count < Array2.Rows.Count Then
Msgbox "Mismatched number of rows in passed Ranges.", vbCritical,
"Parameter Error"
Exit Function
Else
x = Array1.Rows.Count
End If

Final = 0
y = 1

Do Until x = 0
Final = Final + (Array1(y) * Array2(x))
x = x - 1
y = y + 1
Loop

Deconvoloution = Final

End Function

Just out of curiousity; what is the purpose of this kind of inverted
SumProduct that you're doing?

Thanks,
Mark

===================================

"Josh" wrote in message
...
I want to lose Array3 as a variable and make x = to the #
of cells in Array1 as long as Array1 and Array2 have the
same number of cells. Also I am only using a single
column for Array1 and Array2. This is like sumproduct
function in excel only different.

Function Deconvoloution(Array1 As Range, Array2 As Range,
Array3 As Integer)

Dim x As Integer
Dim y As Integer
Dim Final
Final = 0
x = Array3
y = 1

Do Until x = 0
Final = Final + (Array1(y) * Array2(x))
x = x - 1
y = y + 1
Loop

Deconvoloution = Final
End Function

The pourpose of this is Hydrology (deconvoloution is a
hydrology term).
We have a column in Excel, for example [[1] [2] [3]]
and are muliplying it by a column [[a] [b] [c]]
but we really want the matricies multiplied like:
1 0 0 a 1*a
1 2 0 * b = 1*b+2*a
1 2 3 c 1*c+2*b+3*a
of course our colums include 100's of values

Thank you for your help.
-----Original Message-----
Josh,

Like Jim said, use the Range object's row counting
capability, and then you
can ditch the Array3 variable.

Function Deconvoloution(Array1 As Range, Array2 As Range)
Dim x As Integer
Dim y As Integer
Dim Final

If Array1.Rows.Count < Array2.Rows.Count Then
Msgbox "Mismatched number of rows in passed Ranges.",
vbCritical,
"Parameter Error"
Exit Function
Else
x = Array1.Rows.Count
End If

Final = 0
y = 1

Do Until x = 0
Final = Final + (Array1(y) * Array2(x))
x = x - 1
y = y + 1
Loop

Deconvoloution = Final

End Function

Just out of curiousity; what is the purpose of this kind
of inverted
SumProduct that you're doing?

Thanks,
Mark

===================================

"Josh" wrote in
message
...
I want to lose Array3 as a variable and make x = to the
#
of cells in Array1 as long as Array1 and Array2 have the
same number of cells. Also I am only using a single
column for Array1 and Array2. This is like sumproduct
function in excel only different.

Function Deconvoloution(Array1 As Range, Array2 As
Range,
Array3 As Integer)

Dim x As Integer
Dim y As Integer
Dim Final
Final = 0
x = Array3
y = 1

Do Until x = 0
Final = Final + (Array1(y) * Array2(x))
x = x - 1
y = y + 1
Loop

Deconvoloution = Final
End Function


.

Hello. I can't tell if you are trying to do Convolution, or Correlation. I
can't tell how far you are trying to pass the Filter (based on the op.) If
you are doing Convolution with 100's (or up to 2048 data points per array),
you may want to consider Excel's FFT. It is under <Tools <Data Analysis.
(Convolution of two equal size arrays...Convolution of n+m-1 must be <= 4096
in Excel) I find it easier to keep the least significant bits at the top of
the array for fft.

If not, perhaps another option for your function...

Function Convoloution(Array1 As Range, Array2 As Range)
'// Full overlap only!
Dim N As Long
Dim j As Long
Dim Final As Variant
Final = 0
N = Array1.Cells.Count + 1

For j = 1 To N - 1
Final = Final + (Array1(j) * Array2(N - j))
Next
Convoloution= Final
End Function

1 0 0 a 1*a
1 2 0 * b = 1*b+2*a
1 2 3 c 1*c+2*b+3*a

Using another program to check, here is a shortcut for Convolution using
Fourier Analysis. Here, it is stopped at full overlap.

ListConvolve[{1, 2, 3}, {a, b, c}, {1, 1}, 0]

a, 2*a + b, 3*a + 2*b + c
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


wrote in message
...
The pourpose of this is Hydrology (deconvoloution is a
hydrology term).
We have a column in Excel, for example [[1] [2] [3]]
and are muliplying it by a column [[a] [b] [c]]
but we really want the matricies multiplied like:
1 0 0 a 1*a
1 2 0 * b = 1*b+2*a
1 2 3 c 1*c+2*b+3*a
of course our colums include 100's of values

Thank you for your help.
-----Original Message-----
Josh,

Like Jim said, use the Range object's row counting
capability, and then you
can ditch the Array3 variable.

Function Deconvoloution(Array1 As Range, Array2 As Range)
Dim x As Integer
Dim y As Integer
Dim Final

If Array1.Rows.Count < Array2.Rows.Count Then
Msgbox "Mismatched number of rows in passed Ranges.",
vbCritical,
"Parameter Error"
Exit Function
Else
x = Array1.Rows.Count
End If

Final = 0
y = 1

Do Until x = 0
Final = Final + (Array1(y) * Array2(x))
x = x - 1
y = y + 1
Loop

Deconvoloution = Final

End Function

Just out of curiousity; what is the purpose of this kind
of inverted
SumProduct that you're doing?

Thanks,
Mark

===================================

"Josh" wrote in
message
...
I want to lose Array3 as a variable and make x = to the
#
of cells in Array1 as long as Array1 and Array2 have the
same number of cells. Also I am only using a single
column for Array1 and Array2. This is like sumproduct
function in excel only different.

Function Deconvoloution(Array1 As Range, Array2 As
Range,
Array3 As Integer)

Dim x As Integer
Dim y As Integer
Dim Final
Final = 0
x = Array3
y = 1

Do Until x = 0
Final = Final + (Array1(y) * Array2(x))
x = x - 1
y = y + 1
Loop

Deconvoloution = Final
End Function


.