Exercise 5 - Making charts and using group mode

In following exercises you continue developing the student table which was created by you in previous exercises.

1. Open the student table made by you or fetch it from address <URL: http://appro.mit.jyu.fi/ohjelmistot/demot/opiskelijataulukko.xls>. Save the workbook as Demo5.xls.
2. Insert three new spreadsheets into workbook and name them as Course2, Course3 and Course4. Rename the blank-sheet Tentti1 created in previous exercise as Course1.
3. Next we edit the new blank-sheets to be as similar as they were on the student exam-table which was made in previous exercises. Next there is a brief help for how to edit blank-sheets quickly:
   • Copy the whole student-table into clipboard.
   • Select the new blank-sheets as active by using mouse and CTRL- or SHIFT-keybar. Now you have the group-space in use and you can edit selected blank-sheets simultaneously.
   • Paste the student-table from clipboard into some of the active blank-sheets.
   • Clear the student names of exam, exercise points, bonus points and exercise-work marks in the group-space of new courses.
   • Discharge the group-space by selecting some of the external blank-sheets as active. If all blank-sheets of workbook are active you only have to select one of the active blank-sheets.
   • Why the average formula inside the new blank-sheets gives a warning message?
   • Insert some student information into every blank-sheets and check the functionality of formulas.
4. Insert also a new spreadsheet into the workbook and create a student register as in picture. The register is on the place where the common information about students is saved. It is meaning that student information is fetch from the student register into course blank-sheets.

   

5. Select all Course-blank-sheets as group-space and insert a one new column before the student information (column A) into blank-sheets. Insert ID as heading text of column and insert ID-values found from student register into column.
6. Create a formula into Course-blank-sheets into the cell which includes the name of the student. The formula should fetch the student name corresponding ID-value from the student register. You have to use VLOOKUP (suom. PHAKU)-function in formula and the formula can be made in group-space. Test the functionality of the formula very carefully.
7. Insert a one new blank-sheet into workbook. The blank-sheet should be a summary blank-sheet of course results of the students. Name the inserted blank-sheet as Summary. Make the blank-sheet to be as similar as in the picture by using following instructions.
   • The names of the students are fetch from the student table by using ID-number.
   • The course grades of students are fetch from Course-tables. (Tip: Named areas and VLOOKUP-function)
   • Student´s whole grade of subject will be determined by the average of the courses. If the student have fail average in some course the whole course grade is fail.

   

8. Create a chart from exam scores of the students in the Course1 and use following instructions:
   • Start inserting the chart by selecting Insert | Chart.
   • Select bar chart from charts and go to next prong.
   • Select only the information from name-column and exam score-column as Data Range (finn. Tietoalue). Selecting two different cell areas can be done by using CTRL-key.
   • On next prong you can define different properties to the chart. Try the functionality of the properties very carefully!
   • In last prong you choose the location of the chart. You can choose the chart As object in Course1 -blank-sheet to be the location.

   

9. Now create a little more multifunctional and more difficult chart from exam results of the students by using following instructions.
   • Select chart type as in picture.
   • Select the student names and exam grades with headings as Data range (finn. Tietoalue).
   • Select Rows (finn. luo sarja riveittäin). So you make one series from one student.
   • From interleaf Series select every student differently as series.
     • Select the student name as Name (finn. Nimi) of the series
     • Select exam grades of student as Values (finn. Sarjan arvot).
     • Select course titles as Category (X) axis label (finn. X-akselin arvot) .
   • Define the chart headings as in picture.
   • Try also other chart properties and modify the chart as similar as in the picture.

   

10. Next we test a little how the charts work.
    • Edit exam scores a little so that grades change. How the changing of the exam score effects on the charts?
    • Insert one new student into Summary blank-sheet. Because it is only meaning to test the functionality of the chart you can insert the student by hand. How the chart works? Change source data (finn. data-alue) of the chart so that the new student will come as new series.
    • Try to insert only a new course into Summary-blank-sheet. Because it is only meaning to test the functionality of the chart you can insert course information to the students by hand. How the chart works?
11. Arrange blank-sheets of student application in order that you like. Arranging can be done by moving the blank-sheet in right place with mouse.

Extra exercises

If you haven´t done all exercises from previous exercises make them first. (Before you start doing these extra exercises.)

1. Insert one new blank-sheet into the application which analysis wearther information.
   • It will be worthwhile if you insert the blank-sheet between the weather information blank-sheets of Jyväskylä and Tampere.
   • Fill needed information. Check how the filled information effects on the information of the Summary-blank-sheet.
   • If you have created the "three-dimensional" formulas right at the earlier the information of blank-sheet should effect automatically on the information of Summary-blank-sheet.
2. Study how the changing order of blank-sheets will effect on the information of Summary-blank-sheet.
3. Create a chart as in picture from weather information. Try to put every properties which are found from example picture into the chart.