nhush
Blogger
Share this content
0
16
1232

reducing student grades

reducing student grades

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.

03rd Jul 2012 08:29

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!

Thanks (0)
avatar
03rd Jul 2012 17:10

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+?

Thanks (1)
avatar
By nhush
skhan
04th Jul 2012 02:57

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.

Thanks (0)
avatar
By dstickl
mydoghasfleas
04th Jul 2012 07:45

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

nhush wrote:

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

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

Thanks (0)
avatar
By Flash Gordon
03rd Jul 2012 17:36

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?

Thanks (0)
04th Jul 2012 09:30

@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.

 

Thanks (0)
avatar
By nhush
04th Jul 2012 13:53

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.

Thanks (0)
04th Jul 2012 14:32

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 (1)
avatar
By nhush
MM
04th Jul 2012 14:40

 

 

Thanks a lot.  It worked!!!

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

Thanks (0)
avatar
By nhush
MM
04th Jul 2012 15:04

 

 

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) 

 

Thanks (0)
avatar
04th Jul 2012 16:03

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

Thanks (1)
avatar
By nhush
05th Jul 2012 11:34

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)))

 

 

 

Thanks (0)
avatar
By edhy
06th Jul 2012 08:46

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 (1)
avatar
By nhush
ireallyshouldknowthisbut
06th Jul 2012 10:53

Thanks

 

Yes it is unfair, but that is the University policy

Thanks (0)
06th Jul 2012 10:38

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].

Thanks (1)
avatar
By nhush
06th Jul 2012 10:55

 

 

Hah!

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

Thanks (0)