problem with PROB function
Posted by Jon on December 10, 2001 11:36 AM
hi,
I have a series of 400 numbers from A1:E80 and I want to find the probability that the numbers would fall within two numbers. I understand that my x_range is A1:E80 and I know my lower and upper limit's but I'm having difficulty figuring out what my prob_range is. I'm always getting the #NUM! error and have runs out of ideas of what to try. If anybody could help me with this it would be much appreciated.
thanks
Jon
Posted by Damon Ostrander on December 10, 2001 12:49 PM
Hi Jon,
In order to use the PROB function you must know for each of the 400 numbers what its probability of occurrence is. If your data represents a histogram, then for example the probability for the 200th value represents the probability of a value falling between the 199th and 200th numbers. But the key is that you must know these probabilities (or counts of frequency of occurrence divided by the total number of counts), and it is an array of these probabilities that must be assigned to the prob_range argument.
If, on the other hand, your data in A1:E80 already represents probabilities or counts (if counts, they need to be normalized to probabilities by dividing by the sum of all the counts), then you need to come up with the x_range. The x_range is the list of abscissa (x) axis values corresponding to each probability value.
I hope this helps.
Damon
Posted by Jon on December 10, 2001 1:18 PM
The probability for each of the 400 numbers in A1:E80 is equal which works out to 0.0025 for each cell. I'm just stuck with how to put that into the prob_range argument.
Jon
Posted by Damon Ostrander on December 11, 2001 1:44 PM
Hi again Jon,
If the probability for each of the 400 numbers is the same (0.0025), then you have a Uniform distribution, in which case the calculation is so simple that the PROB function is not even necessary. But assuming that you want to use the PROB function because you will eventually have different probabilities, you need to create another range of the same size as your A1:A80 range to contain the value 0.0025 (or whatever your probabilities become later). I suggest you fill the entire range F1:J80 with the value 0.0025. In the last cell, J80, you must put the formula
=1-SUM(F1:J79,F80:I80)
This ensures that the set of probabilities EXACTLY sums to one, which is a requirement of the PROB function. You will find that if you don't do this the PROB function will not work for this large a set of numbers, since they sum to a number which is very slightly (almost imperceptively) different from one.
Now that you have done this, your PROB function should work when called like this:
=PROB(A1:E80,F1:J80,5,12)
where 5 is the lower limit and 12 the upper limit of the range of values in A1:E80 that you want to calculate the probability for.
Happy computing.
Damon
