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.


    Any ideas, thanks in advance!

    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

Participate now!

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

Register Yourself Login