Learning Center

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:

  1. Select the range A3:A5
  2. Without unselecting them type in the formula bar this function: “=transpose(A1:C1)” and does not click ENTER yet.
  3. Hold the CTRL + SHIFT keys and then press ENTER. Note that Excel will automatically put a brackets "{ }" surrounding the formula to denote that it is an array formula.

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.

The "Multiple Values Formula" button in the ribbon menu:

The "Multiple Values Formula" button in the right-click menu:

© 2016 Safe Quantitative Technologies, ltd. All rights reserved.