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!

  • 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!