Module 4E

Introduction to Data Manipulation and Calculations

  • The upcoming module will focus on calculations related to sports science and data analysis, specifically within an athlete's performance database.

  • Key areas of focus:

    • Rolling sums

    • Weekly sums

    • Acute load

    • Chronic load

    • Monotony

    • Strain

Data Preparation and Structure

  • A database has been provided for manipulation, with additional data uploaded on Canvas.

  • Necessary modifications to the database:

    • Creation of additional data columns to facilitate analysis.

    • Key columns to add:

    • Load (calculated from the session Rate of Perceived Exertion (RPE))

    • Rolling averages (3-day, 7-day, and 28-day)

    • Seven-day standard deviation

    • Seven-day average load

    • Monotony

    • Strain

Calculation Process

  • Training Load Calculation:

    • Formula: Load = RPE × Duration

    • Importance of operationalizing data for the intended analysis.

Rolling Averages

  • Calculating Rolling Averages using Excel functions:

    • Use of SUMIFS function for calculating the rolling averages:

    • Example: Rolling 3-day average

      • Formula structure: =SUMIFS(load_range, athlete_range, athlete_condition, date_range, criteria)

      • Criteria for comparison involves specific athletes and date constraints.

    • Calculation steps:

      • Input athlete's data with respective conditions using the ampersand for row references.

      • Ensure that the date checked is both less than or equal to and greater than the specified date minus 3 days.

      • Expected value example: 900 for the initial rolling average.

    • Replicate the formula for 7-day and 28-day averages by modifying the date constraints in the SUMIFS function.

Seven-Day Standard Deviation

  • Steps to calculate the seven-day standard deviation using STDEV.S function:

    • Structure: =STDEV.S(IF(...)) - involves nested IF functions to apply conditions based on athlete and date.

    • Correct usage of operators is crucial to avoid errors (e.g., using > instead of a period).

    • Implementation involves double-checking for typographical errors (common practice in teaching for error checking).

Seven-Day Average Load

  • Calculation using AVERAGEIFS function:

    • Formula structure follows similar logic to SUMIFS:

    • Input load range followed by athlete and date constraints similar to previous calculations.

Counting Valid Loads

  • Implementing COUNTIFS function for athletes:

    • Objective is to count instances where load is greater than zero, with appropriate conditions for athletes and dates.

  • Importance of correcting function types (COUNTIF vs COUNTIFS) for accurate data counting.

Monotony and Strain Calculations

  • Monotony Calculation:

    • Formula structure: Monotony = (7-day average load) / (7-day standard deviation)

    • Use of IFERROR function to manage potential errors in division (e.g., ensuring to return a blank if there's an error).

    • Adjustments as necessary to ensure the formula is correctly applied without typographical errors.

  • Strain Calculation:

    • Similar structure to Monotony with adjusted parameters based on the specific formula required for strain assessment.

    • Continuous error checking is advised during formula applications to ensure accuracy.

Conclusion and Application

  • The calculations established enable further application in monitoring athlete performance and load management.

  • Reinforces the importance of data accuracy and systematic checking while performing calculations in sports science contexts.

  • Final notes highlight the need for continual learning from errors to enhance understanding of data manipulation processes.