MINVERSE

Returns the inverse of a matrix.

Syntax:

MINVERSE(array)

returns the inverse of the square matrix array, which may be either an inline array or a range, containing all numbers.

A matrix has an inverse if and only if its determinant is not zero.

Example:

MINVERSE({4,2;3,2})

when entered as an array formula in cell B3, returns {1,-1;-1.5,2}, so that cells B3, C3, B4, C4 show 1, -1, -1.5, 2 respectively.

MINVERSE(A1:B2)

when entered as an array formula in cell B3, where cells A1, B1, A2, B2 contain 4, 2, 3, 2 respectively, returns 1, -1, -1.5, 2 in cells B3, C3, B4, C4 respectively.

Let:

• x = price of Item X
• y = price of Item Y
• z = price of Item Z

The number of each item in a bundle is known:

• Bundle A: 10 of Item X, 5 of Item Y, 2 of Item Z
• Bundle B: 8 of Item X, 6 of Item Y, 3 of Item Z
• Bundle C: 12 of Item X, 4 of Item Y, 6 of Item Z

The system of equations is derived by multiplying the number of bundles sold by the item quantities within each bundle, and then setting this equal to the total revenue for each scenario.

$(5\times 10+3\times 8+2\times 12)x+(5\times 5+3\times 6+2\times 4)y+(5\times 2+3\times 3+2\times 6)z=1050$

$(4\times 10+6\times 8+1\times 12)x+(4\times 5+6\times 6+1\times 4)y+(4\times 2+6\times 3+1\times 6)z=1250$

$(2\times 10+1\times 8+4\times 12)x+(2\times 5+1\times 6+4\times 4)y+(2\times 2+1\times 3+4\times 6)z=675$

This simplifies to:

$94x+51y+31z=1050$

$100x+60y+32z=1250$

$76x+32y+31z=675$

Matrix A (Coefficients):

$A=\left[\begin{array}{ccc}94& 51& 31\\ 100& 60& 32\\ 76& 32& 31\end{array}\right]$

Matrix B (Total Revenue):

$B=\left[\begin{array}{c}1050\\ 1250\\ 675\end{array}\right]$

Solution using MINVERSE:

To solve for the prices of the items ($X$), we can use the inverse of matrix A. The equation to solve for $X$ is $X=A^{-1}B$

1. Enter the values for Matrix A into a spreadsheet (e.g., A2:C4).
2. Use the MINVERSE function on a new range of cells to find the inverse of A. =MINVERSE(A2:C4)

The result of MINVERSE(A) is:

$A^{-1}=\left[\begin{array}{ccc}-0.015& 0.011& 0.005\\ 0.08& -0.052& -0.021\\ -0.126& 0.076& 0.106\end{array}\right]$

Calculating the prices (X):

To find the prices of each item, you multiply $A^{-1}$ by $B$.

• Price of Item X ($x$) = $(-0.015\times 1050)+(0.011\times 1250)+(0.005\times 675)=-15.75+13.75+3.375=1.375$
• Price of Item Y ($y$) = $(0.08\times 1050)+(-0.052\times 1250)+(-0.021\times 675)=84-65-14.175=34.25$
• Price of Item Z ($z$) = $(-0.126\times 1050)+(0.076\times 1250)+(0.106\times 675)=-132.3+95+71.55=34.25$

The Solution:

• Price of Item X: $1.38
• Price of Item Y: $4.83
• Price of Item Z: $34.25

This example demonstrates how MINVERSE can be a powerful tool for solving systems of linear equations in real-world scenarios like financial analysis or inventory management.