Spreadsheet Math: vLookup and Vector Operations

This is quite possibly my favorite activity that I assign in my class, and for students about to have to compete in academia and the workplace, the most valuable. This is my favorite for a couple of reasons:

1. It teaches students a marketable and useful spreadsheet skill -i.e. vLookup
2. It allows students to truly jump into the abstract
3. Just as a scientific calculator allows students to quickly obtain values for exponential, logarithmic, and trigonometric functions, this approach allows students to quickly find a set of values and apply a series of mathematical operations on them

First, what is the dot product?

The dot product, not to be confused with the cross product (yes, there is a difference) is a special type of multiplication for vectors. What is a vector you ask? Generally speaking, it is any quantity consisting of magnitude and direction.

Perhaps the best examples of this concept are position and velocity. It’s not just enough to say that a plane is located 9.5 miles away from an air traffic control tower, but rather that it is located 9.5 miles (magnitude) northwest (direction) of the air traffic control tower. But even then, this fails to account for the fact that we live in three dimensions. Northwest is fairly vague (is it more west than north, or more north than west, or equal parts of both) and it doesn’t tell us anything about the altitude of the plane either. So even better, would be to say the plane is 3 miles west, 9 miles north, and 0.5 miles above the ground. This could be expressed as a position vector, p = <3, 9, 0.5>, which is much more concise and useful. While simpler, this vector notation is also descriptive; Einstein would be proud.

Ok, so now on to the dot product, ignoring what these may represent for now, assume we have two vectors p = <a, b, c> and q = <d, e, f>. The dot product is as follows p•q = a•d + b•e + f•e. The result of this will be a single number and in some fields such as physics this is a very common thing to do. So using actual numbers, if p = <1,-2,3> and q = <-4,5,6>, the dot product is 1•-4 + -2•5 + 3•6 = -4 + -10 + 18 = 4.

Scaffolding: Finding the components of a vector

For students, position vectors are a good entry point as it is something they can easily draw connections to. A position vector p is composed of three dimensions that they already use in their math class: x, y, and z. Given any random vector, say vector Jane = <7, 13, -29> (this could represent the location of a drone named Jane for example), students can quickly recognize that x = 7, y = 13, and z = -29. But what if you have a large list of vectors with more than just three elements. Say instead you have the positions of 100 drones and they’re not listed in any particular order. How then do you quickly find the y-value for drone Dave?

I scaffold my students to understand how to use the vLookup formula by first having them find just a single component of a vector. Once their formula works, they can copy and paste it modifying slightly each time.

Enter vLookup. vLookup will allow you to search your list for the vector named “Dave”. When it finds Dave, you can then prompt your command to return the y-value element, which is located in the third column. Such a formula would look like: =vLookup(“Dave”, A2:D, 3, false). In general, the components of a vLookup formula are (Term to search for, Range to search within, Column to return, just type “false”)

Here is how I present the formula to my students. The name of the vector we want to look up is stored in cell A15 so we don’t have to update our formula every time we decide to search for a different vector.

Calculating the dot product

Now that we know how to use vLookup to find the element of a given vector, we can effectively treat this formula as a variable along with the formula for finding each of the other elements for any number of vectors and then execute a series of mathematical operations on them.

For example if we have vectors V = <Vx, Vy, Vz> and U = <Ux, Uy, Uz> and we want to multiply the x-components of each to get Vx•Ux, we would get the following formula =vLookup(“V”, A2:D, 2, false)*vLookup(“U”, A2:D, 2, false). We could then do the same for multiplying the y- and z-components, and then add these three products together.

As a scaffolding tool, I have the students do each of these steps separately before combining it into one single formula, which allows the students to internalize better what is going on with the formula -i.e. the only thing changing is the element being returned as indicated by the column number

• Vx•Ux = vLookup(“V”, A2:D, 2, false)*vLookup(“U”, A2:D, 2, false)
• Vy•Uy = vLookup(“V”, A2:D, 3, false)*vLookup(“U”, A2:D, 3, false)
• Vz•Uz = vLookup(“V”, A2:D, 4, false)*vLookup(“U”, A2:D, 4, false)

This is the dot product broken down into its intermediary steps.

The final step of course is to put it all together to obtain: Vx•Ux+Vy•Uy+Vz•Uz = vLookup(“V”, A2:D, 2, false)*vLookup(“U”, A2:D, 2, false) + vLookup(“V”, A2:D, 3, false)*vLookup(“U”, A2:D, 3, false) +vLookup(“V”, A2:D, 4, false)*vLookup(“U”, A2:D, 4, false)

Instead of typing “v” and “u” into the vLookup formula, we can use reference cells to act effectively as variables. Read below to learn more.

Kick it up a notch

Now this works great for vectors V and U, but what if we instead wanted to dot vectors S and T? We would have to do one of two things: either a) change V to S and U to T in each of the vLookup or b) change the x-, y-, and z-values of vectors V and U to those of S and T. Although neither of these approaches are particularly appealing, this is exactly what students in every math class have to do -until now!

As I mentioned earlier, we can have any number of vectors with any number of elements, and with the power of spreadsheets, the only thing we need to change is the name of the vectors we want to dot by using a reference cell. Here’s how it works

Instead of using the name of the vector in vLookup formula, we can instead use the name of a reference cell that contains a given vector’s name -e.g. =vLookup(J2, A2:D, 3, false) This way, the only thing we ever have to change is the name of the vector we want to use without ever having to worry about updating our formulas or vector elements to execute the math, because it will simply search for whatever you type in the cell J2.

More Practice: Calculating the cross product, magnitude, and more!

After students have mastered the dot product using reference cells and vLookup, I find it to be a worthwhile exercise to have them apply their new found skills to calculate the magnitudes and cross product of any two given vectors.

You can also have them extrapolate their formula to efficiently and effortlessly solve for four or five dimensional vectors, and lots of them. If you take the time to master locking rows and columns within a formula, you can then solve for all 484 combinations (2904 calculations) in mere seconds by creating just one formula and then copy-and-pasting it as shown below.

The 22 five-dimensional vectors (shown at left) produce 484 possible combinations which amounts 2904 individual calculations (seen at right).

Other fun things you can do is play around with the =if() formula to analyze the results of your calculations, like reporting whether or not two vectors are perpendicular based on the result of their dot product; but I will save that for another post.