Forum Discussion
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?
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
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) ) )SergeiBaklan That's exactly what I was looking for. Thank you very much. 🙂
ekathebauer , glad it helped
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.
