Definition of a statistical function (CQC) in Excel (maybe with VBA...)

[uriel78]

I need to define a kind of statistical function to retrieve a combination of
some set of values, but I don't know how to implement it in excel...maybe it
is possible using a macro...

The definition of the formula (CQC=complete quadratic combination) is given
by formula (16) and (17) here
http://www.ingegneriasismica.net/Te...ombinazione/3MCmodellazioneC_combinazione.htm

or here
http://www.6dof.com/index.php?module=ContentExpress&func=display&ceid=64

TIA

 

[Guest]

Strange as it may seem, I actually enjoy these types of problems so I gave it
a try. Disclaimer: having only a faint knowledge of what you are trying to
do (and what are reasonable values as inputs and what is the expected output)
I have no way to fully test my results. The code also contains no error
checking, so things like a zero denominator could make it crash. So I won't
vouch for the results, but I hope I have at least outlined a method of
solution. (By the way, I will be curious as to whether I got it right, so if
you have a chance after testing it a quick reply here would be nice!)

I am assuming that you have available a matched list of f, R, and xi values,
since they seem to be necessary to solve the equation. I require that these
be arranged in columns, but not necessarily consecutive columns. However,
they do need to be the same length. I wrote a user function called RCQC: you
can use it just like any other Excel function. You need to supply the three
ranges for your lists of coefficients:

Private fRange As Range, RRange As Range, XiRange As Range

Public Function RCQC(fValues As Range, RValues As Range, XiValues As Range)
' fValues is the range (in a column) containing the f coeffiecients
' RValues is the range (in a column) containing the R coeffiecients
' XiValues is the range (in a column) containing the Xi coeffiecients
Dim Ri As Double, p As Double, Rj As Double
Dim i As Integer, j As Integer
Dim R As Double

Set fRange = fValues
Set RRange = RValues
Set XiRange = XiValues

R = 0

For i = 1 To fValues.Rows.Count
For j = 1 To fValues.Rows.Count
Ri = RRange.Cells(1, 1).Offset(i - 1, 0).Value
p = pij(i, j)
Rj = RRange.Cells(1, 1).Offset(j - 1, 0).Value
R = R + Ri * p * Rj
Next j
Next i

RCQC = Sqr(R)

End Function


Private Function rij(i As Integer, j As Integer) As Double
Dim fi, fj As Double

fi = fRange.Cells(1, 1).Offset(i - 1, 0).Value
fj = fRange.Cells(1, 1).Offset(j - 1, 0).Value

rij = fj / fi

End Function
Private Function pij(i As Integer, j As Integer)
Dim Xii As Double, Xij As Double, R As Double
Dim Numerator As Double, Denominator As Double

Xii = XiRange.Cells(1, 1).Offset(i - 1, 0).Value
Xij = XiRange.Cells(1, 1).Offset(j - 1, 0).Value
R = rij(i, j)

Numerator = 8 * Sqr(Xii * Xij) * (Xii + R * Xij) * R ^ (3 / 2)
Denominator = (1 - R ^ 2) ^ 2
Denominator = Denominator + 4 * Xii * Xij * R * (1 + R ^ 2)
Denominator = Denominator + 4 * (Xii ^ 2 + Xij ^ 2) * R ^ 2

pij = Numerator / Denominator

End Function

 

[uriel78]

Well, I'm very impressed from your work...it is truly great and it seems to
work well...!!

))


I've just finished to take a first look on the results that your function
gives back and they are consistent with the theory assumption (background of
CQC's function)
In addition, I think the results are very reasonable and they seems to be
according to values I could expect before running the function.

On next days I will surely work with your function by doing a very large
number of tests (I've got a ton of experimental data that could be processed
with this function) and I will surely taking care to report to you & other
people some results and relative comments

Many many thanks for now, see you back next days!!!

 

[Guest]

Thanks for the feedback. Was curious since I was developing the code without
a good idea of what the output should be like for a given input. I have
enough of an engineering background to tell you are working with a
combination of vibrational modes, but that is about it (and my engineering
days were a long time ago...)

Hope it works out, or if not that at least you can debug the code.

 

[uriel78]

....if you want I send you a simple application of the function, I tried to
do it yesterday evening but it comes back (need your email, you can give it
to me also in pvt)

 

[Guest]

It would be interesting to see the application. Will give my email -
modified to avoid spam but you will figure it out: kgdccATwestelcomDOTcom.
Thanks