Exercise 4 - Spreadsheet functions

You will improve the product-table which was made in previous time in this exercise meeting. Open the product-table made by you or fetch product-table from address <URL: http://appro.mit.jyu.fi/ohjelmistot/demot/hintataulukko.xls>. At first time you can save the workbook with new name demo4.xls. Remember to save your workbook often enough!

1. You added three named sections (total, discount and flat) into product-table in the end of previous exercise. Then you count total sum of D-, E- and F-columns by using named sections.

   Use the named sections and count information of E- and F-columns. You can also use named sections as relatively and get the formula from cell F4 into form total-discount. You count the difference between cells in the same row inside the named section.

   
2. Let´s get to improve the determination of discount per cent a little. A shopkeeper wants to give discount for total price accumulations to his customers. Produce following formulas which define the discount per cent into cell E1 and use IF(suom. JOS)-function. You can insert the function by selecting Insert | Function (finn. Lisää | Funktio).
   • If the total price sum of products is bigger than or equal to 1000 € there is given 10 per cent discount. Otherwise the discount is 0 per cent.
   • Next there is taken a whole new discount per cent limit within the previous.
     • If the total price sum of products is bigger than or equal to 2000 € there is given 15 per cent discount.
     • If the total price sum of products is bigger than or equal to 1000 € but it is smaller than 2000 € there is given 10 per cent discount.
     • Otherwise the discount is 0 per cent.
   • Next there is taken a new discount per cent limit within the previous again.
     • If the total price sum of products is bigger than or equal to 3000 € there is given 20 per cent discount.
     • If the total price sum of products is bigger than or equal to 2000 € but it is smaller than 3000 € there is given 15 per cent discount.
     • If the total price sum of products is bigger than or equal to 1000 € but it is smaller than 2000 € there is given 10 per cent discount.
     • Otherwise the discount is 0 per cent.
   • The previous formula was got complex and long enough. Try to find out what kind of function you would get if you insert following conditions into it. You don´t have to produce the formula.
     • Let´s insert new discount per cent limits.
     • There is also inserted discount per cent limits for the number of products.
3. It is quite difficult to execute nested IF-functions especially if the number of discount per cents increase. On next the formation of discount per cents is executed a little simple way.
   • Insert a new blank-sheet into your workbook and make a discount per cent table similar than showed in the picture into it.
   • Name the blank-sheet as discount.
   • Name also the cell section A2:B7 as discount-table.
   • The meaning is to execute the determination of discount per cent so that discount per cent is fetch from discount-table. The fetching can be done by using VLOOKUP (finn. PHAKU)-function. Plan and execute a formula which will fetch the discount per cent into cell E1.
   • Check the functionality of the formula by changing the number of products.
   
4. Increase total price of products over 6000 € by changing the number of products. Insert a new row into discount-table so that .it is inside the named section. Create a new per cent limit which defines that if the total price sum of products is bigger that or equal to 6000 € there is given 35 per cent discount. Try out the functionality of the new discount limit very carefully.
5. Insert a new product into the product-table between shoes and trousers.
   • You can insert a product by inserting a row into table. After that you can write name and price for the product.
   • Copy formulas which count total price of product, discount and knockdown price into added row from corresponding columns of trousers.
   • Will the formulas work just like that? Try out the functionality of formulas by changing the number of new products. How total information changed? Will the total information-formulas work still right?
6. Insert TODAY (finn. TÄMÄ.PÄIVÄ)-function which announces the date into cell B1.
7. Next we look back cell references a little with the multiplication table from following picture. The multiplication table have to be like that, that you can emerge any 5*5 multiplication table by using the table.
   • Insert a one new blank-sheet for multiplication table into workbook.
   • Create heading row(1) and column(A) of the multiplication table so that other values of row and column are count by using values of cells B1 and A2.
   • Create the primary multiplication table formula into B2 so that it can be copied into other multiplication table cells.
   • Try out the functionality of the multiplication table by changing values of cells B1 and A2.
   
   
   
8. Name the cells of first row as kerroin1 in multiplication table. Name the cells of A-column as kerroin2 in multiplication table. Modify the multiplication table formula so that it uses those sections.
9. Next we start to create a new multiplication table application which is meant for counting grades of the students.
   • Insert two new blank-sheets into your workbook. Name other blank-sheet as Tentti1 and the other one as Arvosanat.
   • Insert student names and the points of exam exercises into B-, C-, D-, E-, F- ja G- columns in Tentti1-blank-sheet. The values of H, I and J columns are count a little later.

     

   • Create a grade-table as showed in following picture into Grades-blank-sheet. In left column there is point limit which is corresponded grade as in numerical form in B-column. Grade on text-form can be found from C-column.

     

   • Edit the inserted blank-sheets like showed in following pictures by using styles.
10. At next we insert a formula which counts student´s grade.
    • Insert a formula into H-column which can be used for counting the total sum of exam exercise points. You must use SUM (finn. SUMMA)- function for counting points. Create the formula so that it can be copied into other cells in same column.
      
    • Insert a formula into I-column which can be used for defining the grade on numerical form which corresponds the score to the student.
      • The grade which corresponds score in meant to fetch from Grades-blank-sheet. So you should define a named section into the Grades-blank-sheet.
      • After that fetching grades into I-column can be done by using VLOOKUP (finn. PHAKU)-function. Create the formula so that it can be copied into other cells in same column.
    • Insert a formula into J-column which can be used for defining the grade on text form which corresponds the score to the student.
11. Let´s improve the student grade-table a little more.

    

    • Insert a new column behind the exam points. There will come student´s bonus points which are given from exercise-work into that new column.
    • Edit the formula which counts total score so that bonus points are count into total score only if student´s score without bonus points is 12 at least. The student´s exam result have to be accepted without bonus points. You must use SUM (finn. SUMMA)-function alike IF (finn. JOS)-funktion for counting.
    • Insert two new columns into student-table in which are marked the date of accepted from exercise work both parts.
    • Change the formula which defines student´s grade so that accepted grade is given only when student´s both exercise works are done. You must use OR (finn. TAI)-, VLOOKUP (finn. PHAKU)-, ISBLANK (finn. ONTYHJÄ) and IF (fin. JOS)-functions. Next there is a tip for how to think order of the functions.

      TIP:  IF(OR(ISBLANK();ISBLANK()); ;VLOOKUP())
      
      

      The formula can be created also by using other functions!
    • It would also be interesting to get information about exam results from the student-table. Insert count of average-, minimum- and maximum value like showed in picture into the student-table. You need MIN (finn. MIN)-, MAX (finn. MAKS)-, AVERAGE (finn. KESKIARVO)-functions for counting.
    • How you get average, minimum and maximum from text grades?

Extra exercises

1. On attached picture there is compiled statistics of February weather information of Jyväskylä. Create a analysis blank-sheet of weather information like on attached picture. You can create the weather information by yourself.
   • The information are put into four different columns and you can see dates, temperatures, cloudiness and weather situation from those.
   • There is count some statistical information of weather situation to the right side of table. There is used COUNTIF (finn. LASKE.JOS)-, SUMIF (finn. SUMMA.JOS)-, AVERAGE (finn. KESKIARVO)-, MAX (finn. MAKS)- and MIN (finn. MIN)-functions for counting.
   • What problem there will be if you announce for example the average temperatures of cloudiness days if there is no cloudiness days? How you can solve the problem?

   

2. Create corresponding table for analysing the weather information of Tampere.
3. Create also one blank-sheet on which you can count summary from information of Jyväskylä and Tampere. You should use "three-dimensional" counting for creating the summary. That way information are count directly through the counting table. How you should locate the fields of collage-table!