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

1 Answer 1

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

4
  • Thank you so much! I'm going to study this so I can learn why! lol
    – Ashkaan
    Commented Apr 5, 2019 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
    Commented Apr 5, 2019 at 17:54
  • @Ashkaan sure it is. answer updated.
    – user0
    Commented Apr 7, 2019 at 17:58
  • 1
    You're a wizard. Thank you so very much. You nailed it.
    – Ashkaan
    Commented Apr 8, 2019 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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