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(D...
excel
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 01, 2021

    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) ) )

     

Resources