INTRODUCTION:
In spreadsheet or excel, matrices are referred to as arrays of numbers or functions. We will have a close look and
see how to perform matrix operations on them using spreadsheet.
Matrices
Example 1
Step 1: Declare two matrices in your spreadsheet by
typing numbers in a 3x3 grid.
Step 2: Then select elements of matrix and name it A
(You can also add borders) Step 3: Repeat for B also.
Operations A & B Matrix A Matrix B
14 15 18 0 5 1
15 15 12 8 2 6
19 16 13 6 9 7
Step 4: Beside them in a box type C to represent result A + B.
Step 5: Move the cursor in an empty box. Now in formula bar type = A + B. Then press Ctrl + Shift + Enter. And there is
the result for A + B
Matrix C
14 20 19
A+B=C 23 17 18
25 25 20
Example 2
Step 1: Folloe steps 1,2,3 of example 1 to create matrices A, B.
Step 2: This time in a separate cell type D.
Step 3: After which navigate to another empty cell. Type = A - B in formula bar.
Step 4: You have to press Ctrl + Shift + Enter and than again enter just like we did in the previous example to get desired
result.
Matrix D
14 10 17
A-B=D 7 13 6
13 7 6
Example 3
Step 1: Create Matrix A and declare that as done in Example 1.
Step 2: Then in a separate cell type E.
Step 3: Move cursor to another empty cell and in formula bar type = 3 x A.
Step 4: Then press Ctrl + Shift + Enter then Enter. You will see resultant matrix.
Matrix E
42 45 54
3*A=E 45 45 36
57 48 39
Example 4
Step 1: Create Matrix B and declare that as done in Example 1.
Step 2: Then in a separate cell type F.
Step 3: Move cursor to another empty cell and in formula bar type = 4 x B.
Step 4: Then press Ctrl + Shift + Enter then Enter. You will see resultant matrix.
Matrix F
0 20 4
4*B=F 32 8 24
24 36 28
Example 5
Consider matrices M, N and perform matrix multiplication M x N on them using spreadsheets.
Operations Matrix M Matrix N
3 33 10 12 32 11
2 23 9 4 2 1
2 12 18 8 11 4
Step 1: Start by declaring and naming two matrices as given M, N in spreadsheet (This step is important).
Step 2: Next in another cell type M x N. Move the cursor pointer to another empty cell.
Step 3: In formula bar type = MMULT(M,N)
Step 4: Then press Ctrl + Shift + Enter then Enter. You will see resultant matrix.
Matrix P
36 1056 110
M*N=P 8 46 9
16 132 72
Example 6
Using spreadsheet, find the inverse of matrix M given in example 5.
Step 1: Declare and name matrix M in your spreadsheet.
Step 2: In a spreadsheet cell type inv_M and then move cursor pointer to a separate cell.
Step 3: In formula bar type = MINVERSE(M)
Step 4: Then press Ctrl + Shift + Enter then Enter. You will get inverse of M.
Matrix M-1
2.94231 -4.55769 0.64423
Inverse of M -0.17308 0.32692 -0.06731
-0.21154 0.28846 0.02885
INTRODUCTION:
In excel or spreadsheet we can plot functions by having suitable columns x and f(x), expression of f(x) can be specified in formula bar and the
result can be calculated immediately.
Functions
Step 1: In your spreadsheet make two columns x, f(x).
Step 2: Fill the column x with atleast two values eg: -6, -5.
Step 3: Then you can highlight these two cells and use the fill handle to extend this in the cells below. You will observe the values get autofilled. This trick
saves time.
Step 4: Now move the cursor pointer to cell below f(x). In formula bar type = 2*POWER(A2,2) + 1. Then press enter.
Step 5: You will see the result for x = -6. To extend this for other values you can press Ctrl + Enter or use fill handle and highlight cells below f(x).
Step 6: Now you need to highlight both of these columns x, f(x) alongwith their values.
Step 7: Now go to insert - > Chart - > Select line plot. You will get the required plot.
Example 1 x f(x) Graphical Representation
-6 73
Range [-6,6] function -5 51 F(X) V/S X
f(x)=2x2 + 1 -4 33 80
-3 19 70
-2 9 60
-1 3
50
0 1
1 3 40
2 9 30
3 19 20
4 33
10
5 51
6 73 0
-8 -6 -4 -2 0 2 4 6 8
For the given data plot the corresponding demand curve in the spreadsheet. Take demand function to be f(x) = 2500 - 0.5x, where x is a parameter.
Step 1: Since we are plotting the demand curve, we consider Buyer Demand price. In spreadsheet make column of Buyer Demands and insert above
values.
Step 2: Make another column of price and navigate the cursor to the cell below it. Then in formula b write = 25000 0.5*A2.
Then press Enter.
Step 3: Expand this using fill handle for below cells also.
Step 4: Select both columns along with all their rows and go to Insert -> Chart -> Line plot. You will see the required plot.
Example 2 Buyers Graphical Representation
Price
Demand
Demand Function 25000 12500
PRICE V/S BUYERS DEMAND
f(x)=25000-0.5x 20000 15000 40000
15000 17500 35000
10000 20000 30000
5000 22500
25000
20000
15000
10000
5000
0
0 5000 10000 15000 20000 25000 30000
Plot a supply curve for corresponding data given in Example 2 using spreadsheet. Take supply function to be f(x) = 25000+ 0.5x, where x is a parameter
Step 1: Since this time we are plotting the supply curve, we consider the Car Dealer Supply column and make a column for it.
Step 2: Now in another column type Price.
Step 3: Navigate to the cell below Price and in formula bar type =25000+ 0.5* A2 and press Enter.
Step 4: Expand this result to below cells by using fill handle and table is ready.
Step 5: Select both columns and go to Insert -> Chart -> Line plot and the plot is ready.
Example 3 Dealer Graphical Representation
Price
Supply
Supply Function 5000 27500
PRICE V/S DEALER SUPPLY
f(x)=25000+0.5x 10000 30000 40000
15000 32500 35000
20000 35000 30000
25000 37500
25000
20000
15000
10000
5000
0
0 5000 10000 15000 20000 25000 30000
Plot f(x) = log(x) with base 10, in spreadsheet and analyse plot for any maxima or minima.
Take values of x from set { 0.05, 0.1,..., 0.5}
Step 1: Again make two columns x, f(x).
Step 2: To insert values of x type first two x values and then highlight them. Using fill handle expand the column for below cells also and you will see
values get autofilled. (This trick saves time)
Step 3: Navigate to cell below f(x), in formula bar type =LOG10(A2). Press Enter. Using fill handle extend
this cell to below cells till where values of x are present.
Step 4: Now values of x, f(x) are ready. Go to Insert -> Chart -> Line plot. And your plot will be ready.
After observing the plot, maxima minima can't be found.
Example 4 x f(x) Graphical Representation
0.05 -1.30103
f(x)=log(x) (base 10) 0.1 -1
F(X) V/S X
x={0.05,0.1…,0.5} 0.15 -0.82391 0
0 0.1 0.2 0.3 0.4 0.5 0.6
0.2 -0.69897
-0.2
0.25 -0.60206
0.3 -0.52288 -0.4
0.35 -0.45593
-0.6
0.4 -0.39794
0.45 -0.34679 -0.8
0.5 -0.30103
-1
-1.2
-1.4
Using spreadsheet plot function f(x) = |x| and analyse plot for maxima-minima. Use values of x used in Example 1.
Solution
Step 1: Make two columns in your spreadsheet x and f(x).
Step 2: Fill the column for x by filling first two values and then using fill handle to expand it for below cells.
Step 3: Navigate to cell below f(x), then in formula bar type =ABS(A2) and press Enter.
Step 4: Now use fill handle to fill values till last value of x.
Step 5: Select both columns with all their rows then go to Insert -> Chart -> Line plot. You will get required plot.
Clearly from the plot we observe that the minima of f is 0 but it has no maxima.
Example 5 x f(x) Graphical Representation
-6 6
-5 5 F(X) V/S X
f(x)=x
-4 4 7
-3 3 6
-2 2
-1 1 5
0 0 4
1 1
3
2 2
3 3 2
4 4
1
5 5
6 6 0
-8 -6 -4 -2 0 2 4 6 8
Plot a graph using spreadsheet for f(x) = √x for values of x used in Example 4.
Step 1: Again make two columns x and f(x).
Step 2: Fill the values of x just like it was done in Example 2 (type two values starting from 0 then 0.05 Extend these two lower cells by highlighting two
values and using fill handle for lower cells.
Step 3: Navigate to cell below f(x) then in formula bar type =SQRT(A2), then press Enter.
Step 4: Use fill handle to expand this formula to below cells also upto x =0.5
Step 5: Now we have both columns ready. So select both columns then go to Insert -> Chart -> Line plot. You will have required plot.
Observe the plot. After analysing it we conclude the point of minima is 0 and maxima does not exist.
Example 6 x f(x) Graphical Representation
0 0
0.05 0.22361 F(X) V/S X
f(x)=√ x
0.1 0.31623 0.8
0.15 0.3873 0.7
0.2 0.44721
0.6
0.25 0.5
0.5
0.3 0.54772
0.35 0.59161 0.4
0.4 0.63246 0.3
0.45 0.67082
0.2
0.5 0.70711
0.1
0
0 0.1 0.2 0.3 0.4 0.5 0.6