View original (French)

Excel function COUNTIF not working

Solved
tdenta Posted messages 138 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

3 answers

  1.
    ccm81 Posted messages 11033 Status Member 2 434
     
    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
    0
  2. tdenta Posted messages 138 Status Member 37
     
    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!
    0
  3. ccm81 Posted messages 11033 Status Member 2 434
     
    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
    0