My on-line friend Al Vachris recently sent me a link to the Tropical Events site, which features a detailed analysis of the orbital motion of the Earth and the other bodies of the solar system, all done in Excel, and all available for free download:
This is a great site, and well worth a visit for anyone interested in astronomy or the scientific application of Excel, but what really grabbed my interest was that the author had used the XNumbers add-in in preparing his charts, and he had got his brother to update the program for Excel 2007/2010.
Xnumbers is a free open-source maths add-in, offering a huge number of additional maths functions and extended precision arithmetic. It was developed by Leonardo Volpi and the “Foxes Team” in Italy, but it had not been updated for Excel 2007. Steve Beyers, the author of the Tropical Events site, got his brother, John Beyers, to do the necessary update, and also to compile the functions with a resulting performance improvement. The new version, XNumbers6.0, can be downloaded from:
The download includes an excellent detailed help file. Further information is available at:
THE VOLPI XNUMBER ADD-INS: (This is summary material extracted from the site http://digilander.libero.rt/foxes. It is presented as an explanation on how xnumbers works in an Excel environment.)
The Foxes Team site Detailed documentation of XNumbers and related maths topics from the original authors.
It is excellent news that this software is now available for Excel 2007/2010, and I recommend the sites linked above to anyone wanting to do advanced numerical analysis inside Excel.
Newton Excel Bach, not (just) an Excel Blog 
As I’ve posted elsewhere on this blog, I use XNumbers quite frequently (since 2004, I think). There is a big difference between XLS and XNumbers concerning the normalisation as well as the format of the FFT output. Easy access to xNumbers is provided by the built-in formula-converter macro. Because “=A3+B4*C5” at an accuracy of 17 digits (maximum larger than 200) will be translated into “=xAdd(A3;xMult(B4;C5;”17″);”17″)”, for example, the converter may fail if the level of nested brackets exceeds Excels maximum. Then, the original formula has to be splitted before conversion. xNumbers are stored as text, the conversion of a final result in cell D6 back to Excels number format has to be done using “=xCdbl(D6)”. The xNumbers package comprises some non-extended but very useful scientific UDFs.
I recommend to use xNumbers whenever the formulae are very complicated so that a deliberate cancellation of terms may lead to a huge loss in precision, for example, when solving a polynomial equations analytically.
Have fun
Georg
LikeLike
Hi all, please note the following important parts of an email I have just sent to Steve because I found a bug in two functions of the current 64bit version of xNumbers for MS-Office 2010, i.e. 6.0.45-8M:
—————-
But unfortunately, in xNumbers 6.0.45-8M for XLS2010 (version 14) there seems to be a bug at least in the “xMean” as well as “xVar”-functions: my standard example to convince people of the poor quality of XLS internal routines is to let them calculate the mean and variance, resp., of the following data set: 12 (or 14) times 1 and a single value of 1.00000000000001 (1+1e-14).
The correct results are:
12 times 1: mean=1.00000000000000076923076923
var=7.69230769230769E-30
14 times 1: mean=1.000000000000000666666666666
var=6.66666666666667E-30
The results provided by XN-6.0.45-8M are:
12 times 1: mean=1.00000000000000076861594
var=7.68001602188709E-30
14 times 1: mean=1.000000000000000666133814667
var=6.65601388563548E-30
The old version XN 5.6.2 does work properly with XLS2002 under MS-WinXP. You might want to check my “correct” values by using the following formulae with XN 6.0.45-8M:
12 times 1 mean: =xAdd(“1”;xMult(xDiv(“1″;”13″;32);”1e-14”;32);32)
14 times 1 mean: =xAdd(“1”;xMult(xDiv(“1″;”15″;32);”1e-14”;32);32)
12 times 1 var:
=xCDbl(xMult(xDiv(xAdd(xMult(xPow(xDiv(“1″;”13″;32);”2″;32);”12”;32);xPow(xDiv(“12″;”13″;32);”2″;32);32);”12″;32);”1e-28”;32))
14 times 1 var:
=xCDbl(xMult(xDiv(xAdd(xMult(xPow(xDiv(“1″;”15″;32);”2″;32);”14”;32);xPow(xDiv(“14″;”15″;32);”2″;32);32);”14″;32);”1e-28”;32))
or by manually deleting the offset of 1 from all the data values and then using the standard XLS functions for mean and var, resp.
Due to the fact that I nearly got the “wrong” XN-6.0.45 values when subtracting 1 by Excel formulae (which is wrong as 1.00000000000001 – 1 does not equal 1e-14 on a PC because 1e-14 cannot be represented exactly by a sum of negative powers of 2), I suppose that there happens to be an unextended calculation somewhere in the code of the functions xMean as well as xVar, resp.
P.S.: I’m working with a German system, so perhaps a “,” has to be
changed into a “.” and a “;” into a “,”, resp., to make things work in
other localisations…
————————
In the subject line I told Steve that the results were obtained using an Intel i5-530 processor. So be warned, cheers
Georg
LikeLike
Georg – thanks for copying that to us. I can confirm that the 32 bit version has the same problems with Excel 2010.
I also note that:
=(xMult(xDiv(1,15),”0.00000000000001″))
gives 6.666666666666666666666666667E-16
but:
=(xMult(xDiv(1,15),0.00000000000001))
gives 6.666666666666666658793333334E-16
LikeLike
I set up a a small “migration test suite” for xNumbers … the main result of which is that the functions xMean as well as xVar, resp., do what they are meant to do, BUT: the way xNumbers internally converts numerical entries in Excel cells into its internal xNum data type has changed significantly from version 5.6.2 to 6.0.45. So “it’s not a bug, it’s a feature”… typical migration problem: new version is more stringent theoretically but less convenient practically… I have sent Steve my apologies for filing in a “bug report”… I hope the following examples illustrate the behaviour of both versions of xNumbers:
1) xN 5.6.2 XLS2002(32bitMSO10) WinXPprof32bitSP3 i5 650
formula result =xdiv("1";"15";20) 6,6666666666666666666E-2 =xdiv(1;15;20) 6,6666666666666666666E-2 =xmult("1";"1e-14";20) 0,00000000000001 =xmult(1;"1e-14";20) 0,00000000000001 =xmult("1";1e-14;20) 0,00000000000001 =xmult(1;1e-14;20) 0,00000000000001 =xmult("1e-7";"1e-7";20) 0,00000000000001 =xmult(1e-7;"1e-7";20) 0,00000000000001 =xmult(1e-7;1e-7;20) 0,00000000000001 =xadd(1;1e-14;20) 1,00000000000001 =xmult(xdiv("1";"15";20);"1e-14";20) 6,6666666666666666666E-16 =xmult(xdiv(1;15;20);1e-14;20) 6,6666666666666666666E-16 =xadd(1;1e-14;20) 1,00000000000001 (cell B18) =xadd("1";"1e-14";20) 1,00000000000001 =xsub(B18;1) 0,00000000000001 =xsub(B19;1) 0,00000000000001 =xcdbl(B20) 1,00000000000000E-14 =xcdbl(B21) 1,00000000000000E-14 =B18-1 9,99200722162641E-15 =B19-1 9,99200722162641E-15and now for the current version of xNumbers
2) xN 6.0.45-8M XLS2010(64bitMSO14) Win7prof64bitSP1 i5 650
formula result =xDiv("1";"15";20) 6,6666666666666666667E-2 =xDiv(1;15;20) 6,6666666666666666667E-2 =xMult("1";"1e-14";20) 1E-14 =xMult(1;"1e-14";20) 1E-14 =xMult("1";1e-14;20) 9,9999999999999999882E-15 =xMult(1;1e-14;20) 9,9999999999999999882E-15 =xMult("1e-7";"1e-7";20) 1E-14 =xMult(1e-7;"1e-7";20) 9,9999999999999995475E-15 =xMult(1e-7;1e-7;20) 9,999999999999999095E-15 =xMult(xDiv("1";"15";20);"1e-14";20) 6,6666666666666666667E-16 =xMult(xDiv(1;15;20);1e-14;20) 6,6666666666666666588E-16 =xAdd(1;1e-14;20) 1,00000000000001 (cell B18) =xAdd("1";"1e-14";20) 1,00000000000001 =xSub(B18;1) 1E-14 =xSub(B19;1) 1E-14 =xCDbl(B20) 1,00000000000000E-14 =xCDbl(B21) 1,00000000000000E-14 =B18-1 9,99200722162641E-15 =B19-1 9,99200722162641E-15I think the differences are easy to spot. Results of the xNum data type are displayed as strings and thus are left aligned, whereas the last four rows of each block are Excel numbers and thus are right aligned (if the <pre>-tag works…).
LikeLike
A few more comments on this:
– I’m not sure that this is a “feature”. Surely we should be able to work on data from a range in high precision.
– The functions from the old version do seem to work in XL 2010 with Windows 7.
– The menu bar from the old version does not load, but I’m having trouble with the new version to. Initially it didn’t load, then I did something that made it work (I don’t know what), then fiddling around today it has gone back to not working.
So it seems there is still some work required on this version.
LikeLike
Hi Doug,
well, I’ve analysed the XL “Double” data format (it does not matter whether WinXP-XL2002-32 bit or Win7-XL2010-64 bit) a bit over the weekend. If the decimal number 1.00000000000001 is entered in a cell, the binary representation is:
O OIIIIII-IIII OOOO-OOOOOOOO-OOOOOOOO-OOOOOOOO-OOOOOOOO-OOOOOOOO-OOIOIIOI
Hyphens separate the 8 bytes; 1 bit for the sign is followed by 11 bits for the exponent –with offset 1023– and after that follow the 52 mantissa bits; a leading bit with value I has to be added to the mantissa sequence, the exponent refers to that non-stored bit! Wikipedia has an article on Double Prescision… The binary representation of 1.00000000000001 thus corresponds to the extended decimal number 1 + 2^-52 + 2^-50 + 2^-49 + 2^-47 = 1 + 9.9920072216264088638126850128173828125e-15 . In my example we have 12 x 1 and 1 x 1,00000000000001; the xN40-mean is: 1,00000000000000076861594 (exactly !!). I then had the idea that if the decimal number is not marked as string by a leading apostrophe, xN would extend the binary representation to the specified accuracy, but that’s not exactly true:
1,00000000000000076861594 = xDiv(xAdd(13;”9,99200722e-15″;60);13;60)
I don’t understand the truncation at the moment… but I think the behaviour of xN in general is consequent. Like in all other extended precision frameworks known to me (e.g. numpy+scipy, Maxima, Reduce) decimal numbers have to be entered as strings in order to have them represented internally exactly as they are written:
12 x 1 + 1 x '1.00000000000001 leads to 1,000000000000000769230769230769230769231 as xN-40-mean ; in xN we get 1,00000000000000076923076923076923076923076923076923076923077 as result of =xAdd(1;xDiv("0,00000000000001";13;60);60)There even is an improvement in xN: extended numbers are now rounded instead of being simply cut of at the end (compare to my “complaining” post).
– – – –
I had some trouble finding the menu bar when trying out xN under XL2010 the first time, too. Meanwhile, I bought myself an English language pack, so the following technical terms should be found in your XL version, too:
1) click on “File” -> “Options” -> “Customize Ribbon”
2) in the “Customize the Ribbon” section choose “Main Tabs” and in the list then
check “Developer” (for the VBA menu) as well as “Add-Ins” (for the xN menu)
3) left besides the blue XL question-mark help symbol there is a small up/down arrow which toggles the minimisation/expansion of the ribbon. In expanded-ribbon mode there is a closed-book symbol in the ribbon, click it and the xN menu bar appears (hopefully… at least it does on my PC… and the symbol now turns into an open book…)
– – – –
Indeed, there is still work to do in xN. When I looked through the function called by xMean as well as xVar, resp., I found some inconsistencies concerning the usage of the variable controlling the precision. I think that there are good reasons for the usage of xN even with the 64bit version of XL and that it is quite challenging for one person to maintain such a big package in spare time. So if there are several people seriously using the current version of xN, creating a community might be the right choice.
(I was quite surprised this weekend how easy it was to port my old Fortran code for getting the single bytes/bits of a Double Precision number to VB(A) using the famous CopyMemory kernel function together with pointers. It will be funny to see what happens if these “no-no-VBA-has-no-pointers” evangelists read the error message >>in the 64 bit version, the keyword PtrSafe has to appear in Declare statements…<< when opening an old file under 64 bit for the first time. If have OSS Fortran code for many special functions which is proven to be superior to that in xN, but without a broad community it does not make much sense to me to port it…)
LikeLike
Hi Georg,
Thanks for the detailed reply.
To my engineers brain, it seems to make more sense to convert a double into the exact number it displays as, rather than its exact binary representation, but there may be good reasons for doing it that way. Anyway, on going back and trying again I find that if I enter the 1.00000000000001 as text I get the right answer (and the ones can be entered either as text or values).
I also discovered these functions need to be used with some care; for instance with =xMean(A6:A20,30) in C8, and =xMean({“1″,”1″,”1″,”1″,”1″,”1″,”1″,”1″,”1″,”1″,”1″,”1″,”1″,”1″,”1.00000000000001”},30) in C9, then =xSub(C8,C9) gives zero, but =xAdd(C8,-C9) gives 6.6667E-16, and I have to use =xAdd(C8,xNeg(C9)) to get the right answer of zero. (And checking just now, the same applies to xAdd(C8,-C8), which makes sense when I think about it).
On the menu question, I had the add-in box checked in the ribbon customisation menu, but I can’t find any closed or open book symbol anywhere.
In the end I have solved the problem by adding the command: CommmandBar_Create to the Workbook_Open() Sub in XN.xlam ThisWorkbook:
..
Loop
Foglio11.Cells(2, 1).value = s
CommmandBar_Create
If DoFullCalc Then Application.Calculation = OrigCalcStatus
Application.ScreenUpdating = True
..
This seems to work. I don’t know why the other code isn’t working but I don’t have time to look into it at the moment.
As for a community, it would be good, but not knowing the background it would be difficult, or perhaps there is such a thing already. I’ll have a bit of a search.
LikeLike
Dear experts,
would someone know how to solve this problem with XNumber – ODE ?
http://www.mrexcel.com/forum/excel-questions/756575-xnumbers-addin-ordinary-differential-equations.html
Thanks, Miro
LikeLike
Miro – I’ll try and find time to have a look, but I’m pretty busy at the moment so I might not succeed!.
You might like to have a look at:
https://newtonexcelbach.wordpress.com/2010/06/18/using-the-alglib-ode-runge-kutta-solver-with-excel/
It uses the old VBA version of Alglib, but it still works.
LikeLike
Dear Dough,
thanks for your ODE solver; please does it work also for the system of differential equations ?
Miro
LikeLike
Miro,
the source codes of the math parsers used by Xnumbers are located in the two class modules of Xnumbers. It seems to me as if the grammar of these parsers does not allow for the mathematical notation y(x) or similar. Additionally, you will find the list of mathematical functions that have been implemented by the parsers there as well. From comments in the source code of the sub “ODERK4”, which is contained in the module named “integration”, you can infer how to enter systems of 1st order ODEs.
In case you and/or your students prefer a more mathematical notation, you might want to introduce free CAS software like Maxima or Reduce to your students in order to teach them the difference between “mathematical numerical methods” and “floating-point results”.
HTH Georg
LikeLike
Dear Georg,
thanks for your hint. I looked into the VBA code of XN addin, but the code is too difficult to fix…
We would desperately need a dedicated web space for XNumbers, with replies (and fixes) from XN author(s)….
Concerning my teaching, I am restricted to Excel, and XNumbers seems to be the only tool dealing with system of differential equations. I am in need of simple (and free of charge) VBA tools for solving systems of ODE – multiple chemical reactions in the atmosphere.
LikeLike
Miro see https://newtonexcelbach.wordpress.com/2014/02/23/rabbits-foxes-and-lorenz-attractors/
Also I have added an example solving the problem you posted at MrExcel in a separate file which you can download from:
http://interactiveds.com.au/software/ODESolver-Miro.xlsb
The example is on the ODE_2 sheet.
LikeLike
Miro,
there is a comprehensive tutorial on how to use the ODE tools for solving (systems of) ODEs provided by Xnumbers that still can be downloaded from the original Italian website of Xnumbers:
Click to access ODE_tutorial.pdf
In the download section of that site:
http://digilander.libero.it/foxes/SoftwareDownload.htm
you may find other very valuable tools. Unfortunately, the FDsolver add-in is free but password protected. Extensions (under GPL license) to the modern versions of Xnumbers can be found here:
http://www.bowdoin.edu/~rdelevie/excellaneous/
HTH Georg
@Doug: I’ve posted these links here because I think they might be of interest to others.
@Miro: for further detailed discussions on Xnumbers, please contact drgst (at) web (dot) de. AFAIK, there is a single, highly skilled VBA developer pushing Xnumbers. During the past few years, he has released an updated version once a year on average. IMHO, if you need lively discussions with developers, you might try to read Doug’s posts on how to connect XL to PY, and then use the rich variety of scientific PY tools.
LikeLike
Thanks for the links Georg, I’ll have a look at the tutorial, and perhaps look at using the Alglib ODE functions on systems (time permitting). I’ll try and find time to have another look at the current version of XNumbers.
I was also going to suggest that Miro has a look at linking to Python from Excel, As you say, there is a very active community working in Python, although I suspect many of them would turn their noses up at the Excel connection! As far as I know the Pyxll site is the only active forum discussing using Python with Excel, but it is actively maintained by the Pyxll developer, who is very helpful. Pyxll is a commercial product by the way, but is free for non-commercial use, which I presume includes academic.
LikeLike
Pingback: Rabbits, Foxes, and Lorenz Attractors | Newton Excel Bach, not (just) an Excel Blog