View original (French)

MIN formula that doesn't work in Excel

Solved
catalinadu79 -  

Hello,

I created a grade table in Excel (I'm on a Mac Retina) and I want to indicate in cell B60 the minimum grade from the range (L10:L51)

I'm using =MIN(L10:L51) as the formula, but my cell remains empty

I don't understand why, while the maximum function works fine! ! ! I also tried adding a condition to ignore the 0s, but that doesn't work either (and a 0 score is exceptional but not impossible...)

I have another problem that I solved by cheating a bit; I want to calculate the number of candidates who scored less than 5. Excel includes the empty cells. So I asked for the scores between 0.1 and 5

=COUNTIFS($L$10:$L$51; ">=0.1"; $L$10:$L$51; "<5")

If any of you have an idea to solve at least my first problem

https://www.cjoint.com/doc/23_05/MEFjpeMYKpA_Tableaux-calcul-moyennes.xlsx

Thank you for your help.

I wish you a nice day.

Best regards

Catalina


4 answers

  1.  jee pee Moderator

    Hello,

    It is indeed zero that is taken into account, see: =MIN(IF(L10:L51>0;L10:L51))

    1
    1. brucine

      Hello,

      Yes, but also include the empty cells and thus provide the desired result.

      On the second point, the formula has extra spaces, for example:

      =COUNTIFS($L$10:$L$51;">=7"; $L$10:$L$51;"<15") (in the example, the result is 2).

      1
    2. DjiDji59430

      Hello everyone,

      And if you don't like matrix formulas with a version =>2019

      =MINIFS(L10:L51,L10:L51,"<>0")

      And so,

      =COUNTIFS(L10:L51,"<=5",L10:L51,">0")


      Best regards

      1
    3. catalinadu79 Author

      Hello,

      A big thank you for your help.

      0
    4. catalinadu79 Author
      @brucine

      Good evening,

      Thank you very much for your vigilance...

      0
    5. catalinadu79 Author
      @DjiDji59430

      Thank you very much, matrices are indeed not my strong suit... ;-)

      0
  2. PapyLuc51

    Hello,

    To complete the response from Jee pee (greetings), enter this formula using the key combination Ctrl+Shift+Enter otherwise you will get an error #VALUE!

    {=MIN(IF($L$10:$L$51>0;$L$10:$L$51))}

    Best regards

    1
    1. catalinadu79 Author

      Good evening,

      Thank you very much for this additional response.

      Is it the same key combination on Mac or PC?

      Best regards

      0
    2. PapyLuc51
      @catalinadu79

      Hello,

      .

      I didn't see that it was on Mac.

      .

      I'm not sure, but it seems to me that it's command+shift+enter; to be confirmed;)

      .

      Best regards

      0
    3. brucine
      @PapyLuc51

      Hello,

      I'm too lazy to restart my virtual Mac installation and reinstall Office; the documentation says that the key combination is the same starting from Office 16 otherwise of course the Ctrl key is equivalent to control, so control+shift+enter.

      1
    4. catalinadu79 Author
      @brucine

      Thank you for your response.

      0
  3. Raymond PENTIER Contributor

    Hello.

    "but my cell remains empty":
    That's false, the cell contains the value 0!
    In Advanced Options/Spreadsheet Display Options, you disabled the option "Show zero in cells that have a null value" ...

    Just reactivate it!

    Your formula in B67 is correct, but your heading in B62 is wrong: it's N>=15.

    0
    1. brucine

      Hello,

      We can have a good (or bad) reason to differentiate between empty cells and those that are zero, the problem becoming more complicated in that an "empty" cell can indeed contain a formula: it is sufficient in the case that concerns us that the formulas have been copied across all rows but that a particular row does not correspond to (or not yet to) the grades of a student (they are not null but empty and should not be counted in this case in the calculation of the minimum grade although it is practically impossible for a student to have received zero in all their tests).

      But this does not tell us, in a more general case, how to differentiate these situations in Excel.

      1
    2. catalinadu79 Author

      thank you

      0
    3. catalinadu79 Author
      @brucine

      Thank you

      0
  4. catalinadu79 Author

    Thank you all for your help, I must admit that I am not very familiar with how Excel works

    Best regards

    0