I am working with a spreadsheet that, for simplified/example purposes looks like the setup below:
Math Science English History
Charles 80 79 87 83
Dana 85 82 80 90
Samuel 90 92 94 84
Amber 81 76 85 89
Kathleen 82 83 88 79
Patricia 76 92 94 88
Mark 71 77 92 87
Dennis 92 89 79 81
What I want to do is have a section above it with the same setup, but in lieu of the scores/grades, have a number 1-4 indicating which quartile they fall into (1 being the best)-i.e. if you sorted each column 1 would be the top 25% of scores, 2 would be the next 25% of scores, etc. etc. I want the formula to look-up the name in the table below (not rely on the name being in the same position) and produce what quartile the it falls into (based on the data in that column). I have unsuccessfully taken a few stabs at it using INDEX/MATCH and the QUARTILE function (which doesn't seem to calculate quartiles in the way I'm looking for), so I would appreciate any help. I have pasted an example of the result I'm looking for below. Thanks!
*I have a pretty large volume of data that will be changes/updated monthly, so I'm looking for an option with little-to-no manual re-arranging (i.e. pasting & sorting, etc.). Helper cells that would automatically pull from the original data are fine though.
Math Science English History
Charles 3 3 3 3
Dana 2 3 4 1
Samuel 1 1 1 3
Amber 3 4 3 1
Kathleen 2 2 2 4
Patricia 4 1 1 2
Mark 4 4 2 2
Dennis 1 2 4 4
Bookmarks