need help: how to multiply two array in excel
need help: how to multiply two array in excel
(OP)
help
i have x and y array's i want to multiply. somthing like MMULT(Array1;Array2) but this time using vba function called planck()
-------------------------------------------------
Public Function Planck(Lambda As Double, Temperature As Double) As Double
Dim C1 As Double, C2 As Double, Eee As Double
C1 = 374200000#
C2 = 14390#
Eee = 2.718281828
If Lambda * Temperature < 200 Then
Planck = 1E-16
Else
Planck = C1 / (Lambda ^ 5 * (Eee ^ (C2 / (Lambda * Temperature)) - 1#))
End If
End Function
-------------------------------------------------
xArray=B1:P1
x_vals=(0.1,0.15,0.2,0.3,0.4,0.7,1,2,4,6,10,20,40,60,100)
and
yArray=A2:A8
y_vals=(50,100,300,800,1000,2000,5800)
I can do it with very long way but i am wondering if someone know a function to solve this so I can simply select on B2:P8 and Ctrl+shift+enter and get the table calculated.
ps. i get the vba code from:
[faculty.virginia.edu/ribando/modules/xls/VBAPrimer.pdf]
and i use excel 2002
i have x and y array's i want to multiply. somthing like MMULT(Array1;Array2) but this time using vba function called planck()
-------------------------------------------------
Public Function Planck(Lambda As Double, Temperature As Double) As Double
Dim C1 As Double, C2 As Double, Eee As Double
C1 = 374200000#
C2 = 14390#
Eee = 2.718281828
If Lambda * Temperature < 200 Then
Planck = 1E-16
Else
Planck = C1 / (Lambda ^ 5 * (Eee ^ (C2 / (Lambda * Temperature)) - 1#))
End If
End Function
-------------------------------------------------
xArray=B1:P1
x_vals=(0.1,0.15,0.2,0.3,0.4,0.7,1,2,4,6,10,20,40,60,100)
and
yArray=A2:A8
y_vals=(50,100,300,800,1000,2000,5800)
I can do it with very long way but i am wondering if someone know a function to solve this so I can simply select on B2:P8 and Ctrl+shift+enter and get the table calculated.
ps. i get the vba code from:
[faculty.virginia.edu/ribando/modules/xls/VBAPrimer.pdf]
and i use excel 2002





RE: need help: how to multiply two array in excel
If not, perhaps turn the VBA into an Excel addin first.
RE: need help: how to multiply two array in excel
Assuming you want to return an array of all the values of Planck for every combination of Lambda and Temperature, the code below will do the job:
CODE
Dim C1 As Double, C2 As Double, Eee As Double, NumRows As Long, NumCols As Long, PlanckA() As Double
Dim i As Long, j As Long
If TypeName(Lambda) = "Range" Then Lambda = Lambda.Value2
If TypeName(Temperature) = "Range" Then Temperature = Temperature.Value2
NumRows = UBound(Lambda)
NumCols = UBound(Temperature, 2)
ReDim PlanckA(1 To NumRows, 1 To NumCols)
C1 = 374200000#
C2 = 14390#
Eee = 2.718281828
For i = 1 To NumRows
For j = 1 To NumCols
If Lambda(i, 1) * Temperature(1, j) < 200 Then
PlanckA(i, j) = 1E-16
Else
PlanckA(i, j) = C1 / (Lambda(i, 1) ^ 5 * (Eee ^ (C2 / (Lambda(i, 1) * Temperature(1, j))) - 1#))
End If
Next j
Next i
Planck = PlanckA
End Function
to return the full array:
Enter the function in cell B2:
=Planck(A2:A8, B1:P1)
Select the range A2:P8
Press F2
Press Ctrl-Shift-Enter
Any problems, please ask
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: need help: how to multiply two array in excel
This vba function is work perfectly! and works something like MMULT() in spite of unequal number of rows and columns needed to multiply the matrix. :)
Only this function gives quite different outcome than the original function. :(
(Please see excel attachment with both tables and corresponding log-log plot)
Since I am very new to vba it is not easy to trace vba error. Well the formula by itself is oky. So I think something is wrong with the vba code.
Any suggestions how to get this straight?
RE: need help: how to multiply two array in excel
I assumed that your x_vals were temperature and the y_vals were lambda, but it seems that it should have been the other way round.
Corrected function is attached.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: need help: how to multiply two array in excel
great job :)
thank you!