ELE3915 Excel Programming and Automation - Take Home Exam Part 1: SUMIFS for dynamic arrays

ELE3915 - T ake Home Exam - May 2023 CourseNana.COM

This exam consists of two equally-weighted parts. Y our final submission (per group, via WiseFlow) should consist of two excel files (one for part 1; one for part 2) and a text file (e.g. .txt or .ts is fine) which contains the ExcelScript code you are asked to write in the final question of Part 2 below. CourseNana.COM

Part 1 CourseNana.COM

Part 1 of the exam is contained in the workbook “exam_part1.xlsx” . Y ou are required to write 6 functions, one for each worksheet (except “Introduction”), each with the same name as the corresponding worksheet. These should be placed inside a module named “Solutions” . Each function is worth the same number of marks. CourseNana.COM

The worksheet “Introduction” provides additional details, and should be read first. F ollowing this, details of each function you need to create and the tests it needs to pass is given on each worksheet. CourseNana.COM

Here are some hints that may be useful for this part of the Exam, based on the Mock exam. Make functions work with dynamic arrays Most problems in the mock exam asked you to create functions that work with dynamic arrays. You may have to create your own dynamic array in the name manager to test it. Observe that functions such as SUMIFS do not work with dynamic arrays. That is why I used the -- trick together with SUM multiple times in the solutions proposal for the mock exam. CourseNana.COM

Handle errors properly Make sure to handle errors properly , refer to the lecture notes on creating errors if necessary . You will not get points if you return the string “#V ALUE!” instead of a proper value error. CourseNana.COM

Only tests are evaluated A student told me that REPEATTEXT (from the mock exam) is already implemented in Excel as REPT , and asked if he was allowed to use this function in the exam. The answer is Y es ! Y ou are only evaluated how many tests you pass. Y our code, your comments, and documentation does not matter for this part of the exam. You can’t return empty cells Excel functions can’t return empty cells. Hence an array function that, e.g, converts numbers to their negative counterpart and keeps all other values will return 0 instead of an empty cell when presented with an empty cell. This is correct behaviour . CourseNana.COM