 Creating feedback sheets for personalized feedback. In this video we're going to go through the mechanics of downloading the data, creating the Excel sheet, applying the comments, and creating mail merge. So, step one. So the process starts by extracting the information you can from your VLE. In our case we're using Moodle, we go to the grader report, we export, we choose an Excel file, and then we just select whatever we happen to want. In this case we're going to download everything, and then we'll click and download the file. And once you have that file you can find it in your downloads folder, double click, and it opens in Excel. And you'll see your files should contain attendance, different grades for different things that they've done, and if you want then you can also add in other things that you've collected online or offline. So here we have a pretty typical situation. The first thing I'm going to do is split the screen, then I'm just going to tidy things up a little bit, so I'm going to make the columns bigger so I can see what's in there, make sure that all the text is wrapped, and so on and so forth, so that we have a nice Excel file to work with. As you do this you can see different pieces inside it, so you can see attendance or whatever you happen to want to have a look at. And then what we're going to do is that we're going to start putting in columns for things that we actually want to report on. So you'll see that Moodle gives you a percentage of attendance, but that's not necessarily to be relied on, so you might want to check the number of presents, dates, absence, and excused, in which case we'll just add them together and divide by whatever the number that they should have attended was, which is 8. Multiply it by 100 so that we can return it as a percent to our students when they're reading the letter, and then we just push enter, and Excel will correct our formula if we've left any important brackets or equals to signs out. Double click on the button in the bottom right-hand corner of that cell and it will auto-fill that all the way down. The next thing I'm going to do is apply my marking scheme. So in this case, I've told students that if they don't do the quiz, they can only achieve 7 out of 10 for this week. If they do the quiz, they get whatever grade they get. 15% is weighted at the quiz and 85% is weighted for the lab. It's also often useful to tell students what they hypothetically would have gotten had they have done that little bit of extra work. So I'll put in a case for the hypothetical grade. So all I'm going to do is, in this case, put in a simple formula that multiplies one grade by 0.85 and the other grade by 0.15, and that'll make up our grade in total. Again, double click to fill that all the way down, and what you'll notice is that some cells don't compute. So what I'm going to do now is I'm going to replace all of the students that got no... or didn't do the quiz, so I'll have a dash. I'm going to replace that with a zero. So Ctrl and H to bring up the find and replace, and then I want to replace zero. I want to put in zero in place of a dash. And I have to be careful when I'm doing this that I match the entire contents of a cell because otherwise it will replace all dashes, even those in the middle of cells, which have lots of other things in them. Now that that's done, you can see that my formula is returning numbers, and we can see the variety of different grades we get. What I'm going to do in the adjacent cell is I'm going to tell Excel that if they have completed the quiz, so if they have completed and passed the quiz, they've got more than four or out of 10 or more, then they can have their hypothetical mark for the week. If they haven't, then what they receive is 7 out of 10 or their computed grade, whichever is lower. So we put in the formula, if W2 is greater than 3.99, so 4 greater, then they can have V2 as their score, which is our hypothetical mark, otherwise it's the minimum of V2 or 7. And then we fill that all the way in, and if we look at some cases we can see it's returning the right grades for the right different scenarios. I had to complete two quizzes and they were capped at either 4 or 7, but it's the same approach you can see here I put in another formula. And ultimately you go through your spreadsheet applying whatever grading formulas you need before you start assigning the comments. So once we have that done, we can then move on, but you'll notice that I keep my spreadsheet in a particular order so that rather than having to write out this formula every time, I can take the formula that I've generated, I can fill it down, but I can also, when it comes to week 3 and week 4, you can just copy and paste that same formula. So you'll notice if I make two blank cells here, if I copy and paste it directly in, Excel will automatically adjust the formula so that it's referring to the adjacent cells and I don't have any issues. The next thing I'm going to do is I'm going to put in their lab total mark, so I'm going to add up all eight marks for the eight weeks and then divide by 0.8 to give me a percentage out of 100. So I've just gone through it very quickly here. I just select the cells separating them each by a comma in the sum function and divide it by 0.8. And that returns me a number out of 100 and I can fill that down. So the sum just selects it all, double click on the button, and it will fill it automatically all the way down. The next thing I want to do is get the hypothetical mark. Well, I've set it up nicely so that the real mark and the hypothetical mark are in adjacent cells. So actually if I just automatically fill this into the adjacent cell using that same button, Excel will automatically adjust the target of that formula to be one across. And so you can see the same formula does twice. Now what I often want to do for students is that I want to point out to them, well, if you've got this many marks, you could have gotten this many marks, this is what you've lost. So in the next cell what I'm going to do is I'm going to put in the hypothetical mark minus the actual mark so that I can tell the students about where they've missed some marks because they haven't done their quizzes. So at this stage then, if you have all of your totals and hypothetical achievements and marks they've lost and attendance and all that kind of stuff, what we're going to have a look at now is actually putting in the comments. So we've got our marks, we now need to apply different comments to different kinds of behavior. Well, if you follow the link below, you'll be able to access a sample copy where I've set out a comment sheet. But in any event what you want to do is create your own comment sheet. So the first thing we're going to do is if we look down the bottom left-hand side, so you may need to change the way the screen is split, we're going to make a new sheet in Excel and then we're going to call that sheet the comment sheet. Now I'm going to open up another file so I'm just going to rename that so that it has a consistent name. It doesn't actually matter what you call it as long as you call it something consistent. And then I'm going to open up another file and I'm going to copy and paste out of that file a comment sheet that I've already prepared. So if we go into a sample comment sheet here you'll see what I have. On the left-hand side I have comments that apply to different grades. And on the right-hand side, as we move across, if I select it all I have the grades that these apply to. And by having it in a separate sheet it's possible to edit these after you have created your formula without having to go back into your particular spreadsheet. You'll see also that there are if statements. We're going to copy and paste those pieces of text. So comments, criteria and a statement that's going to link those. If you look closely at the if statement you'll see that it refers to the comment sheet consistently. It's important that that name matches the name of your comment sheet, whatever it happens to be. We're going to copy it as text. So don't copy the entire cell. Select the text itself. And then we want to put this in for our Labmark comment. So we're going to make a column and we're going to want to put our comment in beside that. You'll have noticed that the comment itself refers to the correct line and the correct space in the comment sheet. But we don't know beforehand which column we're going to be referring to in this. So before we paste it in, we're going to copy it into Notepad. We're going to want it to refer to U2 because that's the one that has the mark in it. So we're going to copy it into Notepad and then we're just going to use the find and replace function to replace in this case BC2 with U2. So ctrl and h will bring up find and replace and then just replace what we have with what we want. And we can do that replace them all at once. Now we're ready to copy and paste that once we have that replacement done. So successfully done. We can select it all, copy and paste it back into the cell that we started with. So since it's a formula, it has to start with an equals to sign. So type in the equals to sign, paste in the formula and push return. And you can see there now that it comes up as that particular comment. Double click and fill them in for all of them. And we can see if we look down that the different cases match the different categories. And it's always an idea to double check that in case it's not working exactly how you think it is or should be working. So we can do the same then for an attendance comment. So we say what our attendance comment is going to be and again we're going to go back to our comment sheet. We're going to select from the comment sheet the row that matches what we want. So in this case our attendance is going to be whatever line it's going to be. We're going to select our particular set of comments matched by our particular if statement copy out the text take it into notepad find and replace for the cell so in this case our attendance is in or so or two so we're going to find and replace and put in or two instead of whatever cell is there. So paste into notepad, find and replace copy and paste that so successfully replaced it all. Copy and paste that and put it back into the attendance comment and again we're going to start with an equals to sign so if you paste it in and you don't put in the equals to sign first it just won't work or it will just treat it as text. So now we have two comments in we can go look at how mail merge is done. There are other things we can use so counts and concatenating comments so that we can comment only in specific circumstances or tailor our comments more than just using if statements but we'll come back to those. So you open up Microsoft Word and we're going to go to the mailings tab so up the top there pretty much in the middle is the mailings tab and that's going to let us perform a mail merge and the first thing we want to do is choose where our recipients are stored. So use an existing list and then on your computer locate the folder in which it happens to be stored. So before you go doing that though you want to make sure your excel file is closed so we just close that file and then we select our file from whatever folder it happens to be stored in. So click click again and once it's open then we have to choose which sheet we want to refer to as well. So in this case we want to refer to the one that's called grades because that's a sheet that has all our information in it. So once we have that done then we want to start writing our letter and usually you start a letter with deer rather than deer and a name we're going to say deer and then we're going to put in the field in the excel file that stores the name. So deer first name after that then it's kind of yours to play with. In our sheet we have comments and we have percentages. So we create a feedback sheet by writing a letter that contains the comments and the feedback sheet in a way that's going to make sense to the student. You can edit fields to give them whatever particular formatting you want so colors and bold and text size and so you're really free to write a letter which relates it exactly as you want to relate. Here you see I'm going to tell the student what their mark was and then I'm going to give them a comment on that mark but it's up to you. There is a little bit of a learning curve. Sometimes you have to remember to put in percentages or not put in percentages and it helps if you're consistent when you're writing your comments to decide either I'm going to have punctuation at the end or I'm not so that you don't have full stops appear or two full stops appear or no full stops appear. So you just have to choose a style and then be consistent. Often I find it useful to make your comments complete sentences and that way you're not worried that you're going to have to create something that matches lots of other sentences. Again you can do it for attendance and all of the other different parts as you see fit. It's also a good idea certainly I find to make sure that it has a full name and a student number and perhaps a course and group identifier so that the feedback sheet can't go to the wrong student. Once we have that done then we're going to preview so there's a preview button in the mailings tab and it lets you see what each particular iteration of the letter is going to look like so you can look out for mistakes and you can see immediately we can spot a common mistake and that is that there are too many decimal places in this number. So to get around that we can edit the field code. Yes you could round the numbers in Excel but it's a lot nicer to just do it at the final point of use. Edit the field code and put in the following text so put in a backslash hash space hash dot zero and what that's going to do is ensure that we only have one decimal place and one decimal place only so if it ends with no decimal places it will still show a point zero and that gives consistency to our letter and we can see if we look through a few different versions of that that it has worked so if we look through a few different lines or a few different potential letters we can see that's worked nicely. So really that's all the steps are certainly to get started on making feedback letters if you watch on you'll see there's one or two other useful manipulations that you can do in Excel if you want to get in touch feel free to email me if you have any questions be happy to try and answer them if you're looking for a template to get started then there should be a link below I hope that's been helpful bye ok well if you're still interested here's one function that I often use which is a count if function and it gives me an assessment of how many cells meet criteria so it's a great way of counting quizzes completed or that kind of thing and you can let you know if your student has passed or failed rather than just seeing if there's a number there at all and again they're the kind of things that I put in comments for another thing that's nice to use is a concatenate function and if you use that with the if function then you can check some score so for example the cost of a quiz is stored in AQ and if the cost of not doing quizzes is costing more than 2% I want to let them know that and I want to give them a comment if it doesn't then I don't want them to see anything that would work if I just had a simple comment but I couldn't also put in a percentage because the students would see no comment but they'd have 1.5 or 0 or whatever as well so by using the concatenate I can put together the comment and the percentage and the percentage sign as one possibility or if the student hasn't met that criteria I can put it in as another one and you can use that to say maybe the maths learning centre is something you need to visit because you've struggled in this particular criteria but if you don't want a student to see that then you don't want them to see that at all so it's just another useful function inside Excel alright that is all for now thanks for watching I hope that's been helpful