×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# 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

### RE: need help: how to multiply two array in excel

The VBA function is not available for arrays?

If not, perhaps turn the VBA into an Excel addin first.

### RE: need help: how to multiply two array in excel

Since the number of rows in Lambda is less than the number of columns in Temperature I'm guessing that MMult() (which does a matrix multiplication) is not really what you want.

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

Public Function Planck(Lambda As Variant, Temperature As Variant) As Variant

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

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: need help: how to multiply two array in excel

(OP)
IDS thanks!

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

(OP)
IDS
great job :)
thank you!

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!