Bessel functions of fractional power in EXCEL
Wei Dong
Dear Sir/Madam
We are engineers and educators. We need built in
functions in EXCEL for estimating non-integal Bessel
function (I, K, J.), thus
Is there built-in functions in EXCEL for evaluating the
fractional power Bessel functions (I, K, J.)?
Your response is greatly appreciated.
Regards
Wei Dong
Harlan Grove
"Wei Dong" <w....@mailbox.gu.edu.au> wrote...
>We are engineers and educators. We need built in
>functions in EXCEL for estimating non-integal Bessel
>function (I, K, J.), thus
>
>Is there built-in functions in EXCEL for evaluating the
>fractional power Bessel functions (I, K, J.)?
I don't know of any add-in function library for Excel that includes these.
Mathematica and MatLab can handle this, and there are some C math libraries
that can too (e.g., the Cephes library by Stephen Moshier,
http://www.moshier.net/cephes28.zip).
For what it's worth, the R stats package (a GNU project, so free software)
has Bessel functions that accept fractional n. Is there really a compelling
need to do what you're doing in Excel? It's not an ideal tool for
engineering, physics or any form of sophisticated numerical analysis.
--
Public Service Announcement:
Don't attach files to postings in this newsgroup.
Norman Harker
They are in Analysis ToolPak
Access method to Analysis ToolPak varies with Excel Version:
To find if you have it already up and running use:
Select a cell
Insert > Function
Scroll Down the Categories dialog
Check and see if you have an Engineering Classification
Press Escape
If you have 'Engineering' you already have the Analysis ToolPak functions
installed and selected as an Add-In. If you don't, then you'll need to
follow the instructions below.
If Analysis ToolPak is not installed or selected any cells containing
Analysis ToolPak functions and all dependent cells will return the dreaded
#NAME! If that happens read on. In any case, you should read on if you are
building an application that others might use especially if used on
different computers.
***Excel 2002***
Use Tools > Add-Ins
Check the Analysis ToolPak entry.
If you get no messages, press OK.
Depending upon whether its been used before and upon load on first use
options used during installation, you may get messages requiring you to
insert the setup discs.
Once selected, Analysis ToolPak will be loaded every time Excel boots up
until it is de-selected as an Add-In. There is a small penalty in terms of
opening speed that varies from system to system depending upon
specification.
***Excel 2000 and before***
Use Tools > Add-Ins [For old specification computers it might take a few
seconds before anything happens!]
If there is an Analysis ToolPak entry in the Options listed just put a check
against it and press OK.
If there is not an Analysis ToolPak entry you will need to close Excel and
re-run the Setup discs. You will find that Analysis Toolpak is listed as one
of the options for Excel's installation.
Once that is done you may open Excel and you'll find that it is now listed
as an Add-In when you use Tools > Add-Ins.
Once selected, Analysis ToolPak will be loaded every time Excel boots up
until it is de-selected as an Add-In. There is a small penalty in terms of
opening speed that varies from system to system depending upon
specification.
***What's in Analysis ToolPak?***
Analysis Toolpak provides many additional functions, most are (appropriately
or otherwise) located under a new functions category 'Engineering' but there
are others in other categories. For a full list you can refer to:
http://support.microsoft.com/default.aspx?scid=kb;en-us;121730
But here they are (all 89 of them for those who like checking!)
ACCRINT, ACCRINTM, BESSELI, BESSELJ, BESSELK, BESSELY, BIN2DEC, BIN2HEX,
BIN2OCT, COMPLEX, CONVERT, COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD,
COUPNUM, COUPPCD, CUMIPMT, CUMPRINC, DEC2BIN, DEC2HEX, DEC2OCT, DELTA, DISC,
DOLLARDE, DOLLARFR, DURATION, EDATE, EFFECT, EOMONTH, ERF, ERFC,
FACTDOUBLE,FVSCHEDULE, GCD, GESTEP, HEX2BIN, HEX2DEC HEX2OCT, IMABS,
IMAGINARY, IMARGUMENT, IMCONJUGATE, IMCOS, IMDIV, IMEXP, IMLN, IMLOG10,
IMLOG2, IMPOWER, IMPRODUCT, IMREAL, IMSIN, IMSQRT, IMSUB, IMSUM, INTRATE,
ISEVEN, ISODD, LCM, MDURATION, MROUND, MULTINOMIAL, NETWORKDAYS, NOMINAL,
OCT2BIN, OCT2DEC, OCT2HEX, ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD,
PRICE, PRICEDISC, PRICEMAT, RECEIVED, SERIESSUM, SQRTPI, TBILLEQ,
TBILLPRICE, TBILLYIELD, WEEKNUM, WORKDAY, XIRR, XNPV, YEARFRAC, YIELD,
YIELDDISC, YIELDMAT
One minor frequent error is to accuse DATEDIF of being a member of the
Analysis ToolPak function list. It isn't and it has a much more lurid
history that is dealt with elsewhere.
I also have Functions List files that include the Source of functions under
the various classifications and which come in Excel and Word formats.
Before and after installation of Analysis ToolPak you will find the details
of the functions and their use under the Help files for Excel which are
variously located according to Excel Version. My Function List files also
contain brief descriptions, syntax and brief details of the arguments
required.
Analysis ToolPak also provides an additional Data Analysis menu under the
Tools menu.
***Problems with Analysis ToolPak***
The chief problem with Analysis ToolPak functions is that they rely upon
installation and checking of the Add-In under the Tool-Add-Ins menu. This
causes a major lack of robustness of applications that can be a serious
issue.
Very often the user will not realise that the only thing necessary is to
check the Analysis ToolPak option. In some cases the user does not have
administration rights to install a new Add-In. In many cases they don't have
access to the original installation discs (and you can't use a 'foreign
one).
This may be an inconvenience while (if they know) they call up the IT
department but it can be a major annoyance if it happens on the office
laptop and they are at home. (Tricks like just using the key Analysis
ToolPak files just don't work; it needs installation from the discs).
For these reasons, we generally stear clear of Analysis ToolPak functions
wherever there is an alternative. Over time, in these newsgroups
non-Analysis ToolPak formulas have been published. Although these may be
less convenient than a simple use of an Analysis ToolPak function, they are
to be preferred. If the formula used is really not a pretty sight it might
be possible to break it up into parts.
The alternatives are User Defined Functions or Named Formulas. VBA User
Defined Function have to 'sit' inside the file that it is used in or they
suffer the same defect as Analysis ToolPak functions. Named formulas are
created using Insert > Name > Define. But both options have disadvantages.
UDFs may not run if the security settings are set high and the user is
paranoid (i.e. has a healthy attitude!). Named Formulas can be confusing and
cause users to go running off around the workbook looking for the name by
using GoTo.
Chip Pearson advises that ATP was create by an independent company, and some
years ago MS bought the company or at least the rights to the ATP. However,
MS never acquired the rights to incorporate the ATP functions into Excel
proper. That's why some functions still reside in the ATP and not within Exc
el proper.
***Analysis ToolPak VBA***
If you wish to use Analysis ToolPak functions in User Defined Functions, you
should check Analysis Toolpak - VBA in the list of Add-Ins in Excel. This
will bring up an entry in the VBE Project Explorer of:
atpvbaen.xls (ATPVBAEN.XLA)
You might then find it an advantage to add a reference to atpvbaen.xls and
funcres.xla using Tools > Properties in the VBE. But that is straying
outside the intention of this note.
Anyone with additional points to add to this is welcome to make the usual
rude comments.
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"Wei Dong" <w....@mailbox.gu.edu.au> wrote in message
news:056701c2c042$51dfef40$8ef82ecf@TK2MSFTNGXA04...
Harlan Grove
>They are in Analysis ToolPak
Their are times when your desire to add to your posting count seems to crowd out
all other consideration, including any consideration of checking if your answers
are correct.
>"Wei Dong" <w....@mailbox.gu.edu.au> wrote in message
>>We are engineers and educators. We need built in
>>functions in EXCEL for estimating non-integal Bessel
>>function (I, K, J.), thus
..
Let me reemphasize the key point in the OP's question: NON-INTEGER.
Now, from online help for the Bessel function of the first type:
"BESSELJ(x,n)
X is the value at which to evaluate the function.
N is the order of the Bessel function. If n is not an integer, it is
truncated."
If the ATP Bessel function truncates N to integer values, how do the ATP
versions of the Bessel functions (which all truncate N) help the OP? It helps to
know what you're talking about when responding.
Such a long response for being so pointless.
There's nothing that comes with Excel or Office that handles Bessel functions
with noninteger N. The only options are rolling your own in VBA, calling a more
capable Bessel function in some non-Office DLL, or linking Excel to a real math
package like Mathematica, Maple or MatLab (etc.).
--
Public Service Announcement
Don't attach files to postings in nonbinary newsgroups like this one.
Wei Dong
Thanks for the response. I have developed a MACRO for estimating the
NON-INTEGER ORDER modified Bessel functions in EXCEL. I also use
packages like Mathcad, Maple very often. However, there are other
users who do not have a MARCO or easy access to the packages.
Therefore, I thought it might be good to have the functions built-in
in EXCEL just like those for INTEGER ORDER modified Bessel functions,
particularly many engineers who I know still tend to use the EXCEL.
Thanks again and best regards
Norman Harker
Hi Wei!
Sorry I couldn't help.
I see from your snip that Harlan continues to justify his position as the
sole person on my list of excluded posters.
--
Regards
Norman Harker
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"Wei Dong" <w....@mailbox.gu.edu.au> wrote in message
news:878f5a3c.03012...@posting.google.com...
Harlan Grove
"Wei Dong" <w....@mailbox.gu.edu.au> wrote...
...
>Thanks for the response. I have developed a MACRO for estimating the
>NON-INTEGER ORDER modified Bessel functions in EXCEL. I also use
>packages like Mathcad, Maple very often. However, there are other
>users who do not have a MARCO or easy access to the packages.
>Therefore, I thought it might be good to have the functions built-in
>in EXCEL just like those for INTEGER ORDER modified Bessel functions,
>particularly many engineers who I know still tend to use the EXCEL.
If you have a macro solution, then you have the core of a user-defined
function solution (unless by macro you meant udf). While it'd be slower than
built-in functions, it's not a bad solution. Put the udf into an XLA add-in,
and you could distribute it to those who need it. User-defined functions are
the primary means for expanding Excel's calculation capabilities, and Bessel
functions of any type or order aren't exactly core spreadsheet
functionality.
If you want faster (as well as a ton of other numeric stuff Excel doesn't
provide), you could check out the GNU Scientific Library at the GnuWin32
site on SourceForge. See the description (with links to the download page)
at
http://gnuwin32.sourceforge.net/packages/gsl.htm
The -bin- file contains DLLs that are callable from VBA. Almost certain to
be a bit faster than purely VBA code. You may also be able to register them
and use them directly from cell formulas if the interface is amenable (I
haven't checked - yet). You'll also want the other zip files for
documentation and source code.
Your users would also need it, but once a DLL is installed and properly
configured with VBA from a file in the user's startup folder, it'd be
transparent.
--
Public Service Announcement:
Don't attach files to postings in this newsgroup.
Dana DeLouis
anything here in this long list of functions related to Bessel that might
help? You may find a formula suitable for a VBA function.
http://mathworld.wolfram.com/topics/BesselFunctions.html
--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =
<snip>
Harlan Grove
>I'm a little rusty on all the different types of Bessel functions. Is
there
>anything here in this long list of functions related to Bessel that might
>help? You may find a formula suitable for a VBA function.
>
>http://mathworld.wolfram.com/topics/BesselFunctions.html
They're interesting pages, but it takes a fair bit of work to convert them
into good numeric code. The mathworld page for the incomplete beta function
gives its definition, but it's nontrivial to derive the continued fration
algorithm that's the canonical numeric method for deriving it - see
'Numerical Recipes in C', p 227
http://lib-www.lanl.gov/numerical/bookcpdf/c6-4.pdf
Good algorithms, questionable coding style. The book does provide something
on Bessel functions of fractional order,
http://lib-www.lanl.gov/numerical/bookcpdf/c6-7.pdf
or for the FORTRAN aficionados,
http://lib-www.lanl.gov/numerical/bookfpdf/f6-7.pdf
--
Public Service Announcement:
Don't attach files to postings in this newsgroup.
Wei Dong
Thanks. The information cited here is very interesting and useful. I
have also developed a program (a few years back)in FORTRAN using the
Recipes (subrountine) you mentioned previously for the NON-INTERGER
Modified Bessel functions, which works well. I do hope that I can find
time to convert the MACROs into XLA functions in a most efficient way
following your suggestions. Thank you ! Sirs