Formula Error, or Logic error? Triangular Distribution

[bs0d]

I'm trying to calculate the f(x) for a triangular distribution.

here are the inputs:

min: 604
max: 2390.75
most likely (ML):994.45

here is the first random number: 604

This is the formula I'm using:

=IF(B3<= (ML-MAX)/(MAX-MIN), MIN+SQRT((ML-MAX)*(MAX-MIN)*B3), MAX-SQRT((MAX-ML)*(MAX-MIN)*(1-B3)))

B3 contains the random number 604. The result is #N/A. Something is wrong, I can't find it. Any help

 

[Trouttrap2]

It looks like this experssion SQRT((E2-E3)*(E3-E4)*B3) is trying to take the square root of a negative number, giving you a #Num error.

 

[pgc01]

B3 contains the random number 604. The result is #N/A. Something is wrong, I can't find it. Any help

B3 = 604 ???

Shouldn't B3 be a uniform random number between 0 and 1?

 

[pgc01]

=IF(B3<= (ML-MAX)/(MAX-MIN), MIN+SQRT((ML-MAX)*(MAX-MIN)*B3), MAX-SQRT((MAX-ML)*(MAX-MIN)*(1-B3)))

Also it doesn't make sense the (ML-MAX) that is negative, shouldn't it be:

=IF(B3<= (ML-MIN)/(MAX-MIN), MIN+SQRT((ML-MIN)*(MAX-MIN)*B3), MAX-SQRT((MAX-ML)*(MAX-MIN)*(1-B3)))

 

[bs0d]

I want the result to be a number between 0 and 1. The random number is a sample from the triangular distribution. I think I need to double-check the book on the formula and make sure i'm translating it into excel properly!

 

[tusharm]

I don't know if you derived the formula from a basic understanding of the distribution or just found what someone proposed as the solution, but look at the wikipedia article. It will help you understand how to use the triangular distribution.

http://en.wikipedia.org/wiki/Triangular_distribution

To generate a random number from a triangular distribution, generate a uniform random number using RAND. This is the CDF value. So, use the CDF formulas from the wikipedia page to compute the value of x.

I'm trying to calculate the f(x) for a triangular distribution.

here are the inputs:

min: 604
max: 2390.75
most likely (ML):994.45

here is the first random number: 604

This is the formula I'm using:

=IF(B3<= (ML-MAX)/(MAX-MIN), MIN+SQRT((ML-MAX)*(MAX-MIN)*B3), MAX-SQRT((MAX-ML)*(MAX-MIN)*(1-B3)))

B3 contains the random number 604. The result is #N/A. Something is wrong, I can't find it. Any help

 

[bs0d]

Thanks for feedback. I have a list of numbers. I want to plot them on a CDF. I want to place the sampled numbers on the x axis, and the f(x) on the y axis.