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

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/Tem...mbinazione.htm

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

TIA

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" wrote:

> 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/Tem...mbinazione.htm
>
> or here
> http://www.6dof.com/index.php?module...isplay&ceid=64
>
> TIA
>
>
>
>

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

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" wrote:

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

....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)

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

"uriel78" wrote:

> ....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)
>
>
>