Search and return lowest value (sounds easy but it's trickier than it sounds)

[Shmerty]

Hi all,

Below is a sample of Scottish education attainment data. the data is generated by other software and I can't control the output. I just have to try and find formula that will work with the data I get given.

[TABLE="width: 918"]
<tbody>[TR]
[TD][B]Forename[/B][/TD]
[TD][B]Surname[/B][/TD]
[TD][B](A) Subject Level[/B][/TD]
[TD][B](B) Subject Level[/B][/TD]
[TD][B](C) Subject Level[/B][/TD]
[TD][B](D) Subject Level[/B][/TD]
[TD][B](E) Subject Level[/B][/TD]
[TD][B](F) Subject Level[/B][/TD]
[TD][COLOR=#ff0000][B]Output like…[/B][/COLOR][/TD]
[/TR]
[TR]
[TD]Liam[/TD]
[TD]Smart[/TD]
[TD]NAT3[/TD]
[TD]NAT3[/TD]
[TD]NAT3[/TD]
[TD]NAT4[/TD]
[TD]NAT3[/TD]
[TD]NAT3[/TD]
[TD][COLOR=#ff0000]5@3[/COLOR][/TD]
[/TR]
[TR]
[TD]Will[/TD]
[TD]Smith[/TD]
[TD]NAT5[/TD]
[TD]NAT5[/TD]
[TD]NAT5[/TD]
[TD]*[/TD]
[TD]NAT5[/TD]
[TD]*[/TD]
[TD][COLOR=#ff0000]5@5[/COLOR][/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Jones[/TD]
[TD]NAT3[/TD]
[TD]NAT3[/TD]
[TD]*[/TD]
[TD]NAT3[/TD]
[TD]NAT3[/TD]
[TD]NAT3[/TD]
[TD][COLOR=#ff0000]5@3[/COLOR][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Russell[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD]NAT3[/TD]
[TD]NAT3[/TD]
[TD]*[/TD]
[TD][COLOR=#ff0000]5@3[/COLOR][/TD]
[/TR]
[TR]
[TD]Maurice[/TD]
[TD]Malpas[/TD]
[TD="align: right"]44[/TD]
[TD]NAT4[/TD]
[TD]*[/TD]
[TD="align: right"]45[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD][COLOR=#ff0000]5@4[/COLOR][/TD]
[/TR]
[TR]
[TD]Dennis[/TD]
[TD]Taylor[/TD]
[TD]*[/TD]
[TD]NAT4[/TD]
[TD]NAT5[/TD]
[TD]NAT3[/TD]
[TD]*[/TD]
[TD]*[/TD]
[TD][COLOR=#ff0000]5@3[/COLOR][/TD]
[/TR]
</tbody>[/TABLE]

The red column is the output I'm trying to achieve using formula. At first I started writing a SUMPRODUCT() solution thinking I could just combine that with RIGHT() and pick off all the numbers at the end of the "NAT*" cells. But this wouldn't work because pupils in Scottish schools are judges on 3 levels:

How many pupils pass:
5x Level 3 qualifications (5@3)
5x Level 4 qualifications (5@4)
5x Level 5 qualifications (5@5)

So I need help with the formula that will tell me which of the above groups the pupils belong to.

If it isn't possible with Excel formula then I'll write a VBA solution. I am trying to keep this spreadsheet Macro free for as long as i can just now though.

This is how I've designed the formula on paper:

1. Search through the range of 6 horizontal adjacent cells looking for all the cells that start "NAT", ignoring all other values as I can't control those
2. Add any "NAT" cells into a range/array
3. Check the digit to the right of the text "NAT"
4. Find the lowest value from step above
5. Concatenate it to the string "5@" & ... if "NAT" was found

Why am i finding this so difficult? Is it possible?

Is there any way of attaching the wee test Spreadsheet that I am using?

Thanks,
Liam

 

[Shmerty]

EDIT: it's even trickier than my explanation above but I can no longer edit.

Pupils are enrolled for 6 qualifications. If five of those are at Level 5 and one is at Level 3, they are still in the 5@5 group.

If a pupils is enrolled on four Level 5 qualifications, but the other two are Level 3, that pupil falls into the 5@3 group

A pupil who is enrolled on four Level 5 qualifications, one Level 4 and one Level 3 would fall into the 5@4 group (because they can still achieve 5 qualifications @ Level 4 or above)

A pupil who is only enrolled on one Level 3 course is still put into the group of 5@3.

What happens is that if I can filter all the 5@3 pupils and print of a list, i can get them together and work on level 3 assessments to ensure they finish school with at least 5 qualifications. Do the same with the 5@4 groups and 5@5.

So my 'logical' thinking is this now:

This is how I've designed the formula on paper:

1. Search through the range of 6 horizontal adjacent cells looking for all the cells that start "NAT", ignoring all other values as I can't control those
2. Add any "NAT" cells into a range/array
3. Check the digit to the right of the text "NAT"
4. The threes, fours and fives need to be counted
5. Nested IF statement to check the above count??
6. Concatenate it to the string "5@" & ... if "NAT" was found

 

[jmacleary]

Hi there. This formula in I2 and copied down will produce the output you show:

="5@"&MIN(IF(COUNTIF(C2:H2,"NAT3")>0,3,99),IF(COUNTIF(C2:H2,"NAT4")>0,4,99),IF(COUNTIF(C2:H2,"NAT5")>0,5,99))

(This formula will produce a result of 5@99 if there are no NATx values found)

I am sure there may be cleverer answers out ther though!

 

[jasonb75]

Assuming that your sample table is in A1:I7, try this in I2 and fill down

="5@"&LOOKUP(9,AGGREGATE(14,6,RIGHT(C2:H2,1)/(LEFT(C2:H2,3)="NAT"),{1,2,3,4,5,6}))

 

[jmacleary]

OK I have read your revised rules. See if this does it for you (a total of less than 5 of any NAT values will result in 5@3)

="5@"&IF(COUNTIF(C2:H2,"NAT5")>=5,5,IF(COUNTIF(C2:H2,"NAT5")+COUNTIF(C2:H2,"NAT4")>=5,4,3))

 

[Shmerty]

Thanks to both of you! Issue now solved. I was wayyy over complicating things.

Thanks,
Liam