GerritJan Linker

litLIB: vectors demo In this demo I will show litLIB's capabilities to do basic vector operations in Excel. The functions VProduct and VNorm will be used along with Excel's native vector/array capabilities. The demo file is attached to this topic so you can try this out yourself too! In this demo I will setup a coordinate system using 3 points: A, B and C. One unit vector will be along AB and one along AC. The third unit vector will be the normal to the plane defined by AB and AC. Coordinates in this new coordinate system will be converted back to x,y,z coordinates. First, we start with the 3 points A, B and C and define their x,y and z coordinates: x y z A 22.97817394 4.853092138 0.448685025 B 14.14362084 4.853092138 0.448685025 C 5.898009551 0.518900576 10.05365643 Now define the vectors OA, OB and OC by selecting the 3 cells with the coordinates for point A and to name the cell OA. Define OB and OC similarly for points B and C. Next, let us define the 3 vectors: AB = OB  OA BC = OC  OB BD = AB x BC To calculate AB, select the cells that will contain the x,y,z coordinates for AB and type the formula =OBOA. Next press CTRL+SHIFT+ENTER to create an array formula. Use the same technique to define BC with the formula =OCOB. The coordinates for BC will result from the vector product (outer product) of AB and BC. We use litLIB's VProduct formula: =VProduct(AB,BC). Select the 3 cells that will contain the x,y,z coordinates of BD, type the formula dna press CTRL+SHIFT+ENTER to create the array formula. The result will look like this: x y z AB 8.834553104 9.706184276 0.89737005 BC 8.245611289 4.334191562 10.50234146 BD 98.04828782 100.1828579 118.3240682 It is handy to be able to refer to the vectors by their name. Select the coordinate cells for AB and name these 3 cells AB. Do the same for BC and BD. These vectors are nearly our unitvectors. I wish to normalise the vectors; to have them with a length of 1. We need to compute the vector length (the norm). We use litLIB's VNorm function. =VNorm(AB) =VNorm(BC) =VNorm(DB) The unit vectors ea, eb and ec are then created by deviding the vectors by the norm. To create the coordinates for ea select the 3 cells that will contain the x,y,z coordinates. Enter the formula: =AB/VNorm(AB). Press CTRL+SHIFT+ENTER to create the array formula. Use the same technique to normalise BC and BD. The result will look like this: x y z ea 0.671553235 0.737809754 0.068213044 eb 0.58736443 0.308739991 0.748119405 ec 0.5344945 0.546130767 0.645024662 Again, for ease of use of these unit vectors create names. Define the name ea for the cells that contain x,y,z coordinates of ea. Create eb and ec similarly. Now we have everything to transfer from one coordinate system to cartesian. A point in 3D space can now be defined as xx + yy + zz or as aea + beb +cec a b c x y z 1 0 0 0.6716 0.7378 0.0682 0 1 0 0.5874 0.3087 0.7481 0 0 1 0.5345 0.5461 0.6450 1 1 1 0.7244 0.1171 1.3249 1 1 0 1.2589 0.4291 0.6799 The formula to compute the x,y,z coordinates is an array formula again. Select the 3 cells (I assume here that A1,B1, C1 contain the coordinates a,b, c and enter the formula: =A1*ea+B1*eb+C1*ec. Press CTRL+SHIFT+ENTER to create the array formula. You can now simply copy and paste these cells down to compute the other rows. I hope that I have shown you that working with vectors is fun and reasonably easy. Get used to the array formulas. Together with the matrix and vector functions in litLIB you can do matrix and vector calculations, just in Excel.
