**User's Guide: Spreadsheet Application**

**for Calculating Disproportionality Measures**

**[Introduction]{.underline}**

The 1997 reauthorization of the Individuals with Disabilities Education
Act (IDEA) requires states to address significant disproportionality
where it occurs. States are instructed to collect and examine data to
assess whether any racial/ethnic groups are disproportionately
represented in special education disability and educational environment
categories.

Westat created this Excel spreadsheet application as a tool to aid
states in their assessment of racial/ethnic disproportionality. The
application calculates several disproportionality measures, including
composition, relative difference in composition, risk, the risk ratio,
and a weighted risk ratio.[^1] It can calculate these measures for the
state overall and each district within the state.

This user's guide is divided into four parts:

-   Part I. Overview of the Spreadsheet Application,

-   Part II. Data Requirements for the Spreadsheet Application,

-   Part III. Instructions for Using the Spreadsheet Application, and

-   Part IV. Educational Environment Data: Additional Instructions.

**[Part I. Overview of the Spreadsheet Application]{.underline}**

The spreadsheet application is composed of seven sheets: (1) Main Menu,
(2) IDEA Data, (3) Comparison Data, (4) Composition, (5) Risk, (6) Work,
and (7) Help. Some of these sheets require user input while others
display the results of the analyses. Below is an overview of each sheet.
More detailed information about these sheets and how to use the
application is provided in the sections that follow.

***Input sheets:***

1.  [Main Menu]{.underline}

-   Use this sheet to:

    -   Describe your data by choosing and entering labels.

    ```{=html}
    <!-- -->
    ```
    -   Tell the application whether you are including state totals as
        part of your data (see Data Requirement section below for more
        information).

    ```{=html}
    <!-- -->
    ```
    -   Run the application by clicking on the "Calculate" button.

    -   Save your file by clicking on the "Save As" button.

    -   Clear your data and/or results sheets by selecting one of the
        clear options and clicking on the "Clear" button.

2.  [IDEA Data]{.underline}

-   Paste your district-level IDEA data onto this sheet (i.e.,
    race/ethnicity data for a specific disability or educational
    environment category).

3.  [Comparison Data]{.underline}

-   Paste your district-level comparison data onto this sheet (i.e.,
    race/ethnicity enrollment or population data).

***Results sheets:***

1.  [Composition]{.underline}

-   This sheet displays the racial/ethnic composition of the IDEA
    category, the racial/ethnic composition of the comparison group, and
    the relative difference in composition for each district and the
    state overall.

2.  [Risk]{.underline}

-   This sheet displays risk, the risk ratio, and a weighted risk ratio
    for each racial/ethnic group for each district and the state
    overall.

3.  [Work]{.underline}

-   Work1

    -   The Work1 sheet displays your sorted IDEA and comparison data
        and totals for each and identifies districts with missing data.

-   Work2

    -   When analyzing educational environment data, the application
        creates an additional sheet: Work2 (see the Educational
        Environment Data: Additional Instructions section below for more
        information about analyzing educational environment data).

    -   The Work2 sheet displays your sorted IDEA data, the data you
        previously entered for the All Disabilities category and totals
        for each and identifies districts with missing data.

4.  [Help]{.underline}

-   This sheet explains the error messages you may encounter while using
    the application.

**[Part II. Data Requirements for the Spreadsheet
Application]{.underline}**

1.  [Data format]{.underline}

-   The spreadsheet application analyzes district-level data for one
    IDEA category (e.g., mental retardation, \>60% outside the regular
    classroom, etc.) at a time.

-   Your IDEA and comparison data must be organized so that each row is
    the data for one district and each column is the data for one of the
    five race/ethnicity categories.

```{=html}
<!-- -->
```
-   Your district rows do not have to be sorted in any particular order
    (i.e., alphabetical, ascending, descending, etc.); your IDEA data
    and comparison data will be sorted and the districts matched up
    before the application conducts any analyses.

-   The five race/ethnicity category columns appear in alphabetical
    order.

2.  [District identifiers]{.underline}

-   Every district row must have a unique identifier.

-   If one or more districts do not have an identifier (i.e., the ID
    cell is blank), all districts from that row down will be excluded
    from the analyses.

-   The same unique identifier must be used to identify a district on
    both the IDEA Data and Comparison Data sheets.

3.  [Missing data]{.underline}

-   Only numerical values will be analyzed; a text value in any cell
    will result in an error message.

-   The application reads blank cells or cells with periods as missing
    data. No other values are recognized as missing data (e.g., -9, --,
    \*, etc.).

```{=html}
<!-- -->
```
-   Although the application will function if you have missing data, the
    results for any districts with missing data and the state overall
    will be invalid since calculations will be based on incomplete data.

```{=html}
<!-- -->
```
-   Cells with missing data are highlighted in red on the Work sheet(s).

-   Blank rows will result in invalid results. If there are blank rows,
    all districts after the first blank row will be excluded from the
    analyses.

