Mark Piesse

Am assisting a teacher friend in automating a workbook she uses to record coursework and exam scores for her classes. The data on the first sheet is all manual entry and this then needs to feed a summary sheet giving the top 4 scores from 11, of which 1 has to be from 1 of 4 modules. Using my limited knowledge of formulas, I have been able to find the highest score from the 1 of the 4 mandatory modules, copying both the score and subject into the sumary sheet. The remainder of the summary sheet has a few lines of manual entry and the rest of the calculations I have been able to do.

But I am having problems obtaining the next three highest scores and subject from the remaining scores and subjects. Excel won't allow any more than 7 levels of nesting in a formula and I don't know how to use VBA so have ground to a halt and was hoping someone here may be able to help.

Thanks

Mark

bob269

Not sure how your data is layed out or entered but i'd look at creating a pivot table on a 2nd worksheet linking the data from the first incorporating a simple sort by score.

Mark Piesse

Thanks, will have a look at that.

The data is laid out in an 12 column x 32 row grid, name on left, module names across the top and the scores. The mandatory module is one of those marked with a *.

:Ath*:Bad :Bbal:Cri :Fit*:Foot:Hoc :Rou :Rug :Sur*:Swim*:
Name : 10 : 9 : 5 : 7 : 9 : 8 : 7 : 7 : 8 : 10 : 9 :

Dracoro

Is the teacher a maths teacher? Must know one at least They could devise the logic/formula. Excel is merely the tool. There's quite possibly many ways to do this. Score the 4 main topics by multiplying them by 100, thus all those with 100+ clearly contain one of the 4 main topics. Afterwards, take away the 100s as appropriate to get the 'actual' score. Bascially to 'score' a row, multiply all of the cells by 1 (or 100 if one of the 4 main topics) to get a 'final' score. Sort them (or max, max-1, max-2 etc. to get 2nd, 3rd etc.)

Probably a crap solution but it'd work. Like I say, get one of the maths teachers to earn their wage and devise the formula for you

bob269

Theres a command called Large which returns the X highest score in a range.

The syntax would be =LARGE(A1:A10,X)

Where A1:A10 would be your score range, and X would be the position you want to return, i.e. 1,2,3 for 1st 2nd 3rd highest. I assume if theres 2 scores equal it would return the first one it finds.

As Dracoro says tho, there probably is much easier ways to do this, i'll have a think tomorrow if I get a spare 5mins.

DemonDave

iTrader: (13)

use the rank function



http://www.cpearson.com/excel/rank.htm

Mark Piesse

Demondave, , that was just the thing I needed, had been looking at the statistical options, but couldn't fathom it out. Even now my brain is hurting from using a combination of formulae from the link you provided.

Many thanks

Mark

DemonDave

iTrader: (13)