Excel function COUNTIF not working
Solved
tdenta
Posted messages
138
Status
Member
-
ccm81 Posted messages 11033 Status Member -
ccm81 Posted messages 11033 Status Member -
Hello,
I have to create a spreadsheet for a probability exercise.
I am using Excel 2010, and I need to use the COUNTIF function, but it always returns 0.
The goal is to check in a range of 500 rows if the value 0 is present, and return the frequency of that presence.
My syntax: =COUNTIF(D2:D501,0)/500
Configuration: Windows 7 / Chrome
I have to create a spreadsheet for a probability exercise.
I am using Excel 2010, and I need to use the COUNTIF function, but it always returns 0.
The goal is to check in a range of 500 rows if the value 0 is present, and return the frequency of that presence.
My syntax: =COUNTIF(D2:D501,0)/500
Configuration: Windows 7 / Chrome
3 answers
Hello
your syntax is correct, so if there’s an error, it lies elsewhere
1. is the value 0 present in the range D2:D501
2. are the values in this range numbers? integers?
3. do the values in this range come from a formula? if yes, which one, and does it produce integers?
4. would the cell containing the formula =COUNTIF be formatted as a number with 0 decimal places? if yes, format it with 2 or 3 decimal places
5. ...
good luck
your syntax is correct, so if there’s an error, it lies elsewhere
1. is the value 0 present in the range D2:D501
2. are the values in this range numbers? integers?
3. do the values in this range come from a formula? if yes, which one, and does it produce integers?
4. would the cell containing the formula =COUNTIF be formatted as a number with 0 decimal places? if yes, format it with 2 or 3 decimal places
5. ...
good luck
1) Yes, the value 0 is present in the range
2) The values in this range are integers that are either 0, or 1, or 2
3) The values in this range come from the SUM function that adds the results of two cells containing the RAND() formula, and the values are displayed with 0 decimals.
And I think that's where the problem comes from. After replacing this formula with RANDBETWEEN, everything works perfectly .....
Thank you for your response!
2) The values in this range are integers that are either 0, or 1, or 2
3) The values in this range come from the SUM function that adds the results of two cells containing the RAND() formula, and the values are displayed with 0 decimals.
And I think that's where the problem comes from. After replacing this formula with RANDBETWEEN, everything works perfectly .....
Thank you for your response!
3) The values in this range come from the SUM function which adds the results of two cells containing the formula RAND(), and whose values are displayed with 0 decimals.
There is a difference between the exact value and the displayed value that depends on the formatting...
RQ1. The RAND() function returns a random "real" number from 0 inclusive to 1 exclusive, and it can be used to produce a random integer by combining it with the INT function
for example INT(6*RAND()) produces an integer from 0 to 5
RQ2. The RANDBETWEEN(a;b) function produces a random integer from a to b inclusive (if a and b are integers)
So you could certainly have managed with RAND, but the main thing is that you found a solution...
Good continuation
There is a difference between the exact value and the displayed value that depends on the formatting...
RQ1. The RAND() function returns a random "real" number from 0 inclusive to 1 exclusive, and it can be used to produce a random integer by combining it with the INT function
for example INT(6*RAND()) produces an integer from 0 to 5
RQ2. The RANDBETWEEN(a;b) function produces a random integer from a to b inclusive (if a and b are integers)
So you could certainly have managed with RAND, but the main thing is that you found a solution...
Good continuation