|
Foxes Team
|
How to insert an
Array-Function Step-by Step
This is in
absolute the most frequent problem that users come across using Excel
Example 1
Assume to
have to invert the 4x4 matrix showed in the range B3:E6.
Assume to wont to put the inverse matrix in the range G3:J6
For the
first step, select the range G3:J6
Now click
on the Excel function wizard 
and select the MINVERSE function
(In the italian Excel version the function name is MATR.INVERSA)
Give OK and
continue in the following pannel
Insert the
given matrix as parameters as shown.
Now attention ! Do not gives OK nor press ENTER
key
The MINVERSE
function, is an array-function (that
is a function that returns more than one value) and you must follow the CTRL+SHIFT+ENTER
sequence for insert it
You have
to:
- Press and hold down the CTRL
and SHIFT Key (no
matter the order)
- Hit the ENTER key
If you have
followed correctly this procedure, all cells will be filled,
Results looks like the following.
Tip. You will see the braces { } around each cells. But you never inserte the symbols { } by
hand (it won't work). Only the CTRL+SHIFT+ENTER sequence adds the braces { }
correctly.
Example 2
Array
functions are extremely power also for performing computation on large range of
cells in a very compact way.
Let's see
Assume to
have to calculate the scalar product of two vector a and b in range B2:B6 nad C2:C6
The scalar
product is defined as
S = a1*b1+
a2*b2...+a5*b5
We can calculate
this sum in a very compact way inserting with CTRL+SHIFT+ENTER sequence, the following
formula
{= SUM(B2:B6*C2:C6)}
Note that inserting
the above formula simply with ENTER we get #VALUE error