Find the median of Column if Header contains specified text

[chyeenne]

Hello, is there VBA code i can write to find the median of Column if Header contains specified text? For example, if the header contains "Test Time_X", it will be selected and the median of all columns selected will be calculated and input into D1. Thank you so much for your help!

Book1 (2).xlsm

[HansDouwe]

Please try in D1 and copy down:

Please Login or Register  to view this content.

[chyeenne]

thank you so much for your reply! would there be a way that the formula wont read the entire header? eg Test Time_X VS Test Time_X_Oct. Thank you so much!

[HansDouwe]

Please try in D1 and copy down:

Please Login or Register  to view this content.

[Glenn Kennedy]

I think that Hans is incorrect. His is returning the median of the first row, only:

=LET(h,B$5:M$5,d,B$6:M$99,MEDIAN(INDEX(d,SEQUENCE(ROWS(d)),FILTER(SEQUENCE(,COLUMNS(h)),C1=LEFT(h,LEN(C1))))))

[HansDouwe]

I think Glenn is right.

Another way to fix my mistake is to replace the INDEX function in my formula with the CHOOSECOLS function.
Then the SEQUENCE(ROWS(d)) does not need to be added to the formula.

Please Login or Register  to view this content.

[chyeenne]

that worked wonderfully! thank you so much <3

[chyeenne]

thank you so much! <3

[Glenn Kennedy]

You're welcome. Thanks for letting us know that you got an answer.

---

Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.