When you write your end of year report comment, you will be commenting on the student's progress or lack thereof, and the actual mark received. If the mark is low, and/or the progress is negative, you will want to suggest some kind of remedy. These comments need to be based on verifiable information. This got me thinking about Nested IFs in spreadsheets...
When you have marks and/or rankings in a spreadsheet, you can use a formula to convert them to text. That text can form the basis of a comment.
Here is a simple setup I have created:
The basic syntax of an IF statement is:
=IF (condition, if the condition is met, if the condition is not met)
To create a nested IF, just add another IF into the “not met” section - ie after the second comma.
The formula in D2 in the example above is as follows:
=if(C2>7," She has made pleasing progress in the final quarter of the year.",(if(C2>-4," She has worked consistently this year."," Her lack of progress in this subject has been disappointing.")))
In English, this means:
If the value in C2 is greater than 7, then give the comment “She has made pleasing progress in the final quarter of the year.”, and if it is not greater than 7, then, if C2 is greater than -4, give the comment “She has worked consistently this year.” If it is not greater than -4, give the comment “Her lack of progress in this subject has been disappointing.”
The formula uses the IF function, twice, ie the second IF is nested inside the first one.
- Note that we use " " to indicate a text string in a formula. If you are introducing numbers, you do not need the " ".
But, it gets more complicated. What if a student makes no progress, but gets a great mark, say she goes from 2nd place to 1st place and gets 90%?
If you look at the example spreadsheet below, you will see that I have set up 9 conditions as follows:
Hi Final Mark |
Medium Final Mark |
Low Final Mark |
|
Improved |
She has made pleasing progress in the last quarter of the year, achieving a commendable final result. She is encouraged to maintain this positive approach to her work right from the start of next year. |
She has made progress this year, achieving an acceptable result. With greater attention to ... next year, she could achieve a better final mark. |
Although she has made some progress this year, she will need to maintain her efforts to improve. With greater attention to ... next year, she could achieve a better final mark. |
Stayed the same |
She has worked consistently this year, achieving a commendable final result. She is encouraged to maintain this positive approach to her work next year. |
She has worked consistently this year, achieving an acceptable result. With greater attention to ... next year, she could achieve a better final mark. |
She has not made sufficient effort during the year, hence this disappointing result. With greater attention to ... next year, she could achieve a better final mark. |
Went down |
Her final result is acceptable but she could probably have achieved a better mark. She is encouraged to pay attention to .... next year. |
Her final result is disappointing as she was probably expecting a better mark. She is encouraged to pay attention to .... next year. |
Her final result is disappointing. She needs to devote herself to ... next year so that she can remedy this situation. |
These are in a table starting at G7 in the top left corner. I have then used nested IF statements to choose the comments based on the mark and the progress.
You can view the spreadsheet in action here (make a copy so you can play with it).
- Note the use of conditional formatting for the colours.
The formula is as follows (with spaces and indents to make it clearer what is going on):
=if(A8>69,
(if(D8>5,$H$8,
(if(D8>-5,$H$9,$H$10)))),
(if(A8>53,
(if(D8>5,$I$8,
(if(D8>-5,$I$9,$I$10)))),
if(D8>5,$J$8,
(if(D8>-5,$J$9,$J$10))))
)))
- Note the use of the $. That is called an absolute reference. What it means is that when I copy or autofill that formula, it will not automatically change those cell references.
To identify where the various comments are introduced, follow the money! Look for the $ signs, and see the way the formula works through the H column, then I and finally J.
If you are at a co-ed school, you will have to make a nesting that chooses he/she for the whole formula. But since you now understand nested ifs, and since I am at a single-sex school, you will have to work that out for yourself!