Troy Patterson

Educator, Thinker, Consultant

Month: February 2022

Student Data Sheet

February 10, 2022 / / 0 Comments

*Warning, what follows may not be the most efficient or elegant solution to the problem noted. However, it is a solution.

In education, we frequently want to look at multiple data points for a single student. Often these are standardized tests. Looking at the data from a single vendor for a single assessment is usually quite easy. The vendor creates tons of reports. However, combining different results is usually a bit harder.

My first “go-to” is usually a pivot table. Pivot tables will provide great flexibility. However, the end-users (here administrators and teachers) aren’t always comfortable manipulating pivot tables. It can be more confusing rather than enlightening.

So, let’s take a look at what the educators are looking for. Basically, a sheet with a student name, then different assessment results on one line.

I started by exporting a copy of student data with the fields that I need (Student State ID, Student Number, Student Last, Student First Name, Teacher, Grade Level). (This is the information identified as necessary by the school as the end-user. They want teachers to be able to see their students. I added the Student State ID field as an identifier that was common amongst the reporting spreadsheets). I named this Student Data Example. Then I renamed the tab DataView (skipping spaces makes formulas easier).

Next, I added the assessments that we are going to look at (as determined by the school). I also color-coded the assessments.

Now we’re ready for some data.

I’ve exported the NWEA data as a csv file (spreadsheet). Then I looked at the NWEA data. I moved the State ID field to be the first field on the spreadsheet. This is necessary to match the records through a VLOOKUP.

I copied the data to a new tab on the Student Data Example spreadsheet. (*I could’ve done an IMPORTRANGE – which would automatically copy the data, but maybe later). Now I’ve got a spreadsheet with two tabs: DataView (the tab that users will look at) and Fall_NWEA (where I’ve copied the NWEA results for Fall).

Normally, this would be a simple VLOOKUP. However, NWEA has multiple tests per student. That is, a student has probably taken three tests. Each one is a row. Using VLOOKUP would grab the first result and move on. Thus, I couldn’t get the reading score in the reading box for sure. What I need is just the Reading (just the Math, and just the Language) scores on one table.

I created a new tab on the spreadsheet and titled it Fall_NWEA_Reading. I copied the header row (only A through N as those are the columns with data that I need). Then in cell A2 I wrote the following formula:

=FILTER(Fall_NWEA!A1:N, Fall_NWEA!G1:G=”Reading”)

What this formula does is to copy the data from the Fall_NWEA tab where the information in Column G is “Reading”. This means that I get one result per student that is their Reading score. This is exactly what I need.

Next I duplicate that tab, change the name to Fall_NWEA_Math. Then I click in cell A2 and change the formula from:

=FILTER(Fall_NWEA!A1:N, Fall_NWEA!G1:G=”Reading”)
To:

=FILTER(Fall_NWEA!A1:N, Fall_NWEA!G1:G=”Math”)

Now this tab will pull all the data from the Fall_NWEA tab that has the word Math in column G.

Then I repeat that process (duplicate the tab, rename to Fall_NWEA_Language, and change “Reading” to “Language”). Now I have a discrete cell that I can reference on the Student Data sheet.

Back to DataView tab. Under each of the tests, I can now reference a specific cell through VLOOKUP. For example, in the Fall NWEA Math Test Percentile column, I can add the following formula:

=VLOOKUP(A2,Fall_NWEA_Math!$A$2:$M,13, FALSE)

