SOLUTION
CASE STUDY SCENARIO
You are managing data for a training program where participants register online, and you track attendance,
performance, and feedback. Your tasks involve managing data entry, validating inputs, analyzing data, and
creating reports.
1. Data Collection Setup:
o Create a Google Form with fields: Name, Email, Age, Gender, and Training Session.
o Responses are automatically recorded in a linked Google Sheet.
o Export the Google Sheet as an Excel file using File > Download > Microsoft Excel
(.xlsx).
o In Excel, link or import the file for further processing and analysis.
2. Excel Form Creation:
o Create a user-friendly data entry sheet with fields for Name, Attendance, and Scores.
o Use formulas to automatically populate another sheet (e.g., "Database") with the entered
data.
o Set up data validation for dropdowns and restrict invalid entries.
3. Dropdown List Validation:
o Go to Data > Data Validation.
o Choose "List" and enter options (Session A, Session B, Session C).
4. Age Validation:
o Select the Age column.
o Go to Data > Data Validation.
o Set criteria: "Whole number between 18 and 60".
5. VLOOKUP for Participant Details:
o Formula: =VLOOKUP("Participant Name", A2:E100, 3, FALSE)
Fetches the email of a participant by name.
o Errors: If the lookup value isn’t found or the data isn’t sorted, errors like #N/A may
occur.
6. HLOOKUP for Attendance:
o Formula: =HLOOKUP("Date", B1:Z10, 5, FALSE)
Finds attendance status for a given date in a row-wise data layout.
7. LOOKUP for Last Score:
o Formula: =LOOKUP(2,1/(A2:A100="Participant Name"), B2:B100)
Retrieves the last score recorded for a participant.
8. Highlight Attendance Issues:
o Select the data range.
o Use Conditional Formatting > New Rule > Use a Formula.
o Formula: =AND(A2="Absent", A3="Absent", A4="Absent").
9. Top Performers:
o Use Conditional Formatting > Highlight Cells Rules.
o Formula: =AVERAGE(B2:D2)>85.
10. COUNT Function:
o Formula: =COUNT(A2:A100)
Counts the number of sessions recorded.
11. COUNTIF for Absent Days:
o Formula: =COUNTIF(B2:B100, "Absent")
Counts the absent days for a participant.
12. SUM of Scores:
o Formula: =SUM(B2:B100)
13. SUMIFS for Session Scores:
o Formula: =SUMIFS(B2:B100, C2:C100, "Session A", D2:D100, "<30")
14. AVERAGE of Scores:
o Formula: =AVERAGE(B2:B100)
o Conditional Formula: =AVERAGEIF(B2:B100, ">80").
15. AVERAGEIFS for Gender Analysis:
o Formula: =AVERAGEIFS(B2:B100, C2:C100, "Male", D2:D100, "Session B").
16. CONCATENATE for Reports:
o Formula: =CONCATENATE(A2, " - ", B2) or =A2 & " - " & B2.
17. Text to Columns:
o Select the column with names.
o Use Data > Text to Columns.
o Choose "Delimited" and specify delimiters (e.g., space or comma).
18. IFERROR for Missing Data:
o Formula: =IFERROR(AVERAGE(B2:B100), "No Scores").
19. Eligibility with IF:
o Formula: =IF(AND(A2>75, B2>80%), "Eligible", "Not Eligible").
20. IFS for Feedback Categorization:
o Formula: =IFS( A2=1, "Poor", A2=2, "Average", A2=3, "Good", A2=4, "Very Good",
A2=5, "Excellent")
