## 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!