PDA

View Full Version : [SOLVED] Multiple arguments used in cell function



nosense123
05-22-2015, 12:36 AM
Hi Guys,

Supose i have range of data in a A3:A10 and I want this to be used as an arguments into calculator that requires input into cell, say B2 and returns value in C3.
I would like to get all values returned for arguments in a3:a10 in b3:b10.

Any suggestions ??
Thanks ,

Yongle
05-22-2015, 01:26 AM
Your question is a little bit vague.
So how about giving us some idea of what you are trying to achieve, using a proper example. Something like:
Column A contains Number of items
Column B contains Price of each item
Column C = Number of items X Price

nosense123
05-22-2015, 01:53 AM
Your question is a little bit vague.
So how about giving us some idea of what you are trying to achieve, using a proper example. Something like:
Column A contains Number of items
Column B contains Price of each item
Column C = Number of items X Price


As said before:

I have a random numbers in a range a2:a10;

I have a function that requires manual input into cell (argument) c1, and returns value in anoher cell- c2.

What I want to achieve is to do macro that will use all numbers from a2:a10 one by one, and place it one by one into c1 and then return value in c2. I want thi to be done for all arguments from a2:a10 and this retuned values to apear in a range b2:b10.

Is hat clear now?
cheers

mancubus
05-22-2015, 02:23 AM
copy the formula in C2 to B2.
Change cell reference C1 to A2.
copy the formula to B3:B10

nosense123
05-22-2015, 03:36 AM
copy the formula in C2 to B2.
Change cell reference C1 to A2.
copy the formula to B3:B10

Thanks for your reply.
The trouble is my 'formula' in C2 is part of an array (matrix) and it cannot be used in suggested by you way. This is a calculator rather than one cell formula. And i want to use this calculator with a range of values and return range of values in te form i mentioned i my quote.
thanks

nosense123
05-22-2015, 03:44 AM
13468

I did attach an example I am trying to workout. In cell A23 there is an input value.
The calculator calculates several different properties. I am interested in a value from an array in cell T7.

And now I want to get this values from T7 for each of the arguments in a41:a100
and to return them in a range c41:c100

Any ideas for approach?
Thanks

mancubus
05-22-2015, 04:37 AM
welcome.

you mean;
copy all value in A41:A100 one by one to A23,
recalculate the formulas (if calculation mode is set to automatic excel will do)
copy the value in T7 to C41:C100
???

i'm sure, being the first poster, youngle will provide you a solution.



just a reminder: current value of A23 in example file is 55.00 whereas values in A41:A100 are between 0.05 and 3.15

nosense123
05-22-2015, 06:47 AM
welcome.

you mean;
copy all value in A41:A100 one by one to A23,
recalculate the formulas (if calculation mode is set to automatic excel will do)
copy the value in T7 to C41:C100
???

i'm sure, being the first poster, youngle will provide you a solution.



just a reminder: current value of A23 in example file is 55.00 whereas values in A41:A100 are between 0.05 and 3.15


Unfortunately it does not work like that. T7 result is based on an input in a23 and this is complex calculations involving several operations on matrices. I cannot assign this calculations like for one cell formula and drag it down to apply to a range.
The macro will be needed here and thats what I am asking for....

Yongle
05-22-2015, 07:12 AM
This is what you are aiming for I think
Given the names of the various tables etc, I could not resist naming the macro in this way - LOL
Basically what the vba does is put each value from column A in the "range" (in turn) into cell A23 and then copies the calculated value each time from T7 and places it in column C of the "range."
NOTE - this will only work if you have calculation set to automatic. Can add 2 lines to vba (to switch it on and then off again)
Workbook incl macro attached. (Values left blank in column C)


Sub Tabulate_Stiffness_By_Degree()


Dim lr As Long
Dim Range01 As Range


With Sheets("Sheet1")


'determine the lasr row in range to be tabulated
'assumes there are no empty cells in column A in the range required
lr = .Range("A41").End(xlDown).Row


'sets the range for which values are to be tabulated
Set Range01 = .Range("A41:A" & lr)


'and for each cell in that range
For Each r In Range01
'put the value of that cell into cell A23
Range("A23").Value = r.Value
'then take the (newly-calculated) value from T7 and copy that to correct row in column C
Range("C" & r.Row).Value = .Range("T7").Value


Next
End With
End Sub

Paul_Hossler
05-22-2015, 09:17 AM
If I didn't need any of the intermediate matrices I'd make a User Defined Function and just pass the barest number of parameters and do all the heavy lifting internal to the UDF


This isn't finished but you'd just use

=StiffnessMatrixOrient(151.7, 10.3, 0.25, 3.6, 55)

