1

I'm tracking performance for some athletes and we need a way to see their "Best" workout numbers meaning the highest numbers of (rep × weight).

The table looks like this:

Before

The reps and the weight are different columns and the Dates are 2x merged.

In the above table, the result should read:

enter image description here

0
  • paste in C2 and drag down:

    =ARRAY_CONSTRAIN(SPLIT(REGEXEXTRACT(QUERY(TRANSPOSE(QUERY(E2:J2, 
     "select E*F,E,F,G*H,G,H,I*J,I,J 
      label E*F'',G*H'',I*J''")), , 999^99), 
     MAX({E2*F2, G2*H2, I2*J2})&" (.*)"), " "), 1, 2)

    0


  • fix for empty cells:

    =ARRAYFORMULA(ARRAY_CONSTRAIN(SPLIT(REGEXEXTRACT(QUERY(TRANSPOSE(QUERY(
    IF(E2:J2="", 0, E2:J2), 
    "select Col1*Col2,Col1,Col2,Col3*Col4,Col3,Col4,Col5*Col6,Col5,Col6 
    label Col1*Col2'',Col3*Col4'',Col5*Col6''")), , 999^99), 
    MAX({E2*F2, G2*H2, I2*J2})&" (.*)"), " "), 1, 2))

    0

  • Thank you so much! I'm going to study this so I can learn why! lol – Ashkaan Apr 5 at 15:43
  • I get an error for blank spaces. Is there an easy way for the QUERY function to see blank spaces as zeros? – Ashkaan Apr 5 at 17:54
  • @Ashkaan sure it is. answer updated. – user0 Apr 7 at 17:58
  • 1
    You're a wizard. Thank you so very much. You nailed it. – Ashkaan Apr 8 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.