Today just for fun wanted to play with something, and the outcome is a randomly generated class marks list and some calculations. For my usage, I used Libre Office Calc as I am on Linux. For the sake of generated student names, I used the serial number from 1 to 60 in a template “Student$ Name” using the Ubuntu shell and the following command
seq 1 60 | while read dn; do echo "Student${dn} Name"; done
I could have used bash itself to generate the marks in random with the following
seq 1 60 | while read dn; do echo "Student${dn} Name,$(( RANDOM % 80 + 20 ))"; done
but instead I used ChatGPT to do this one with the prompt
generate 60 random numbers one in a line ranging from 20 to 99
And copied the result into my text editor http://bz2.in/texdit to clean up any extra comments and then copied it into the marks column.
Now just for visibility leaving out 5 rows after the marks entry the following formula was entered.
=AVERAGE(D2:D61)Note that the first two columns (A and B of the sheet) were left blank and C contains the Random Names so D is the marks column, the first row is column names “NAME, MARKS’ etc
Now to see if I can implement the grading system
The following formula was copied into F2 and then copied and pasted into all cells F3::F61, when you copy from the spreadsheet and paste it into another cell, the cell references in the formulae get updated automatically and the whole sheet reflects the result.
=IF(D2>=97,"A+",IF(D2>=93,"A",IF(D2>=90,"A-",IF(D2>=87,"B+",IF(D2>=83,"B",IF(D2>=80,"B-",IF(D2>=77,"C+",IF(D2>=73,"C",IF(D2>=70,"C-",IF(D2>=67,"D+",IF(D2>=65,"D",IF(D2>=35,"E","F"))))))))))))
I know the formula above is a bit complicated. Hence I will add the mark sheet template.xls for download for a reference.
To explain the above formula it is actually the simple IF(Condition, When True, When False), but with the When False next lower condtion and thus nested all 11 conditions and final when false is the 12th condition.