How to practice basic spreadsheet skills
Exercise: Loan of Books
- Create the table with the required data for 20 students. Use recent dates (January 2019 onwards) to complete the Original Return Date column.
- The format for the Days of Delay column is “General”.
Tabla 1 Informe Cargo por Demora
- The “header”. It should be:
EDP University of Puerto Rico, Inc.
Information Resource Center
Book Loan Charge Report
- Use the correct function to get today’s date.
- Write down the penalty perday; en: $0.20
- Using the correct formula, determine the days of Delay and Late Fees.
- Prepare a chart depicting student days of delay.
- Rename the “sheet” to Loan.
II Exercise: Payroll
- Prepare a payroll for your company that contains 10 employees. Fill in the missing information.
Tabla 2 Nómina
- Use the right formulas to get Subtotal, Income Tax, Social Security, Net Pay and totals.
- Use the function to concadenate the full name.
- Name your company and division. Determine month and year of payroll.
- Prepare a graph representing employees’ hourly wages.
- Rename the “sheet” to Payroll.
III Exercise: Registration of notes
- Create the table with the required information for 10 students. To do this, you will note (from 0 to 100) the partial marks you have taken in the columns under the columns of Exam 1, Exam 2, Exam 3 and Exam 4.
- Create a dividing column between Exam 4 and the Average. Fill the column with some color, as shown in the image above.
|ID||Apellidos||Nombre||Nombre Completo||Examen 1||Examen 2||Examen 3||Examen 4||Promedio||Nota|
Tabla 3 Registro de Notas
- In the header, determine the name of the course
EDP University of Puerto Rico, Inc.
School of Technology
Note Log ___________
- Use the correct formula to get the average and grade. According to the average obtained, the student will obtain a grade or grade according to the following scale:
|De 90 a 100||A|
|De 80 a 89||B|
|De 70 a 79||C|
|De 60 a 69||D|
|From 90 to 100||A|
|From 80 to 89||B|
|From 70 to 79||C|
|From 60 to 69||D|
|Less than 60||F|
It will be necessary to calculate the average of the grades and determine the corresponding grade according to the table using the concept of decisions.
- To get the average, use the formula =AVERAGE (cell:cell), for example: =AVERAGE(E6:H6) To get the note, the IF statement will be used. An example using numbers: let’s say that if the number that is in a cell, let’s say A1 is greater than 100, then cell A2 will say It is greater than 100. Otherwise, that is, if it is A1 is 100 or less then it will say “It is less than 100”. To program this function, you will go to the place where you want the text to come out and write the schedule.
=IF(A1>=100,”Is greater than or equal to 100″,”Is less than 100″)
To calculate the grade you have to create the formula that indicates the grade you will get according to the score obtained in AVERAGE. Using the above, and assuming that the average is in H6, the programming of I6 would be as follows:
=IF(H6>=90,”A”, IF(H6>=80,”B”,IF(H6>=70,”C”, IF(H6>=60,”D”,”F”))))
Or, you can use the following formula:
La tabla quedará así:
- Prepare a table with the distribution of notes (A, B, C, D, F). To get the number of students who obtained a particular grade you must use the formula: =countif (range of cells, “Note”)
|Distribution of Notes|
|From 90 to 100 – A||2|
|From 80 to 89 – B||8|
|From 70 to 79 – C||4|
|From 60 to 69 – D||1|
|Less than 60 – F||0|
For example, to obtain how many students obtained A, the formula to be written will be: =countif(I6:I20,”A”)
- Prepare a table for the registry overviews.
- Using the correct formula, determine the number of students. For example: =COUNTA(D2:D5)
- Using the correct formulas, determine the highest average and the lowest average in the note log.
- Creates a graph to represent the distribution of notes. It must have a title, legend and the “labels”.
- Rename the sheet to Note Record.
Your exercise should look like the one presented below.