How To Calculate Preliminary 2022 English Learner Progress to Proficiency (ELPtP) Calculations

Oct 10, 2026

Step-by-step instructions for calculating Preliminary 2022 English Learner Progress to Proficiency (ELPtP) results using Excel files from the Student Information System (SIS). The workflow covers downloading the 2021 ELPtP report, cleaning and restructuring headers, converting selected columns to numeric values, applying formulas to derive grade and timeline measures, sorting by timeline years, and replacing targeted values. It also includes integrating 2022 ACCESS Scores detail data via VLOOKUP, filtering zeros and missing IDs, and computing revised targets based on grade bands.

LanguageEnglish

CategoryExam

FormatPDF

Pages8

Reading Time20 min

AccessFree to download

Preparation and source data

Download required ELPtP and ACCESS reports

Clean the 2021 ELPtP spreadsheet

Excel formatting and header setup

Delete specified rows and columns

Rename headers and format angle

Core calculated fields

Convert selected columns to numbers

Enter formulas for grade and timeline years

Sort data by Timeline Years

Updating targets using ACCESS Scores

Add lookup table values

Use VLOOKUP to compute new target fields

Filter zeros and handle missing IDs

Revised targets by grade band

Compute Revised Target for Grades 5 and below

Continue for Grades 6 and higher

How To Calculate Preliminary 2022 English Learner Progress to Proficiency (ELPtP) Calculations. 1. Download the 2021 ELPtP report in the Student Information System (SIS) as an Excel file. (Note: A district with many schools may wish to download the ELPtP reports for all its schools and combine them into a single file before following the remainder of these instructions. ) 1 5 3 2. Delete rows 1-6, making row 7 the new header row. 3. Delete the contents of the following columns: a. Header A (Excel E) b. Header C (Excel G) c. Header E (Excel I) d. Header F (Excel J) e. Header G (Excel K) f. Header I (Excel M) g. Header K (Excel O) h. Header L (Excel Q) i. Header N (Excel R) j. Header O (Excel S) 4. Replace the letters in the header row with their descriptors: a. B = Prior Scale Score b. D = Timeline Target c. H = Partial Years d. J = Target Proficiency Grade e. M = Target Proficiency Scale Score 5. Highlight the header row and click the Text Angle button in the Alignment portion of the Home ribbon and choose the Angle Counterclockwise option. If needed, also click the Align Bottom button directly to the left of the Text Angle button. This will make the headers easier to read. 6. Highlight the contents of the remaining rows and convert them to numbers by clicking the dropdown arrow next to the small yellow warning icon to the left of the selected column(s) and choosing Convert 2 to Number. Note: Using the format cell option does NOT convert the text to a number. 7. In cell E2, enter the formula =D2+1 and copy down. Label the column 2022 Grade. 8. In cell O2, enter the formula =N2+1 and copy down. Label the column New Target Proficiency Grade. 9. In cell M2, enter the formula =O2-E2 and copy down. Label the column Timeline Years. 10. Select all data in columns A through Q, click the Sort & Filter menu in the Home ribbon, and choose the Custom Sort option. 11. Make sure the My data has headers box is checked. Then choose the Timeline Years column from the Sort by: field, leaving the option Smallest to Largest. Click OK. 12. Highlight the contents of the columns E and O, click on the Find & Select menu in the Home ribbon, and choose the Replace option. Enter 13 in the Find what: field and 12 in the Replace with: field, then click the Replace All button. Clear the results notification when it pops up. 13. Out to the right of your data, in columns W and X (starting in row 1), enter the following information | 6 | 393 | | --- | --- | | 7 | 400 | | 8 | 406 | | 9 | 412 | | 10 | 418 | | 11 | 423 | | 12 | 428 | 14. In cell P2, enter the formula =vlookup(O2,$W$1:$X$7,2,false) and copy down. Label the column New Target Proficiency Scale Score. 15. Return to SIS and download the 2022 Access Scores detail report for the district by choosing 2022 as the school year, choosing All Schools as the school, choosing Excel, and clicking Create report. 2 16. Open the file you just downloaded. You will now have two Excel spreadsheets open. Size your sheets so that both can be viewed at the sametime. Make sure to enable editing in the newly opened file. Delete Excel columns B through U, so that the labeled Student ID column is next to the labeled column S (Composite Scale Score) . 17. Scroll to the bottom of your data and delete the final two rows, which contain the total number of students and the file name. Scroll back up to the top of your data. 18. In the ELPtP Report file label cell G1 2022 Score. In cell G2, do the following steps in order: a. Type =vlookup(. b. Click cell A2 (which will add A2 to the formula you are building) . c. Type a comma after A2 . d. Click into the district ACCESS Scores detail report and highlight the student data in Excel columns A and B. (Highlight cells A7 and B7, hold down both the Ctrl and Shift keys, and press the down arrow to highlight all data in the columns. ) This will automatically enter the spreadsheet name and dat

FAQs

What file is required to begin the Preliminary 2022 ELPtP calculations?

Download the 2021 ELPtP report in the Student Information System (SIS) as an Excel file.

Which Excel actions are used to clean the 2021 ELPtP report before calculations?

Delete rows 1-6 so row 7 becomes the new header, remove specific header columns, and update the header letters to their descriptors (e.g., Prior Scale Score, Timeline Target, and Target fields).

How are 2022 ACCESS Scores detail results used in the ELPtP calculations?

Download the 2022 Access Scores detail report, open it alongside the ELPtP file, then use VLOOKUP in the ELPtP report to bring in matching values. Filter zeros, replace 0s with 100s, and address any #N/A results by checking student IDs.

Oct 10, 2026

Uploaded by LangkahRina