×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

need help: how to multiply two array in excel

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

Any problems, please ask

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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close