Triangular Distribution using VBA

[pogo1]

hello,

I'm trying to use Excel VBA to create a function sub for a triangular distribution with parameters a(min), b(mode), and c(max).
The method is:

• Calculate d = (b-a)/(c-a)
• Generate a uniformly distributed random number U between 0 and 1 (with the rnd function)
• If U<d return a+(c-a)*sqr(dU) as the random number
• Else, return a+(c-a)*[1-sqr((1-d)*(1-U))] as the random number.

Currently I have:

Please Login or Register  to view this content.

But it doesn't seem to run on the excel spreadsheet.

Any help is greatly appreciated
Thanks

[MarvinP]

Hi pogo and welcome to the forum,

I get an error in compiling the function as "prob(1)" is not defined. What is prob(1) supposed to be??

[protonLeah]

cumProb = Prob(1):
1. Prob does not correspond to syntax and description of the Worksheetfunction per Microsoft.:

PROB(x_range,prob_range,lower_limit,upper_limit)

2. CumProb is never used in the function.

Please Login or Register  to view this content.

[pogo1]

Hello! thank you!

I thought it was suppose to be a finding the cumulative probability? but now that i look at it, it does not seem like that needs to be there.

[pogo1]

Please Login or Register  to view this content.

when i type the function into the excel spreadsheet will it have to be

=Triangular(3,4,6)

[protonLeah]

No, in a cell it should be cell addresses. for example:
=Triangular(A1,A2,A3)

[MrShorty]

That would be one potential function call. You could also put any/all of those values into cells and use cell references =Triangular(A1,B1,C1) where A1 is 3, B1 is 4, and C1 is 6.

I note that you are using Singles for the arguments and calculations. I have seen at least one case on this forum where the poster had some concerns with the way that Excel/VBA converted a result from Single to Double, since that conversion will not always be exact. I guess I just wonder if there is a reason you are using the Single data type rather than the Double data type for all variables.

[marium.k]

Hi, I am currently doing the same problem and am having trouble because it keeps showing an error in the worksheet. If someone could help me, that would be great. Thanks.

[MrShorty]

@marium.k: This forum usually doesn't like it when you post your question in the thread of another member. They would prefer that you start your own thread. If this thread will help others understand your question, include a link to this thread in your post. When you start your own thread, it will probably be important to know exactly what code you are using, how you are using it, and what error you are getting.