Grade conversion (Excel If function)

  • james.me.robinson
  • Offline
More
8 years 9 months ago #5564 by james.me.robinson
If function was created by james.me.robinson
Hi,
I have set up my excel spreadsheet to show a grade based on the score using the if function.
Is there a way of converting this formula so that it will work in my grade book in iDoceo?
Here is the formula, where AC6 is the total mark column.
IF(AC6>26,"*",IF(AC6>22,"A",IF(AC6>18,"B",IF(AC6>15,"C",IF(AC6>12,"D",IF(AC6>9,"E",IF(AC6>7,"F",IF(AC6>4,"G",IF(AC6<4,"U")))))))))

I can't seem to get the speech marks or text.

Thanks

Please Log in or Create an account to join the conversation.

More
8 years 9 months ago #5565 by bert
Replied by bert on topic If function
Hello James,

Yes, you can do that with what we call a 'grade conversion'.
I'll prepare the article today and I'll post here the link.

In short, the steps are as follows:

If you have not done it already, you must create a text grade type with the steps explained here . Add your texts from A to U
The exact value of the percentages is not important, just make sure that the highest value has a 100%, the lowest 0%

Add now a new grade type but this time choose Grade conversion. Make sure the input max value is what you need (30?) and select as output the Text grade type you have created in the previous step. You will find a table at the bottom left corner that will let you enter the correlation between the score and the grade.
Once finished, assign this new grade type to your score column. Double tap header > Grade type > Your grade conversion.

Here a couple of screenshots of how the grade conversion looks



Attachments:

Please Log in or Create an account to join the conversation.

More
8 years 9 months ago #5566 by bert
Replied by bert on topic If function
Here it is .
Let me know if something is not clear enough!

Regards
The following user(s) said Thank You: james.me.robinson

Please Log in or Create an account to join the conversation.

  • james.me.robinson
  • Offline
More
8 years 9 months ago #5567 by james.me.robinson
Replied by james.me.robinson on topic If function
This is brilliant. Thank you. Really helpful and easy to follow instructions.

This brings me onto one more feature I use in Excel. I usually get excel to auto format the grade compared to the target grade. E.g. When a student has met their target it is yellow. When above target green. When below target red. Is it possible to do conditional formatting like this?

Please Log in or Create an account to join the conversation.

More
8 years 9 months ago - 8 years 9 months ago #5568 by bert
Replied by bert on topic If function
Yes, you will find here the details and it will look like this
Attachments:
Last edit: 8 years 9 months ago by bert.
The following user(s) said Thank You: james.me.robinson

Please Log in or Create an account to join the conversation.

More
8 years 8 months ago #5609 by Wendy
Replied by Wendy on topic If function
this is a fantastic resource! have been exporting my gradebook to excel in order to use an if function for all our tracking needs, yet now I can do it directly in the gradebook, thanks.
just one question, if I may: having set up my text grade type as standard AS grades with % decrements of 10, as far as 40. then 0 for U
however, when using it, the column gives any value less than 40 as E not U. What have I done wrong?! :huh:
thanks

Please Log in or Create an account to join the conversation.

More
8 years 8 months ago #5610 by bert
Replied by bert on topic If function
Hi Wendy, the issue may be due to:
- the columns leading to that final result not having the proper grade types.
- something wrong in the grade conversion's configuration

If you are working with the grade conversion, you should disable it for a moment. Double tap on the column's header > Grade type and change the grade type to something like 'From 0 to 100' for example.

If this yields the right result it is then only a matter of properly configuring the grade conversion, if you can send me a screenshot of your configuration (with the green graph) I'll see if I can spot the problem.

Please Log in or Create an account to join the conversation.

More
8 years 6 months ago #5841 by Richard01902
Replied by Richard01902 on topic If function
You want to put the max mark allowed for each grade in the grade conversion. This confused me at first! See the attached picture for my setup for A Level UMS that works.
Attachments:

Please Log in or Create an account to join the conversation.