or

StiffnessMatrixOrient(a7, a8, a9, a11, a23)


in the worksheet


I couldn't figure out if you were returning a single number or a matrix, but the UDF could do either




Option Explicit

Sub drv()
Dim v3x3 As Variant

v3x3 = StiffnessMatrixOrient(151.7, 10.3, 0.25, 3.6, 55)
End Sub


Function StiffnessMatrixOrient(E1 As Double, E2 As Double, V12 As Double, G12 As Double, OrientationDegrees As Double) As Variant
Const cPI As Double = 3.14159265358979

Dim T(1 To 3, 1 To 3) As Double, T_Inverse As Variant, T_Transpose As Variant
Dim R(1 To 3, 1 To 3) As Double, R_Inverse As Variant, R_Transpose As Variant
Dim C(1 To 3, 1 To 3) As Double, S(1 To 3, 1 To 3) As Double

Dim SinRad As Double, CosRad As Double, v21 As Double

SinRad = Sin(OrientationDegrees * cPI / 180#)
CosRad = Cos(OrientationDegrees * cPI / 180#)

'T matrix
T(1, 1) = CosRad ^ 2
T(1, 2) = SinRad ^ 2
T(1, 3) = 2 * SinRad * CosRad
T(2, 1) = SinRad ^ 2
T(2, 2) = CosRad ^ 2
T(2, 3) = -2 * SinRad * CosRad
T(3, 1) = -SinRad * CosRad
T(3, 2) = SinRad * CosRad
T(3, 3) = CosRad ^ 2 - SinRad ^ 2
T_Inverse = Application.WorksheetFunction.MInverse(T())
T_Transpose = Application.WorksheetFunction.Transpose(T_Inverse)

'R matrix
R(1, 1) = 1#
R(1, 2) = 0#
R(1, 3) = 0#
R(2, 1) = 0#
R(2, 2) = 1#
R(2, 3) = 0#
R(3, 1) = 0#
R(3, 2) = 0#
R(3, 3) = 2#
R_Inverse = Application.WorksheetFunction.MInverse(R())
R_Transpose = Application.WorksheetFunction.Transpose(R_Inverse)

'C matrix
v21 = (E2 / E1) * V12

C(1, 1) = E1 / (1 - V12 * v21)
C(1, 2) = V12 * E2 / (1 - V12 * v21)
C(1, 3) = 0#
C(2, 1) = v21 * E1 / (1 - V12 * v21)
C(2, 2) = E2 / (1 - V12 * v21)
C(2, 3) = 0#
C(3, 1) = 0#
C(3, 2) = 0#
C(3, 3) = G12



Stop ' to check
End Function

Paul_Hossler
05-25-2015, 11:17 AM
Don't know if you're still interested in this, but it was a fun exercise

Materials is not my field, but Google to the rescue

I didn't see how to take the Stiffness and the Compliance matrices and get to a single number. Explain that and we could expand this

I made a UDF out of the inputs and made it return two 3x3 matrices

13506



Option Explicit

'http://www.efunda.com/formulae/solid_mechanics/composites/calc_ufrp_cs_arbitrary.cfm
' E1 = Young 's Modulus along Fibers
' E2 = Young 's Modulus Normal to Fibers
' V12 = Poisson 's Ratio
' G12 = Shear Modulus

Function Stiffness_Compliance_Orient(OrientationDegrees As Double, E1 As Double, E2 As Double, V12 As Double, G12 As Double) As Variant
Const cPI As Double = 3.14159265358979

Dim T(1 To 3, 1 To 3) As Double, T_Inverse As Variant, T_Transpose As Variant
Dim R(1 To 3, 1 To 3) As Double, R_Inverse As Variant
Dim Stiffness(1 To 3, 1 To 3) As Double, Compliance(1 To 3, 1 To 3) As Double
Dim C_Bar As Variant, S_Bar As Variant

Dim i As Long, j As Long
Dim Answer(1 To 3, 1 To 6) As Double

Dim SinRad As Double, CosRad As Double, v21 As Double

SinRad = Sin(OrientationDegrees * cPI / 180#)
CosRad = Cos(OrientationDegrees * cPI / 180#)

'coordinate transformation matrix [T]
T(1, 1) = CosRad ^ 2
T(1, 2) = SinRad ^ 2
T(1, 3) = 2 * SinRad * CosRad
T(2, 1) = SinRad ^ 2
T(2, 2) = CosRad ^ 2
T(2, 3) = -2 * SinRad * CosRad
T(3, 1) = -SinRad * CosRad
T(3, 2) = SinRad * CosRad
T(3, 3) = CosRad ^ 2 - SinRad ^ 2
T_Inverse = Application.WorksheetFunction.MInverse(T)
T_Transpose = Application.WorksheetFunction.Transpose(T_Inverse)

'engineering-tensor interchange matrix [R]
R(1, 1) = 1#
R(1, 2) = 0#
R(1, 3) = 0#
R(2, 1) = 0#
R(2, 2) = 1#
R(2, 3) = 0#
R(3, 1) = 0#
R(3, 2) = 0#
R(3, 3) = 2#
R_Inverse = Application.WorksheetFunction.MInverse(R)

'stiffness matrix [C]
v21 = (E2 / E1) * V12

Stiffness(1, 1) = E1 / (1 - V12 * v21)
Stiffness(1, 2) = V12 * E2 / (1 - V12 * v21)
Stiffness(1, 3) = 0#
Stiffness(2, 1) = v21 * E1 / (1 - V12 * v21)
Stiffness(2, 2) = E2 / (1 - V12 * v21)
Stiffness(2, 3) = 0#
Stiffness(3, 1) = 0#
Stiffness(3, 2) = 0#
Stiffness(3, 3) = G12
'compliance matrix [S]
Compliance(1, 1) = 1 / E1
Compliance(1, 2) = -V12 / E1
Compliance(1, 3) = 0#
Compliance(2, 1) = -v21 / E2
Compliance(2, 2) = 1 / E2
Compliance(2, 3) = 0#
Compliance(3, 1) = 0#
Compliance(3, 2) = 0#
Compliance(3, 3) = 1 / G12

'The stiffness matrix for a lamina of arbitry orient can be expressed in terms of the stiffness matrix in the principal direction
'C_Bar
C_Bar = Application.WorksheetFunction.MMult(T_Inverse, Stiffness)
C_Bar = Application.WorksheetFunction.MMult(C_Bar, R)
C_Bar = Application.WorksheetFunction.MMult(C_Bar, T)
C_Bar = Application.WorksheetFunction.MMult(C_Bar, R_Inverse)
'The complicance matrix for a lamina of arbitry orient can be obtained in a similar manner.
'S_Bar
S_Bar = Application.WorksheetFunction.MInverse(C_Bar)

'put in answer
For i = 1 To 3
For j = 1 To 3
Answer(i, j) = C_Bar(i, j)
Answer(i, j + 3) = S_Bar(i, j)
Next j
Next i


Stiffness_Compliance_Orient = Answer

End Function

nosense123
05-26-2015, 01:00 AM
[QUOTE=Paul_Hossler;325128]Don't know if you're still interested in this, but it was a fun exercise

Materials is not my field, but Google to the rescue

I didn't see how to take the Stiffness and the Compliance matrices and get to a single number. Explain that and we could expand this

I made a UDF out of the inputs and made it return two 3x3 matrices

13506





Paul,
You did a great job on this. Answering your qustion about stiffness corresponding to orientation angle it would be:
E1 - value from cell E2
E2 - value from cell F3.

nosense123
05-26-2015, 01:10 AM
This is what you are aiming for I think
Given the names of the various tables etc, I could not resist naming the macro in this way - LOL
Basically what the vba does is put each value from column A in the "range" (in turn) into cell A23 and then copies the calculated value each time from T7 and places it in column C of the "range."
NOTE - this will only work if you have calculation set to automatic. Can add 2 lines to vba (to switch it on and then off again)
Workbook incl macro attached. (Values left blank in column C)


Sub Tabulate_Stiffness_By_Degree()


Dim lr As Long
Dim Range01 As Range


With Sheets("Sheet1")


'determine the lasr row in range to be tabulated
'assumes there are no empty cells in column A in the range required
lr = .Range("A41").End(xlDown).Row


'sets the range for which values are to be tabulated
Set Range01 = .Range("A41:A" & lr)


'and for each cell in that range
For Each r In Range01
'put the value of that cell into cell A23
Range("A23").Value = r.Value
'then take the (newly-calculated) value from T7 and copy that to correct row in column C
Range("C" & r.Row).Value = .Range("T7").Value


Next
End With
End Sub


Dear Yongle,


Great job!
That is exactly what I needed,
Many thanks!
Stan

snb
05-26-2015, 03:34 AM
Or

Sub Tabulate_Stiffness_By_Degree()
With Sheets("Sheet1")
sn=.cells(41,1).currentregion.resize(,3)

for j=2 to ubound(sn)
.cells(23,1)=sn(j,1)
sn(j,3)=.cells(7,20)
next

.cells(41,1).currentregion.resize(,3)
end with
End Sub