Data Optimization and Formula Implementation

📅 2024-10-14 — Session: Data Optimization and Formula Implementation

🕒 17:15–20:15
🏷️ Labels: Data Optimization, Google Sheets, Excel Formulas, Database Design
📂 Project: Dev
⭐ Priority: MEDIUM

Session Goal

The session aimed to optimize data structures for educational systems and implement advanced data manipulation techniques in Google Sheets and Excel.

Key Activities

1. Redesign of Database Tables: Proposed a redesign of tables to optimize data structures in an educational system, focusing on tables for students, tutors, subjects, and Individualized Study Plans (PEI).
2. Google Sheets Simulation: Explained how to simulate the ‘stack’ function from pandas in Google Sheets using ARRAYFORMULA, FLATTEN, and FILTER.
3. PEI Data Structure Proposal: Presented a proposal to improve the PEI data structure by adopting a ‘long’ format for enhanced efficiency.
4. Google Sheets Automation: Developed methods to extract the latest date using Google Sheets formulas like FILTER, SORT, and INDEX.
5. Excel Formula Implementation: Provided Excel formulas for retrieving the latest date based on criteria, with detailed breakdowns and error handling strategies.

Achievements

• Completed a comprehensive redesign proposal for database tables in educational systems.
• Successfully implemented data manipulation techniques in Google Sheets and Excel.

Pending Tasks

• Review and test the proposed database redesign in a live environment.
• Further refine Excel and Google Sheets formulas for additional use cases.