Forum Discussion
Countif function
Frequency will return an array with an element count 1 more than the bins_array argument (number of elements within the braces { }).
I included -1E-25 as I wasn't sure if your data included negatives or decimals. So, -1E-25 should, I believe, group everything below 0. Then, the 0 element should capture the 0's as well as everything between it and the next element in the bins_array, but since it is the last element the frequency function automatically groups everything greater than it into another element (and is why the function always returns an array 1 element greater than the number of elements in bins_array).
In this example, you can see frequency is returning 3 elements, everything below zero, zero, everything above zero. So, index is used to pull out the second element.
If you're just dealing with integer data, then you should be able to count the 1's with:
=INDEX(FREQUENCY((A1,A3,A5,A7,A9,A11,A13,A15,A17,A19),{0,1}),2)
If decimal data, then I believe you could use:
=INDEX(FREQUENCY((A1,A3,A5,A7,A9,A11,A13,A15,A17,A19),{0.999999999999999,1}),2)
Thanks the formula =index(frequency((A1,A3,A5,A7,A9,A11,A13,A15,A17,A19(,{-1E-25,0}),2) worked well. However when finding the same value subsituting "0" for "1", "2" and "3" respectively did not work.
Could you explain the formula and suggest the correct formula for find the number of times 1,2 and 3 occur in the 10 cells.
Why use index and not just frequency? What is the significance of {-1e-25,0),2). I am assuming the 0 is the value we are seeking to know.
Thanks
Frequency will return an array with an element count 1 more than the bins_array argument (number of elements within the braces { }).
I included -1E-25 as I wasn't sure if your data included negatives or decimals. So, -1E-25 should, I believe, group everything below 0. Then, the 0 element should capture the 0's as well as everything between it and the next element in the bins_array, but since it is the last element the frequency function automatically groups everything greater than it into another element (and is why the function always returns an array 1 element greater than the number of elements in bins_array).
In this example, you can see frequency is returning 3 elements, everything below zero, zero, everything above zero. So, index is used to pull out the second element.
If you're just dealing with integer data, then you should be able to count the 1's with:
=INDEX(FREQUENCY((A1,A3,A5,A7,A9,A11,A13,A15,A17,A19),{0,1}),2)
If decimal data, then I believe you could use:
=INDEX(FREQUENCY((A1,A3,A5,A7,A9,A11,A13,A15,A17,A19),{0.999999999999999,1}),2)
Thank you very helpful.
What do you know about prediction, a series of whole numbers either a "0" or a "1" , can we predict what the next number will be, will it be a "0" or will it be a "1".
I would love hear your thoughts.
- Sorry, but prediction is outside my realm.