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

"News" <NoMail@127.0.0.1> wrote in message news:<31adc.35$Kl.21@newsfe1-win>...
> 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/groups?hl...ing.google.com)

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

"Ian Smith" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "News" <NoMail@127.0.0.1> wrote in message
news:<31adc.35$Kl.21@newsfe1-win>...
> > 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/groups?hl...ing.google.com)
>
> 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

"News" <NoMail@127.0.0.1> wrote in message news:<y7hdc.659$D%(E-Mail Removed)>...
> "Ian Smith" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > "News" <NoMail@127.0.0.1> wrote in message
> news:<31adc.35$Kl.21@newsfe1-win>...
> > > 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/groups?hl...ing.google.com)
> >
> > 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

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

"Ian Smith" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "News" <NoMail@127.0.0.1> wrote in message
news:<y7hdc.659$D%(E-Mail Removed)>...
> > "Ian Smith" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > "News" <NoMail@127.0.0.1> wrote in message
> > news:<31adc.35$Kl.21@newsfe1-win>...
> > > > 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/groups?hl...ing.google.com)
> > >
> > > 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
>
> 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

"News" <NoMail@127.0.0.1> wrote...
....
> . . . 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.

"Harlan Grove" <(E-Mail Removed)> wrote in message news:<T3zdc.947$(E-Mail Removed)>...
> "News" <NoMail@127.0.0.1> wrote...
> ...
> > . . . 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.

Mr. Grove,

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

(http://groups.google.com/groups?hl=d...newsranger.com).

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