Practice Problem 2

Objective

The objective of this practice problem is for the user to demonstrate an understanding of the If Statement commands discussed in the previous section by creating an automation that utilizes said commands. The commands covered in the previous section were the Begin Multi If, End If, and Else commands. In the future, these commands may be implemented into other practice problems.

Scenario

There was a student at the ABC school district that was missed by the automated grading system and needs to have a final letter grade calculated based on the score table and the overall score. The student’s Honors status also needs to be determined based on their overall score and if they finished the class (95 or above). Once the letter grade has been calculated, the Excel sheet is emailed to the principal for final review.

Prerequisites

The only prerequisite for this practice problem is an Excel file that is formatted in a manner as seen below. The Name, Subject, and Class Average can be set to whatever the user wants, but the criteria in the Score Table must remain the same. The Finished Class field has to be set to either Yes or No. Be sure to place the file in the Project folder next to Main .JSON

Practice Problem Requirements

The user must create an automation that performs the following tasks:

Opens the prerequisite file
Extracts the Finished Class and Overall Score values
Uses multiple Begin Multi If commands to calculate the Grade value using the Score Table.
Writes the letter grade into the Grade field.
Writes Yes in the Graduate w/ Honors field based on if the Overall Score is 95 or over and the Finished Class value is Yes. Otherwise, for all other Grades, the Graduate w/ Honors field should be set to No.
Saves the file and emails it to an address determined by the user.

Note: The user should be able to edit the Overall Score and Finished Class values and still have the automation calculate the necessary values.
Problem Difficulty: Easy

Solution

The code for the solution has been provided below.

As a quick summary:

Step 1: Open the prerequisite file and extract the Overall Score and Finished Class values using the Create Excel Application and Get Cells command.

Step 2: Use Begin Multi If commands to compare the values in the Score Table to the Overall Score.

Step 3: Within each of the Begin Multi If, write the appropriate Grade and Graduate w/ Honors values to the Excel sheet using the Write Cell command.

Step 4: Within the final Begin Multi If command, write the appropriate Grade value and check to see if the Finished Class value is Yes or No. If it equals Yes, write Yes in the Graduate w/ Honors field. If it equals No, write No.

Step 5: Close the file and email it to another user.

Lets stay in touch!