4.  [Including state totals with your data]{.underline}

-   You have the option of providing state totals as part of your IDEA
    and/or comparison data.

```{=html}
<!-- -->
```
-   You must indicate on the Main Menu whether your data include state
    totals.

```{=html}
<!-- -->
```
-   **If you [choose not to provide]{.underline} state totals:**

```{=html}
<!-- -->
```
-   The application will calculate state totals by summing the district
    data; these calculated state totals will be used in the analyses.

-   If you mistakenly indicate on the Main Menu that you provided state
    totals, the last district row will be treated as your state totals.

```{=html}
<!-- -->
```
-   **If you [choose to provide]{.underline} state totals:**

```{=html}
<!-- -->
```
-   The application will use your state totals in the analyses.

```{=html}
<!-- -->
```
-   The last row of data [must]{.underline} be your state totals.

    -   If your state totals appear elsewhere in your data, they will be
        treated as district data, and your last row of district data
        will be treated as your state totals.

    -   If you mistakenly indicate on the Main Menu that you did not
        provide state totals, your state totals will be treated as
        district data.

    -   [Do not]{.underline} include any blank rows before your state
        totals row.

```{=html}
<!-- -->
```
-   Your state totals row [must]{.underline} have a unique identifier.

    -   If this row does not have an identifier (i.e., the ID cell is
        left blank), the application will treat the state totals row as
        a blank row; it will use the last row with an identifier as your
        state totals.

    ```{=html}
    <!-- -->
    ```
    -   The application will compare your state totals to the totals
        calculated by the summing the district-level data. Discrepancies
        between the totals will result in an error message and will be
        highlighted in yellow on the Work sheet(s).

**[Part III. Instructions for Using the Spreadsheet
Application]{.underline}**

***Opening the spreadsheet:***

1.  Open the spreadsheet document in Excel.

2.  A popup message will appear warning you that the application
    contains macros. You must enable the macros for the application to
    function.

3.  Click on the "Enable Macros" button to continue.

***Input sheets:***

1.  The spreadsheet should open to the Main Menu page. If it does not,
    click on the Main Menu tab.

> ![](media/image1.png){width="6.217361111111111in"
> height="4.439583333333333in"}

2.  Use the Main Menu to describe your data:

-   You must select an IDEA data type, an IDEA data category, and a
    comparison data type; not completing these fields will result in
    error messages. You must also indicate whether you are providing
    state totals. The remaining fields are optional and will be used by
    the application to describe your data on the results sheets.

```{=html}
<!-- -->
```
-   Under the heading "Students with Disabilities":

    -   Select the type of IDEA data you plan to analyze (i.e., child
        count or environment).

    -   Select the IDEA data category you plan to analyze (e.g., mental
        retardation, more than 60%, etc.).

        -   If you select "Other" for either the IDEA data type or the
            IDEA data category, you should describe the data in the "If
            Other, describe" cell.

    -   Describe the age range of your data (e.g., 6-though
        21-year-olds).

    -   Describe the collection year of your data (e.g., 2004).

    ```{=html}
    <!-- -->
    ```
    -   Indicate whether your IDEA data include state totals.

```{=html}
<!-- -->
```
-   Under the heading "Comparison Group":

    -   Select the type of comparison data you will be providing (i.e.,
        enrollment or population).[^2]

        -   If you select "Other" for your comparison data type, you
            should describe the data in the "If Other, describe" cell.

    ```{=html}
    <!-- -->
    ```
    -   Describe the age/grade range of your data (e.g.,
        pre-kindergarten through 12^th^ grade).

    -   Describe the collection year your data (e.g., 2004).

    ```{=html}
    <!-- -->
    ```
    -   Indicate whether your comparison data include state totals.

3.  Click on the IDEA Data sheet tab.

-   Paste either your district-level child count or educational
    environment data for the IDEA data category into the cells. Make
    sure these data match the data type you selected on the Main Menu.

4.  Click on the Comparison Data sheet tab.

-   Paste either your district-level enrollment or population data into
    the cells. Make sure these data match the data type you selected on
    the Main Menu.

5.  Return to the Main Menu by clicking on the tab.

-   Click on the "Calculate" button to run the application.

***Results sheets:***

1.  Go to the Work sheet(s) by clicking on the tab:

-   Examine the sheet(s) for missing data (indicated in red).

-   If you provided state totals, verify that your reported totals match
    the calculated totals (discrepancies are highlighted in yellow).

-   If you find any problems, go to the IDEA Data and/or Comparison Data
    sheets, revise your data, and then re-run the analyses by clicking
    on the "Calculate" button on the Main Menu sheet. Repeat these steps
    until you no longer find problems.

2.  Go to the Composition and Risk sheets by clicking on the tabs

-   The number of districts included in the analyses is listed at the
    top of these sheets as part of the title. Verify that the number of
    districts analyzed matches the number of districts you planned to
    analyze. If the numbers do not match, revise your data and re-run
    the application.

