# Statistical: Probability Function

• I would like to use the probabilty function and am having some difficulty.

I have a lower limit 0 and an upper limit 1400 an array1 with a set of 21 values and another array2 with a set of 21 probability values. The individual probability values are each 1/count(array1).

I'm finding that if I just have 10 or 20 values it works fine, however, if I have 21 it appears that the sum of the probability values doesn't add up to exactly 1

Or something else is going on. Attached is a sheet that started with 21 values and didn't work. I then copied this to the right. I deleted one set of values from the left side and it works with an even number of 20. However, when I do the same thing on the right side it doesn't even though this should be the same thing????

When I just do an example the thing tends to work, but, when I go with real numbers it tends not to.

• Remember that the sum of probabilities must be 1, so when you have 21 items each propbability must be 1/21. I have changed your workbook to show this and you can see that everything now works.

• I see where I had an error on the original attachment, but, I see if I add a few rows and values on the right hand side of the one you edited, again it doesn't work. It picks up the fact that there are a new number of values etc but I get the error

• The problem is caused by poor programming on Microsofts part. When Excel added all of the 1/27s together it did not quite hit 1 due to numerical accuracy, thereby failing the must equal 1 test. I changed the formula in the last probability value to equal 1 minus the sum of the rest and now it works. Microsift should have allowed a bit more "slop" (technical term) in its check for summing to 1.

• I see your solution and it works with 21 and some other numbers but doesn't appear to always work. The actual number of records could vary so I don't want to tie myself down if I can avoid it.

I tried it with some real data of 74 records and it didn't fix it. I deleted about ten records and then it worked.

Any more ideas?

Thanks

• Forget about Excel's finicky Prob function and use SumProduct instead. See the attached. You could also put in a separate check to ensure the probabilities sum to 1.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!