Forum Discussion

ekathebauer's avatar
ekathebauer
Copper Contributor
Nov 01, 2021
Solved

Count highest and lowest repeats of "0.04" in my 3 columns

Hello, 

I am looking to find a formula that counts the highest and lowest repeats of "0.04" in my three columns respectfully. 

 

I used helper cells and an IF formula, specifically this one "=IF(D3=0.04,G2+1,")" and it works until my repeats are separated by a zero.  

 

Could someone help me?  

  • ekathebauer 

    If without helper columns and taking into account that's Excel 365

    max:

     

    =LET(
      n,     0.04,
      range, D1:INDEX(D:D, COUNTA(D:D)),
      k,     SEQUENCE(ROWS(range)),
      f,     FREQUENCY( IF( range=n, k), IF(range<>n, k) ),
    MAX(f) )

     

     

    min:

     

    =LET(
      n,     0.04,
      range, D1:INDEX(D:D, COUNTA(D:D)),
      k,     SEQUENCE(ROWS(range)),
      f,     FREQUENCY( IF( range=n, k), IF(range<>n, k) ),
    MIN( FILTER(f, f) ) )

     

7 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    ekathebauer 

    If without helper columns and taking into account that's Excel 365

    max:

     

    =LET(
      n,     0.04,
      range, D1:INDEX(D:D, COUNTA(D:D)),
      k,     SEQUENCE(ROWS(range)),
      f,     FREQUENCY( IF( range=n, k), IF(range<>n, k) ),
    MAX(f) )

     

     

    min:

     

    =LET(
      n,     0.04,
      range, D1:INDEX(D:D, COUNTA(D:D)),
      k,     SEQUENCE(ROWS(range)),
      f,     FREQUENCY( IF( range=n, k), IF(range<>n, k) ),
    MIN( FILTER(f, f) ) )

     

  • mtarler's avatar
    mtarler
    Silver Contributor

    ekathebauer  I think this is what you want:

    change the formula to use 0 (zero) instead of "" (blank)

    then max will work

    min is a little more tricky and I came up with:

    =MINIFS(G2:G25000,G3:G25001,0,G2:G25000,">0")

    but if this isn't what you want/meant please explain what you need better. 

Resources