Edge Media is an online-learning provider based in Cape Town. We were contracted to provide a reporting solution:
Data management of Excel held data
Edge hold a large amount of exam results for their students. They required a VBA solution to collate this data for reporting purposes. The data structure was non uniform – a result of different exams having different structures. The final outcome was one VBA macro which covered all-bar-one case and a second VBA solution to cover the a-typical case. The project manager engaged an additional freelancer to provide the VBA macro, defined and communicated the requirements of the tool and tested the macro.
Reporting with Pivot Tables
Once the data was collated into one file, averages were calculated (with another small macro) to allow analyses of results. To view the data pivot tables were created and from that, pivot graphs, with slicers so that the average overall performance of each college or average outcomes for each exam could be viewed.
With the tools developed to allow past and future data to be quickly collated a training morning was organised to engage the stakeholders and ensure that they understood the use and dangers of use of the pivot table. A clear danger of any pivot table using averages is that the table can merrily provide averages of averages, which are as useful as a chocolate tea pot. The staff were encouraged to engage with the tool and after a week a follow up training session organised for them to ask specific questions.