Student Exam Reports - Box&Whisker Plots

[JoeBurrows]

Hi Community, I'm really stuck on a challenge I'm facing at work. I'm a teacher in a school. After a series of end-of-term exams, management wants pupils to receive a bespoke report showing how they did individually. This is easy enough to knock together in Word-Excel-Mailmerge--> Letters. However, they also want a box&whisker plot per exam, individual to that pupil. It would ideally be something like this:

Student name: Student number

Subject 1: score: rank : [box & whisker plot to show relative position].
Subject 2: score: rank : [box & whisker plot to show relative position].
Subject 3: score: rank : [box & whisker plot to show relative position].

Can VBA help me achieve this on a mailmerge --> Letter?

Thank you for your consideration. In hope!!

Joe

[MrShorty]

Excel should have no trouble creating the box&whisker plots. If the built in box&whisker template cannot handle the individual student's score, then you can build the box&whisker as a stacked bar/column chart (https://peltiertech.com/excel-box-an...ams-box-plots/ is a good tutorial. In your case, replace the average with individual student score).

I have no experience at all with mail merge or how to best bring that into your student letters. With my limited skill set, I would build the spreadsheet, link the spreadsheet elements into the form letter in word, then loop through the list of students, updating the spreadsheet for each student. If I've linked the word and excel files together correctly, every time I update the spreadsheet, the word file should update and I can print/send the letter before going on to the next student. Exactly how best to do that is well outside of my experience.