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

     

mtarler's avatar
mtarler
Silver Contributor
Nov 01, 2021

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