Jun 29 2021 03:40 PM
I want to count the number of times "0" is found in 10 individual cells, not in a range. What is the formula ?
Jun 29 2021 06:42 PM
Jun 30 2021 05:21 AM
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
Jun 30 2021 07:40 AM
Solution
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)
Jun 30 2021 05:04 PM
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.
Jun 30 2021 07:40 AM
Solution
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)