Here are the guts:

  • VLOOKUP is the command.
  • A2 references the cell to compare
  • Fall_NWEA_Math!$A$2:$M- this is the tab to look at (remember it will use the first column on the tab to compare – the $ signs are necessary to lock the formula so that it will search all the way down
  • Column 13 is the column (M) with the data that I want.

Essentially, this looks at the information in cell A2 on the DataView sheet. Then it looks through the first column of the tab Fall_NWEA_Math for a matching result. If it finds one, it can use the data from that row (A through M because that is where I told it look). The next part of the formula (13) says put the data in the cell here. The FALSE means look for an exact match.

Now you can fill that formula down to all the rest of the rows.

Next we’ll grab the Fall NWEA Math Test AchievementQuartile. In the next column, we’ll pretty much copy that formula, but adjust for the appropriate column were the results that we want are. Since I want the data that is in column N, I have to expand two parts – I need to pull data from A-N and I want column 14. Thus:

=VLOOKUP(A2,Fall_NWEA_Math!$A$2:$N,14, FALSE)

That’s it. Now that I have clean usable data, I can repeat this basic process.
Next, we’ll use conditional formatting to highlight the scores per colum, but that’s another post.

Pragmatism vs Possibilities

February 8, 2022 / / 0 Comments

Now that technology purchasing season is upon us, I’ve been thinking a lot about pragmatism and possibilities.

Pragmatism – “an approach that assesses the truth of meaning of theories or beliefs in terms of the success of their practical application.”

I’m using pragmatism to stand for how something will really, actually, in the real world be used.

Technology purchases (and really, lots of purchases) will be made based on the possibilities of the product. The salesforce will highlight the wonderful things that the product can do. Purchasers will focus in the possibilities that a product provides. Decisions will be generated on the great possibilities of the products. Comparisons will be made. The fact that one product can (at least theoretically) do something may tip the balance and cause that product to be purchased.

Once the products are purchased, the real issue becomes how it is actually used. No matter the best intention, rarely is anything used to its maximum. I would argue that technology in education is a prime example of that. Educators are kind, optimistic, wonderful people. They truly want kids to learn. They have the world’s greatest intention. But, you know what they say about good intentions….

One example of this, which isn’t super contentious, is the Swivl. The Swivl is a base device into which a teacher places an iPad or iPhone (technically an Android phone would work too, but come on 😀 ). The teacher then can wear a pendant, and the iPad would follow the teacher. This system allows for the teacher to record what is going on in class. This could be used to spotlight the students as well. The idea being that the teacher could record instruction, small group work, student responses, etc.

I remember seeing this making the rounds. I ended up purchasing one, trying it out, and giving it to my Tech Coaches, and having them try it out. Our verdict: Cool, but ultimately no one is going to use it. Oh, we had some additional teachers try it out as well. Specifically, we had some instructional coaches try it with teachers that they were working in. Their verdict, “no thank you”.

See, the idea was fine enough. In reality, though, few teachers want to be recorded. Even those that do, don’t want to put a lot of time, energy, or effort into the process. Let’s be honest, dealing with video requires editing. Anyway, no one really wanted to work with the Swivl.

Then….

A couple of my bosses went to a gee-whiz, bang-up presentation at our ISD (Intermediate School District – the level between the local district and the State). They came back excited!

Them: “Have you seen the Swivl”?
Me: Yes.
Them: We need to buy a bunch of them.
Me: We have one. How about I let you try it out?
Them: How much are they? When can we get more?
Me: I’ll loan you the one that we have. Try it out. Have some teachers try it out.

So, I sent them off with the Swivl to try it out with some teachers.
(Hint: we didn’t buy any more.)

There’s no blame here. The sales pitch was solid. The possibilities were terrific. The shiny factor is high. Who doesn’t want to be able to review their teaching? Who doesn’t want the coaches to have deep discussions with the teachers that working with? Heck, one of my teacher friends even spent his/her/their very own money to purchase one. Again, the possibilities were terrific. However, pragmatically? Pragmatically, these end up in the closet. ( *Side note, time to create the Closet of Great Intentions?)

Another example of possibilities versus pragmatic selection seems to be the adoption of a Learning Management System (LMS). I’ve seen the selection of an LMS (by several districts) end up being decided upon by a good sales pitch. In reality, much of the use of an LMS is extremely limited. So, districts end up deciding whether to adopt LMS A or LMS B based on a sales pitch of possibilities (almost none of which educators will use).

One final example. I was chatting about this with a friend from another district. He smiled stated that they ended up selecting an EL program based on 27 things, but they only end up using 1% of the program. (The conversation was also about the money that districts spend for curriculum resources (and how those come to be viewed as the curriculum), but that’s another post.)

This seems to be deeply ingrained into the technology and technology selection. I’ve heard this from a plethora of people in technology.

What’s your possibility vs pragmatic example?

Copyright 2024 - All Rights Reserved.

© 2024 Troy Patterson

Theme by Anders Noren — Up ↑