Using Large to pick highest numbers from a range of cells but only once

[CatCatJake]

I'm using an excel sheet to choose highest grades from a range of qualifications using the Large function to fill assessment slots. The first, second and third highest grades are chosen from a fairly limited group that will stay constant for all students.

=IF(ISERROR(LARGE($J$4:$N$4,1)),"blank", LARGE($J$4:$N$4,1)) for the first slot picks the highest
=IF(ISERROR(LARGE($J$4:$N$4,2)),"blank", LARGE($J$4:$N$4,2)) for the second slot picks the next highest

and =IF(LARGE($J$4:$N$4,3)= 0,LARGE($O$4:$S$4,1),LARGE($J$4:$N$4,3)) for the third slot picks the third highest unless there is no third highest and then it moves on to the next group of qualifications that are available but not constant for all students.

at this point it gets tricky as each time I pick a value using large I cannot use that qualification result again

Can anyone help

CatCatJake

 

[alacrity59]

First off the numbers you are looking at need to be rounded. You pick the level. I'm just pointing out that 76.567 is different than 76.568 but if you have formatting set to 2 decimal points they will both look like 76.57.

Once you have found the largest number you could use counta() to find the number if incidents. The second number wound be Large(therange,counta(previous large number(s)) + 1). The previous large numbers would have to be accumulated somewhere . . .perhaps in a helper column.

 

[Somendra Misra]

Hi,

Welcome to chandoo.org forum.

Your post is not clear. Instead of posting your formulas, can you post your requirement in a more clear way with the data presented in your sample sheet. Like what are these slots? What do you mean by qualification? Why are you using only J4:N4 range in your formula?

Regards,

 

[alacrity59]

I should have said countif rather than counta. Example attached here.

 

[CatCatJake]

Sorry guys - will try to make things clearer this time

Sheet is designed to calculate the best 8 scores achieved by students across a range of subjects.

This would be straightforward were it not for the fact that the rules of selection is not just by highest score but also by groups of subjects and that each score can only be chosen once

So slots 1 and 2 are drawn from five subjects as a simple highest / 2nd highest
Slot 3 is drawn from the first five subjects but if a student doesn't have a grade it needs to expand its reach to the highest from the next group of five subjects (which I have been able to do) If slot 3 is drawn from the first five subjects the sheet will move on to pick the three highest grades for slots 4 to 6 from the next group of subjects.

The problem now arises that if I take the highest grade from this second group of subjects I cannot use this subject or value again when I look to populate the next three slots.

Slots 4 to 6 can be populated from both subject groups B and C.

I have re- labeled the file

To summarize

Slots 1 and 2 - highest values from subject group A
Slot 3 - highest value from subject group A or if no value present, from group B (cannot be used again) in slots 4 to 6
Slots 4 to 6 - highest values from subject groups B & C

Its the formula for slot 3 that is causing all the pain

How do I make sure that the value for slot 3 if it comes from subject group B is not used again for that student?

many thanks

CatCatJake

 

[CatCatJake]

Sorry guys - I have amended some details from the previous post to make things even clearer

Sheet is designed to calculate the best 8 scores achieved by students across a range of subjects.

This would be straightforward were it not for the fact that the rules of selection is not just by highest score but also by groups of subjects and that each score can only be chosen once

So slots 1 and 2 are drawn from five subjects as a simple highest / 2nd highest
Slot 3 is drawn from the first five subjects but if a student doesn't have a grade it needs to expand its reach to the highest from the next group of five subjects (which I have been able to do) If slot 3 is drawn from the first five subjects the sheet will move on to pick the three highest grades for slots 4 to 6 from the next group of subjects.

The problem now arises that if I take the highest grade from this second group of subjects I cannot use this specific subject value again when I look to populate the next three slots.

Slots 4 to 6 can be populated from both subject groups B and C.

I have re- labeled the file to correct my formulae and to indicate the two cells I am having difficulty with.

To summarize

Slots 1 and 2 - highest values from subject group A
Slot 3 - highest value from subject group A or if no value present, from group B (cannot be used again) in slots 4 to 6
Slots 4 to 6 - highest values from subject groups B & C

Its the formula for slot 3 when it can't find the 3rd highest value from subject group A that is causing all the pain.

How do I make sure that the value for slot 3 if it comes from subject group B is not used again for that student?

many thanks

CatCatJake

 

[alacrity59]

I'd approach it as thinking about stroking out choices once they are chosen.

One of the difficulties I talked about earlier was that it is possible to have duplicates . . . I.e., two scores of 8 for example. How would we identify, in a way that Excel can find it, which 8 we used to satisfy the condition we are searching for?

Let's imagine that we add a second set of columns where we copy the scores for each subject + column()/1000. This would make a duplicate score of 8 just slightly different than another 8. We then use our large functions on these new columns to find the first couple of scores in the limited number of subjects.

Now we build a third set of columns for the subjects and use an if statement with vlookup and iferror to see if the score from the second set of columns exists in the first picks then 0 otherwise use the score from the second set of columns.

Now you have a third set of scores excluding the ones already used . . . and now you can continue on with picking the next largest etc. from this new set of scores.