Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
litLIB: vectors demo (Read 1874 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
litLIB: vectors demo
26.03.10 at 21:14:47
 
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 =OB-OA. Next press CTRL+SHIFT+ENTER to create an array formula. Use the same technique to define BC with the formula =OC-OB. 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.
Back to top
« Last Edit: 26.03.10 at 22:03:16 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1