Function xlMSUB(M1, M2)
Dim rslt()
Dim i, j
Dim rws1,
rws2, cls1, cls2
rws1 =
M1.Rows.Count
cls1 =
M1.Columns.Count
rws2 =
M2.Rows.Count
cls2 =
M2.Columns.Count
ReDim
rslt(1 To rws1, 1 To cls1)
If (rws1
<> rws2) Or (cls1 <> cls2) Then
xlMSUB
= "xlERR1"
Else
For i
= 1 To rws1
For j = 1 To cls1
rslt(i, j) = M1(i, j) - M2(i, j)
Next j
Next i
xlMSUB
= rslt
End If
End Function
|
MyExcelRoutines
Friday, August 19, 2016
xlMatrix series: Subtracting 2 matrixes
Report any errors to afstblogs@gmail.com
xlMatrix series: xlMADD - summing 2 matrixes
Report any errors to afstblogs@gmail.com
Function xlMADD(M1, M2)
Dim rslt()
Dim i, j
Dim rws1,
rws2, cls1, cls2
rws1 =
M1.Rows.Count
cls1 =
M1.Columns.Count
rws2 =
M2.Rows.Count
cls2 =
M2.Columns.Count
ReDim
rslt(1 To rws1, 1 To cls1)
If (rws1
<> rws2) Or (cls1 <> cls2) Then
xlMADD
= "xlERR"
Else
For i
= 1 To rws1
For j = 1 To cls1
rslt(i, j) = M1(i, j) + M2(i, j)
Next j
Next i
xlMADD
= rslt
End If
End Function
|
Tuesday, August 16, 2016
Eigenvalues and eigenvectors of 2x2 matrices
Source::
http://www.math.harvard.edu/archive/21b_fall_04/exhibits/2dmatrices/ |
M7: =M4+N5
|
M8:
=PRODUCT(M4,N5)-PRODUCT(N4,M5)
|
M10: =M7/2+SQRT(M7^2/4-M8)
|
M11: =M7/2-SQRT(M7^2/4-M8)
|
L13: =M10-N5
|
L14: =M5
|
M13: =M11-N5
|
M14: =L14
|
N13: =L13/L14
|
N14: =L14/L14
|
O13: =M13/M14
|
O14: =M14/M14
|
L16: =N4
|
L17: =M10-M4
|
M16: =N4
|
M17: =M11-M4
|
N16: =L16/L17
|
N17: =L17/L17
|
O16: =M16/M17
|
O17: =M17/M17
|
L19: 1
|
L20: 0
|
M19: 0
|
M20: 1
|
Monday, August 8, 2016
Th Xnumbers add-in: functions and special functions as presented in the Manual
All the elements presented are not warranted to be correct or free from defects.
Please report any errors found to afstblogs@gmail.com
|
About the Xnumbers add-in
The original site with the address below is no longer available.
http://digilander.libero.it/_ppricerca/index.html
Download and general informartion
http://www.thetropicalevents.com/Xnumbers60.htm |
Manual - 1
https://www.google.pt/url?sa=t&rct=j&q=&esrc=s&source=web&cd=7&cad=rja&uact=8&ved=0ahUKEwiWz_exrLHOAhXCVBQKHTlXAa0QFghSMAY&url=http%3A%2F%2Fwww.reading.ac.uk%2Fssc%2Fresource-packs%2FICRAF_2007-11-15%2Fresearch%2FResMetRes%2F5%2FHeiser%2Fnotes%2Fnoteaa.pdf&usg=AFQjCNFk20z89Gc06t5GLcIVSiqY8Lkc7w&sig2=RVy5Jd7iD3sZzeD5k1S1fA
NOTE: Many functions may seem not necessary but Xnumbers allows results with more digits than the limits of the Excel functions.
The add-in contains more functions than those listed below namely regarding special functions
NOTE: Many functions may seem not necessary but Xnumbers allows results with more digits than the limits of the Excel functions.
The add-in contains more functions than those listed below namely regarding special functions
Labels:
add-ins,
Excel,
higher precision,
Special functions,
VBA
Wednesday, August 3, 2016
Probability ellipses: *the origins (Galton 1886)
From
ANTHROPOLOGICAL MISCELLANEA
REGRESSION towards MEDIOCRITY in HEREDITARY STATURE
By FRANCIS GALTON, F.R.S. &C.
[WITH PLATES IX AND X.]
[Journal of the Anthropological Institute 15 (1886), 246–263.]
https://www.york.ac.uk/depts/maths/histstat/galton_reg.pdf
It is deduced from a large sheet on which I entered every
child's height, opposite to its mid-parental height, and in every case
each was entered to the nearest tenth of an inch. Then I counted
the number of entries in each square inch, and copied them out
as they appear in the table. The meaning of the table is best
understood by examples. Thus, out of a total of 928 children who
were born to the 205 mid- parents on my list, there were 18 of the
height of 69·2 inches (counting to the nearest inch), who were
born to mid-parents of the height of 70·5 inches (also counting to
the nearest inch). So again there were 25 children of 70·2 inches
born to mid-parents of 69·5 inches. I found it hard at first to
catch the full significance of the entries in the table, which had
curious relations that were very interesting to investigate. They
came out distinctly when I" smoothed" the entries by writing at
each intersection of a horizontal column with a vertical one, the
sum of the entries in the four adjacent squares, and using these to
work upon. I then noticed (see Plate X) that lines drawn through
entries of the same value formed a series of concentric and similar
ellipses. Their common centre lay at the intersection of the
vertical and horizontal lines, that corresponded to 68!- inches.
Their axes were similarly inclined. The points where each
ellipse in succession was touched by a horizontal tangent, lay in a
straight line inclined to the vertical in the ratio of 2/3; those where
they were touched by a vertical tangent lay in a straight line
inclined to the horizontal in the ration of 1/3. These ratios confirm
the values of average regression already obtained by a different
method, of 1/3 from mid-parent to offspring, and of 2/3 from offspring
to mid-parent, because it will be obvious on studying Plate X that
the point where each horizontal line in succession is touched by
an ellipse, the greatest value in that line must occur at the point
of contact. The same is true in respect to the vertical lines.
These and other relations were evidently a subject for mathe-
matical analysis and verification. They were all clearly dependent
on three elementary data, supposing the law of frequency of error
to be applicable throughout; these data being ( 1) the measure of
racial variability, whence that of the mid-parentages may be inferred
as has already been explained, (2) that of co-family variability
(counting the offspring of like mid-parentages as members of the
same co-family), and (3) the average ratio of regression. I noted
these values, and phrased the problem in abstract terms such as a
competent mathematician could deal with, disentangled from all
reference to heredity, and in that shape submitted it to Mr. J.
Hamilton Dickson, of St. Peter's College, Cambridge. I asked
him kindly to investigate for me the surface of frequency of error
that would result from these three data, and the various particulars
of its sections, one of which would form the ellipses to which I
have alluded.
Tuesday, July 26, 2016
Legendre polynomial (Legendre function of the first kind)
All the elements presented are not warranted to be correct or free from defects.
Please report any errors found to afstblogs@gmail.com
|
D3:
=IF(ISEVEN(B3),(-1)^(B3/2)*FACTDOUBLE(B3-1)/FACTDOUBLE(B3),0)
|
||||
D4: =xlLegendrePn(B3,B4)
|
Leave cell I4 blank
Ranges I5:K5 and N5:P5 are merged
I6: =IF(H6=0,1,IF(H6=1,$I$2,(2*H6-1)/H6*$I$2*I5-(H6-1)/H6*I4))
J6: =xlLegendrePn(H6,$I$2)
K6: =Poly_Legendre($I$2,H6)
M6: =H6
N6: =(-1)^M6*I6
O6: =xlLegendrePn(M6,-$I$2)
P6: =Poly_Legendre(-$I$2,M6)
MyExcelRoutines
This function returns only the value of the polynomial. The computation of the first derivative will be the object of a separate function.
Function xlLegendrePn(n, x)
Dim i
Dim P0, P1, Pn, Pn_1, Pn_2
P0 = 1: P1 = x
Pn_1 = P1: Pn_2 = P0
If (n <> Int(n) Or n < 0) Then
xlLegendrePn = "** n **"
ElseIf n = 0 Then
xlLegendrePn = 1
ElseIf n = 1 Then
xlLegendrePn = x
ElseIf x = 1 Then
xlLegendrePn = 1
ElseIf x = -1 Then
xlLegendrePn = (-1) ^ n
Else
For i = 2 To n
Pn = (2 * i - 1) / i * x * Pn_1 _
- (i - 1) / i * Pn_2
Pn_2 = Pn_1
Pn_1 = Pn
Next i
xlLegendrePn = Pn
End If
End Function
|
Xnumbers add-in
Poly_Legendre is an array function that returns both the values of the polynomial and its first derivative calculated by the subroutine EvalLegendre.
Function Poly_Legendre(x, Optional n)
Dim Pol#, Dpol#, k&, z#
If IsMissing(n) Then k = 1 Else k = n
z = x
Call EvalLegendre(k, z, Pol, Dpol)
Poly_Legendre = PasteVector_(Array(Pol, Dpol))
End Function
|
Sub EvalLegendre(n&, x#, Pol#,
Dpol#)
' Rutina para calcular el
polinomio ortonormal de Legendre de orden n y su derivada en x
' Los polinomios de Legendre son
un caso especial de los de Jacobi con a = b = 0
' Pol valor del polinomio en x;
DPol valor de la derivada del polinomio en x
' Bibliografia: Abramowitz M et al.; "Handbook
of Mathematical Functions...",Dover
'
Press et al.; "Numerical recipies in fotran77", Cambridge U
Press
'mod. 12.4.04 VL
Dim
k&, p#(0 To 2), dp#(0 To 2)
If n = 0
Then
Pol =
1
Dpol =
0
ElseIf n =
1 Then
Pol =
x
Dpol =
1
Else
p(0) =
1
p(1) =
x
If
Abs(x - 1) < 0.1 Or Abs(x + 1) < 0.1 Then
dp(0) = 0
dp(1) = 1
For k = 1 To n - 1
p(2) = ((2 * k + 1) * x *
p(1) - k * p(0)) / (k + 1)
'Polinomio de orden k+1 en x
dp(2)
= ((2 * k + 1) * (p(1) + x * dp(1)) - k * dp(0)) / (k + 1) 'Derivata del
polinomio di ' ordine k+1 in x .VL
p(0) = p(1)
p(1) = p(2)
dp(0) = dp(1)
dp(1) = dp(2)
Next
Pol = p(2)
Dpol = dp(2)
Else
For k = 1 To n - 1
p(2) = ((2 * k + 1) * x *
p(1) - k * p(0)) / (k + 1)
' Polinomio de orden k+1 ' ' en x
p(0)
= p(1)
' (***)
p(1) = p(2)
Next
Pol = p(2)
Dpol = n * (x * p(2) - p(0)) / (x ^ 2 - 1) ' Derivada del polinomio de orden k+1
en x
End If
End If
End Sub
|
From the Tutorial -1:
Matlab
Values of Pn(x)
Polynomials
Friday, June 17, 2016
Three ways to use Excel worksheet functions in VBA
Function xlAverage01(rng)
xlAverage01 =
WorksheetFunction.Average(rng)
End Function
Function xlAverage02(rng)
With WorksheetFunction
xlAverage02 = .Average(rng)
End With
End Function
Function xlAverage03(rng)
Dim wsf As WorksheetFunction
Set wsf = WorksheetFunction
xlAverage03 = wsf.Average(rng)
End Function
|
Subscribe to:
Posts (Atom)