Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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...play&ceid= 64 TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Definition of a statistical function (CQC) in Excel (maybe with VB
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...play&ceid= 64 TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Definition of a statistical function (CQC) in Excel (maybe with VB
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!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Definition of a statistical function (CQC) in Excel (maybe wit
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!!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Definition of a statistical function (CQC) in Excel (maybe with VB
....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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Definition of a statistical function (CQC) in Excel (maybe wit
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
definition of a formula and function? | Excel Discussion (Misc queries) | |||
Does excel have a function for statistical hypotosis testing? | Excel Worksheet Functions | |||
Definition of a statistical function (CQC) in Excel | Excel Worksheet Functions | |||
Statistical Excel Function Question within Excel 2000... | Excel Worksheet Functions | |||
Excel user-defined Function: definition/help of arguments | Excel Programming |