| An Example Answer for the Practical Exercise (Practice 29) in the Previous Section |
Review various features of spreadsheets through an example answer of the practical exercise (Practice 29) in the previous section.
Open the file that you have created in the practical exercise (Practice 29) in the previous section in StarSuite Calc, save it as ex4.sxc. Complete the sheet "Attendance Test" and the sheet "Evaluation" referring to the following example answers, and overwrite save on the file ex4.sxc.
Example Answer (The First Half: Sheet "Attendance Test")
Count the number of all the lectures using the function "Count" first,
Input a formula in the cell in which the threshold, two-thirds of all lectures, is to be displayed.
In the field of number of attendances, count the number of "1" using the function "Countif".
Determine pass or fail by comparing the two-thirds attendance at all the lectures (L12) and the actual number of attendances. Cell L12, which contains two-thirds of the lectures needs to be referenced with an absolute reference ($L$12). Otherwise, the cell reference goes wrong when the formula is copied using the auto-fill feature.
Write a formula to calculate the points to be added according to the definition. The total number of lectures and two-thirds of the lectures also need to be referenced with an absolute reference. Otherwise, the cell reference goes wrong when the formula is copied using the auto-fill feature.
Next, check if each test score reaches the passing point, and display "1" if it passed and "0" if it failed. The cell that contains the passing point also needs to be referenced with an absolute reference.
The numbers of times a student passed a test can be obtained by counting the number of "1"s in the cell range from Aok to Cok.
(Note) If numeric values, 1 and 0, are written in the cells from Aok to Cok instead of text numbers "1" and "0" as in the following illustration, the number of passes can be obtained using the function "sum".
Pass or fail can be determined in the same way as attendance.
Since the perfect score for each test is 100, the points to be added can be calculated by referencing cell T11 that contains the passing point with an absolute reference.
(Note) If the number of tests is calculated with the function "count" as in the following illustration, the versatility of the usage will be expanded.
Example Answer (The Latter Half: Sheet "Evaluation")
With regard to the "Numbers" filed, reference the numbers in the sheet "Attendance Test" as they are. First, input "=" which is the symbol for a calculation formula, and display the sheet "Attendance Test" by clicking on the tab of the sheet.
Click the cell A5, the corresponding cell in the sheet "Attendance Test" and press "Enter" key, next. Then, numbers are displayed in the sheet "Evaluation".
For confirmation, check the contents of the cell B4. You will see that it references the cell A5 of the sheet "Attendance Test" as the following illustration.
Likewise, in the name field, input "=" first, click cell B5 of the sheet "Attendance Test", input the conjunction operator "&", and input the space character " ". Then, input the conjunction operator "&" again, click cell C5 of the sheet "Attendance Test", and press the Enter key. Now, the Last name and First name with a blank space between them is displayed.
Finally, use the function "and" to express that the number of attendances of a student reaches the passing level and the test score also reaches the passing score, and make a formula using the function "if" whose condition is the above function "and" to display his or her learning results, which is expressed with the score calculated using the formula (points added on attendance + points added on the test score + 60) or "failure".
