Scenario

30% of students can receive A+/A.

In a class of 20, 8 student get A's but only the top 6 scores can qualify.

The other 2 exceed the 30% rule. How do I reduce the lowest A scores down to a B+ without affecting the other A scores or the other 12 students?

Lets use Column A and B, and Rows 2-21

## Replies

## Please login or register to join the discussion.

## picture-151130-1399621153.jpg

Darren Freeman .....

he always used to help me with my maths home work at school. I think he's on friends re-united ... remember to say Steve say's hi!

I am not sure if I understand your question. Only 6 out of 20 (30%) can get grade A+/A. But only the top 6 scores can qualify. so 6 out of 8 to qualify?

Does it mean that the other 2 will not qualify? Do the remaining 14 students can only receive grade B+?

grading students

Yes, the lowest two scores do not qualify and will receive a B+. Any student with a B+ or below will keep the grade they earned.

I failed to mention that I need a formula that ignores 'zero' scores.

If there was a way of uploading an attachment I would to make life easier.

By the way, I am asking for an Excel formula.

An exam room usually has physical rows and columns ...

Was that explicit in OP? After all, an exam room usually has furniture in physical rows and columns, doesn't it?

Obvious mistake

You've made a mega error in your question - surely you mean that in our remarkably clever age all students will get A+s for turning up (or not turning up but having their picture in the Daily Wail with their sob story of why they couldn't get out of bed) and the question is actually 'will any of them get a job when prospective employers realise that they can't spell their own names or count to three?'

And I'd rather not bother with columns or rows because the answer is none.

Easy-peasy, do I get a gold star?

## picture-151768-1312549468.jpg

@nhush

You've fallen into the old trap of trying to get AWEB members to do your homework, or at least your question looks like homework. Not a popular move.

Hint ignore the grades and concentrate on the scores.

I received a sensible answer from Cantona1 but a lot of nonsense from everyone else.

I honestly joined this website thinking that someone could help me - obviously, I was wrong.

## picture-172516-1313502054.jpg

Stop whining!

Using the data from your previous post, the correct answer is reproduced below.

The formula in cell C3 (and copied down) is:

"=IF(B3>94,"A+",IF(B3>89,"A",IF(B3>84,"B+",IF(B3>79,"B",IF(B3>74,"C+",IF(B3>69,"C","F"))))))"

although a lookup table would be better,

and the formula in cell D3 (and copied down) is:

"=IF(AND(RANK.EQ(B3,B$3:B$22,0)>ROUND(COUNT(B$3:B$22)*0.3,0),LEFT(C3,1)="A"),"B+",C3)"

ABCD1 OriginalFinal2StudentScoreGradeGrade3110FF4291AB+5392AB+6494AA7574CC8693AA9735FF10856FF11945FF121077C+C+131185B+B+141288B+B+151384BB161489B+B+171599A+A+181698A+A+191797A+A+201896A+A+211990AB+222090AB+

Thanks a lot. It worked!!!

You must be a Brit or you simply like "Porridge."

I really appreciated your help before...Can I be bold enough to ask one more favour?

In the same Excel file, I need to find the lowest, median and highest scores. I have no problem with the formula for median and highest, but I have tried in vain to find a Vlookup with Min that ignores zero numbers. The problem is that some students drop out of class and it is impossible to simply delete rows, so their 'row' registers '0's.

Can you alter the formula so that it ignores the zeros.

This is what I am using now.

=VLOOKUP(MIN(S12:S31),S12:T31,2,0)

Use

=IF (B3=0, " ",VLOOKUP(MIN(S12:S31),S12:T31,2,0))

If the value you are looking for (B3) is zero, return a blank

Hi Cantona1,

I don't understand where the B3 comes from. And also the phrase'

"If the value you are looking for (B3) is zero, return a blank"

I need a vlookup for T12:31 to correspond to =MIN(IF(S12:S31>0,S12:S31,MAX(S12:S31)))

Min excluding 0's

Use following formulae

=SMALL(B2:B21,COUNTIF(B2:B21,0)+1)

You will get minimum score excluding zeros.

Regards

Zubair Edhy

PS. It seems unfair to limit number of students to a particualr grade, if some one is making up to it, s/he should get the grade.

Thanks

Yes it is unfair, but that is the University policy

## picture-172516-1313502054.jpg

That's clever...

... but what happens if you get a student who's still on the course who actually manages to score [french_accent]

nil points[/french_accent].Hah!

That would be a miracle. Simply attending every class would give 10%.

## Please login or register to join the discussion.