RIZAL TECHNOLOGICAL UNIVERSITY
Cities of Mandaluyong and Pasig
Spread Sheet Activity
1. Assume the following sample data in an Excel spreadsheet: (Activity 1)
First Name Last Name Middle Name Jan Feb Mar Apr May
Erick Padayao C P A P P P
Jane Balisi A P P P A P
Bob Mijares F P P P P P
Fred Sison C A A P P P
Raver Reyes R P P P A A
Chester Ling P A A A A A
Robert Solis S A A P P A
2. In column J2:J8, create a concatenated name of each employee. This will create an
outcome like “ Padayao, Erick C.”
3. In Column K2: K8, Calculate the total number of days each employee was present using
COUNTIF function for each employee.
4. Calculate the overall attendance percentage for each employee. In this case, "P" stands
for "Present," and "A" stands for "Absent."
• In cell I2, enter the formula: =COUNTIF(B2:F2, "P") / COUNTA(B2:F2), which
calculates the percentage of "P" values.
• Format the result as a percentage using the format settings
5. Add conditional formatting to highlight percentages less than 60 percent.
2. Create another sheet (Activity 2)
• Create a table with the following columns and data
A B C
1 Sales Person Item Sold Quantity
2 Erick Masterlise Naruto 10
3 Jane Ichiban Kuji Itadori 5
4 Robert Nendroid Saske 20
5 Raver TreasureCruise Luffy 2
6 Raver Vibration Star Gon 15
7 Jane Popup Parade Kenshin 3
8 Robert Ichiban Kuji Deku 7
1. In Column E1:E5, write the name of each salesperson.
2. In column F1:F5, calculate the total sales for each salesperson (E column) using the
SUMIF function and absolute reference. Example
“=SUMIF($A$2:$A$8,A2,$C$2:$C$8)”
3. Create another worksheet and named it (Activity 3)
1. In cell A1, enter "Sales Report."
2. In cell A3, enter "Name."
3. In cell B3, enter "Sales Amount."
4. Enter the following data in cells A4:A10 (names of your team members)
•A4: John
•A5: Sarah
•A6: Michael
•A7: Lisa
•A8: David
•A9: Emily
•A10: Kevin
5. Set up data validation to ensure that team members cannot enter sales amounts less
than 5000.
o Select cells B4:B10
o Go to the "Data" tab and choose "Data Validation."
o Under the "Settings" tab, select "Custom" from the "Allow" dropdown.
o In the "Formula" field, enter the following formula to allow values greater
than or equal to 5000: =b4>5000
o Click “Ok”
6. Enter some sales amounts for each team member in cells B4:B10. Use Philippine
currency format.
7. In cell A12, type "Total Sales."
8. In cell B12, use the SUM function to calculate the total sales of all salespersons. Use
Philippine currency format
9. In cell A13, type "Average Sales."
10. In cell B13, use the AVERAGE function to calculate the average sales. Use two
decimals only.
11. In cell A14, type "Highest Sales."
12. In cell A15, type "Team Member:"
13. In cell B14, use the MAX function to identify the highest sales amount
14. In cell B15, enter the following formula to display the team member's name with the
highest sales: =INDEX(A4:A10, MATCH(B16, B4:B10, 0))
15. In cell A16, type "Sales above Php 9,000:"
16. In cell B16, use COUNTIF formula to count how many team members achieved sales
above $9000:
17. In cell A17, type "Total Sales above $9000:"
18. In cell B17, use the SUMIF function to calculate the total sales above $9000. Use
Philippine Currency
4. Create another worksheet and named it (Activity 2.1)
1. Enter the following data
2. In a separate worksheet, enter the following category.
3. Add data validation (drop-down list) for the “assignment type” column using the data
in sheet no. 5
4. Randomly assign assignment type to each student.
5. In cell E1, enter "Assignment Type."
6. In cell E2, enter "Homework”, E3, enter “Quiz”, E4, enter “Project” to specify the
assignment type you want to calculate the average for.
7. In cell F1, enter "Average Grade."
8. In cell F2, use the following formula to calculate the average grade for Homework
assignments: =SUMIF(B2:B15, D2, C2:C15) / COUNTIF(B2:B15, D2)
9. Copy the formula in cell F2 for calculating the average grade of quiz and project, but
use both relative and absolute references to apply it to different assignment types”