Forum Discussion
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...
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) ) )
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
- I am now being asked to make sure that 0.04 is written in only one of the three columns.
Is there a way to make a formula that if Column E and F have "0.04" then column D becomes "0", otherwise it keeps it's original values.
Or would I need to create a helper cell that reads column D unless columns E and F say 0.04 then input 0.
