Trying to develop a predicted grade spreadsheet

[burnsie]

hello.

I want to develop a spreadsheet where you can type in grades that a students obtains during their course and it can predict their (current) grades.

Each unit they do is grades as P, M or D. Each grade is attached a point score so p=7, M=8, D=9

Once they have grades, all these scores add up, and the resultant score is placed in a grade boundary. is a score of 132 would sit between 130-134 and therefore = and overall MPP. However, a total score of 148 would fall between 146-150 = DDM

So, I'm wondering how to develop this spreadsheet

I obviously need a some form of look-up formula for the grade (ie if you put D in then excel understands this is 9) and the the overall grade (ie MMD etc).

Any ideas where to start?

[Richard Buttrey]

Hi,

Exactly as you suggested - with a VLOOKUP - two in fact

Create a Grade table with P, M or D on column 1 and 7, 8 and 9 in column 2. So assuming this is A1:B3 and the grade is in D1 then the VLOOKUP is

Please Login or Register  to view this content.

Similarly for the boundary . With 0, 130 & 146 in say G1:G3 and H1="", H2= MPP and H3 = DDM then with the total score in I1 use

Please Login or Register  to view this content.

[burnsie]

can you have these gribs on a separate sheet, and if so would it jus be ="sheet2" then into the VLOOKUP??

[Richard Buttrey]

Hi,

You can have them wherever you want. I see that I didn't include the 'Sheet1' text in the second example. In fact you only need to qualify cell addresses with sheet names if the VLOOKUP refers to stuff on another sheet. If everything is on the same sheet then you don't need to qualify with a sheet name.

[burnsie]

cool..... thanks. i think ive got it!

[burnsie]

so, a follow up question

now i have a table with grade boundries ie;
a1 might have a score of 1300, b1= 1339, c1= MPP (the overall grade)
a2 might have a score of 1340, b2= 1379, c2= MMP (the overall grade)

i want to be able to right a score in a cell, for example, 1345 and the spreadsheet to convert that to a grade letter (ie MPP)

errrr.......

[Richard Buttrey]

Hi,

With A1=1300, A2=1340, A3=1380, B1=MPP,B2=MMP,B3=???, D1= 1345

Please Login or Register  to view this content.

[burnsie]

IM a little lost and have (tired) to upload my Excel file to date....

[Richard Buttrey]

See attached.

With the grade score for which you're wanting to return the grade in I1 use

Please Login or Register  to view this content.

You don't actually need to specify the top and bottom of the grade boundaries so you could delete column F and change the 3rd parameter in the VLOOKUP to '2'

[burnsie]

I cant post a reply very well on this forum, it keeps timing out.

anyway, if this reply works, many thanks, you've only gone and done it!