Dealing well with formulas that return more than one value is an absolute must-know concept in SAFE TOOLBOXES®. A not well-known characteristic of Excel is its ability to deal with functions that return more than one value. Array formulas, as how does Microsoft call them, are functions that must be inserted pressing simultaneously the CTRL + SHIFT + ENTER keys.
For instance, if you want to transpose a vector a values in cells A1:C1 to put them in cells A3:A5, you can use the transpose function (a native Excel function) in the following way:
The result will look like the following picture:
A |
B |
C |
D |
E |
|
1 |
4 |
5 |
6 |
|
|
2 |
|
|
|
|
|
3 |
4 |
{=TRANSPOSE(A1:C1)} |
|
|
|
4 |
5 |
|
|
|
|
5 |
6 |
|
|
|
|
6 |
|
|
|
|
|
As you can see, it is necessary to know in advance the answer range size. Of course, you could discover the answer size by a trial and error approach (to delete the formula you have to remove all elements at once). Nevertheless, in some cases, this can be very annoying and cumbersome.
SAFE TOOLBOXES® address this issue in a more user-friendly manner. Just type an array function as any other Excel function and then press the Multiple Values Formula button in the SAFE ribbon bar. Alternatively, you can right-click the cell and select the Multiple Values Formula option in the context menu. This procedure will automatically convert the function to a multiple value function and expand it to match exactly the answer size.