Betainv() problem

[News]

Can anyone help me with a problem concerning the betainv worksheet function?

Let me say right away that I am not a statistician!

I have a requirement to convert some Excel VBA code into C++ for speed
purposes. The code that I need to convert calls the betainv worksheet
function. I have managed to find an implementation of betainv on the web.
While testing the program I have discovered that my betainv in C++ is giving
different results to the excel worksheet function. I know that the most
likely possibility is that it is my function that is in error, however, I
have an innate distrust of all things microsoft. I cannot see any errors in
my code (that doesn't mean they don't exist!)

Alternatively, perhaps my algorithm is wrong. The code that I have taken
from the web contains the following reference:

// ALGORITHM AS 63 APPL. STATIST. VOL.32, NO.1

// Computes P(Beta>x)

Does this mean anything to anyone? Is this the same algorithm as used by
Excel? Does anyone know what algorithm Excel uses? Can anyone supply me
with a few betainv values & the parameters of probability, alpha & beta used
to calculate them?

Alternatively, does anyone have a C++ inplementation of betainv (that would
save me a lot of headaches!!!)

Thanks

 

[Ian Smith]

Can anyone help me with a problem concerning the betainv worksheet function?

Let me say right away that I am not a statistician!

I have a requirement to convert some Excel VBA code into C++ for speed
purposes. The code that I need to convert calls the betainv worksheet
function. I have managed to find an implementation of betainv on the web.
While testing the program I have discovered that my betainv in C++ is giving
different results to the excel worksheet function. I know that the most
likely possibility is that it is my function that is in error, however, I
have an innate distrust of all things microsoft. I cannot see any errors in
my code (that doesn't mean they don't exist!)

Alternatively, perhaps my algorithm is wrong. The code that I have taken
from the web contains the following reference:

// ALGORITHM AS 63 APPL. STATIST. VOL.32, NO.1

// Computes P(Beta>x)

Does this mean anything to anyone? Is this the same algorithm as used by
Excel? Does anyone know what algorithm Excel uses? Can anyone supply me
with a few betainv values & the parameters of probability, alpha & beta used
to calculate them?

Alternatively, does anyone have a C++ inplementation of betainv (that would
save me a lot of headaches!!!)

Thanks

First of all the code you refer to calculates the equivalent of
"betadist", rather than "betainv". The Applied Stats algorithm for
this is AS64 which calls AS63.

Next there are problems with AS63 (see
http://groups.google.co.uk/[email protected])

I can let you have some code in C# which will calculate the equivalent
of "betainv" for you. It's effectively a C# implementation of
"invbeta" in http://members.aol.com/iandjmsmith/Examples.xls, which
means the error signalling which appears in the higher level
"inv_beta" is up to you!

Ian Smith

 

[News]

"News" <[email protected]> wrote in message

First of all the code you refer to calculates the equivalent of
"betadist", rather than "betainv". The Applied Stats algorithm for
this is AS64 which calls AS63.

Next there are problems with AS63 (see
http://groups.google.co.uk/[email protected])

I can let you have some code in C# which will calculate the equivalent
of "betainv" for you. It's effectively a C# implementation of
"invbeta" in http://members.aol.com/iandjmsmith/Examples.xls, which
means the error signalling which appears in the higher level
"inv_beta" is up to you!

Ian Smith

Hi Ian,

Thanks for the help. I thought I was going mad with the differences in
results I was getting. Now you've pointed me in the right direction I can
look at your VBA code in Examples.xls & try to figure out what I need to do.

The C# code would be welcome too. You could post it here if it's not too
long or email me direct at simon<at>otusit<dot>co<dot>uk (replace <at> and
<dot> as necessary!)

Thanks

Simon

 

[Ian Smith]

Hi Ian,

Thanks for the help. I thought I was going mad with the differences in
results I was getting. Now you've pointed me in the right direction I can
look at your VBA code in Examples.xls & try to figure out what I need to do.

The C# code would be welcome too. You could post it here if it's not too
long or email me direct at simon<at>otusit<dot>co<dot>uk (replace <at> and
<dot> as necessary!)

Thanks

Simon

The code is pretty quick and accurate but, for that reason, too long
to post, so I've sent the code. As a matter of curiousity what is the
expected performance improvement in going to C++?

Ian Smith

 

[News]

"News" <[email protected]> wrote in message

The code is pretty quick and accurate but, for that reason, too long
to post, so I've sent the code. As a matter of curiousity what is the
expected performance improvement in going to C++?

Ian Smith

Hi Ian,

Not sure about the performance improvement of betainv from VBA to C++, but
on a recent project I was converting a Monte Carlo simulation from VBA to
C++ that did 1 million simulations. VBA is interpreted line by line & is
thus very slow. The system was run with less simulations & then the results
extrapolated. Performance extrapolations on the VBA system showed an
expected runtime of around 4 years!!!! The equivalent C++ system was
extrapolated to run in 30 days. This was with some pretty intensive
performance tuning.

In actual measurements I have had a C++ system running in 3 seconds while
the equivalent VBA system ran in 30 seconds without doing any massive
performance tuning.

Obviously the performance improvement depends on many factors such as what
is being done, run time platform etc etc. When I've got BetaInv working in
C++ I'll run a benchmark test & let you know the results. If I have time
i'll also test the same run in C#.

Cheers

Simon

 

[Harlan Grove]

. . . VBA is interpreted line by line & is thus very slow. . . .

VBA is NOT interpretted, but neither is it compiled into machine code.
Still, it's going to be slow.

 

[Paul Liu]

VBA is NOT interpretted, but neither is it compiled into machine code.
Still, it's going to be slow.

Mr. Grove,

I read you post in regarding using offset in conjunction with offset:

(http://groups.google.com/groups?hl=de&lr=&ie=UTF-8&c2coff=1&[email protected]).

I wouldlike to use the vlookup function together with the indirect.ext
function. Could you please elaborate on what you meant by:

"If the OP wants to dereference the values from a single area,
multiple cell
range in a closed workbook, two CELL("Address",OFFSET(...)) calls
would be
needed."

I tried adding more Cell functions to compose the range for the
vlookup, but I could not get the formula to work.

I have also tried to using a SumIf array formula together with
indirect.ext, but that would not work either.

Any help would be much appreciated.

 

[ragnartorfi]

Gentlemen,
Very interesting.
I am not an expert coder myself but I can work my way through simple c and c++ code examples.
I am however very interested in the betainv function and would very much like to see a code example.
It has been a while but if Mr. Smith would be so kind as to share the code with me I would be most grateful. Excel does the work but I'd prefer if I could see a working version, especially if it's fast )
My intentions are to try and build a simple cost-estimation tool to be used for home-purpose )

My email is ragnartorfi ( at ) gmail ( dot ) c o m

Kindest regards,
RT