-   These sheets include the results for each district and for the state
    overall. The results for the state will be located after the results
    for all of your districts; state results are labeled either "All
    (using summed totals)" or "All (using reported totals)" depending on
    whether you included state totals as part of your data.

***Saving and clearing your data*:**

1.  To save your results, return to the Main Menu and click on the "Save
    As" button. You will then be able to name your file and choose where
    you want it to be saved.

-   When saving your results, you cannot name your file
    "OSEP_disproportionality_template.xls" because this file name refers
    to the spreadsheet application template. You must choose a different
    file name to save your results.

2.  To clear either your data and/or result sheets, go to the Main Menu,
    select one of the clear options, and click on the "Clear" button.
    The clear options are as follows:

-   "Clear Main Menu" -- clears all of the data labels and resets the
    Main Menu options.

-   "Clear IDEA Data" -- deletes data on the IDEA Data sheet and deletes
    the results from the Composition, Risk, and Work sheets.

-   "Clear Results" -- deletes the results from the Composition, Risk,
    and Work sheets.

-   "Clear All Data & Results" -- deletes the data/results from all
    sheets (IDEA Data, Comparison Data, Composition, Risk, and Work
    sheets).

**[Part IV. Educational Environment Data: Additional
Instructions]{.underline}**

You must analyze your total child count data before analyzing your
educational environment data.[^3]

-   To analyze your total child count data:

1.  On the Main Menu sheet, select "Child Count" as your IDEA data type.

2.  Select "All Disabilities" as your IDEA data category.

3.  Select the type of comparison data you will be providing (i.e.,
    enrollment or population).

4.  Paste your All Disabilities data onto the IDEA Data sheet and your
    comparison data onto the Comparison Data sheet.

5.  Return to the Main Menu and click on the "Calculate" button.

-   After analyzing your All Disabilities data, you can analyze your
    educational environment data using the instructions from Part III
    (summarized briefly below).

1.  On the Main Menu sheet, select "Environment" as your IDEA data type.

2.  For the IDEA data category, select the educational environment
    category you plan to analyze.

3.  Select the type of comparison data you will be providing (i.e.,
    enrollment or population).

-   You do not have to provide your total child count data again. When
    you analyzed the All Disabilities category, the application stored
    these data and will use them in the educational environment
    analyses.

4.  Paste your educational environment data onto the IDEA Data sheet.

5.  Paste your comparison data (i.e., enrollment or population) onto the
    Comparison Data sheet.

6.  Return to the Main Menu and click on the "Calculate" button.

-   Once you have analyzed your All Disabilities data, they remain
    available for use by the application even if you analyze other IDEA
    data categories.

    -   When you analyze an educational environment category, the
        application uses the data from your most recent analysis of the
        All Disabilities category.

```{=html}
<!-- -->
```
-   After analyzing an educational environment category, make sure to
    review your All Disabilities data on the Work2 sheet.

    -   Verify that your All Disabilities data are consistent with your
        educational environment data with regard to the data collection
        year and age range.

    -   If the data are not consistent, re-analyze the All Disabilities
        category using the appropriate data before continuing with your
        educational environment analyses.

    ```{=html}
    <!-- -->
    ```
    -   **Selecting the last clear option "Clear All Data & Results"
        will delete your All Disabilities data.**

```{=html}
<!-- -->
```
-   You will have to re-analyze your All Disabilities data before
    analyzing your educational environment data.

-   If you select any of the other clear options, your All Disabilities
    data will remain available.

**[Questions or Problems?]{.underline}**

This spreadsheet application assumes a basic knowledge of Excel. If you
have questions about the disproportionality spreadsheet application that
are not specific to the use of Excel, please email them to
<ideadata@westat.com>.

[^1]: Westat's technical assistance document, *Methods for Assessing
    Racial/Ethnic Disproportionality in Special Education: A Technical
    Assistance Guide,* provides more information about these measures,
    including step-by-step instructions on how to calculate them,
    discussions of how to interpret each, and a summary of their
    strengths and weaknesses. This document is available at
    <https://www.ideadata.org/index.html>.

[^2]: An OSEP/Westat disproportionality task force recommended that
    states use enrollment data when calculating disproportionality
    because these data are available at the state and district levels.
    Population data may not be readily available at the district level.
    States should use the same comparison data for all levels of
    analysis.

[^3]: When analyzing educational environment data, the application uses
    your comparison data (i.e., enrollment or population) to calculate
    the relative difference in composition (i.e., the racial/ethnic
    composition of the educational environment category is compared to
    the racial/ethnic composition of the comparison data). The
    application uses your total child count data to calculate risk and
    the risk ratio (both unweighted and weighted). For more information
    about how these measures are calculated, see Westat's technical
    assistance document, *Methods for Assessing Racial/Ethnic
    Disproportionality in Special Education: A Technical Assistance
    Guide*, available at <https://www.ideadata.org/index.html>.