Do your results need to be concatenated? What you want to do has to be done independently (two summary tables) and then concatenate the results of two tables to get what you want. I.e. the % rankings are done and then the students are determined in reference to the rankings.
If you still want to do it that way let me know.
What I would propose is a single summary with two columns for each header Teacher/Subject.
In the attached array enter this formula in H4 and fill down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Format as %.
Once filled down copy that range and paste into cells J4, L4 and N4.
Now array enter this formula in G4. and fill down.
Again once filled down copy that range and paste into cells I4, K4 and M4.
By the way your profile shows Excel 2007. You uploaded an *.xls file. If other formulas need to work in earlier versions let us know.
Bookmarks