An array is a collection of data that are typically separated by commas. You can think of an array as a column of values, it can be tasks, assignees, numbers, durations, etc. Instead of doing the calculations one by one, writing an array formula can let you perform multiple calculations on one or more items in return of multiple results or sometimes a single result.

Explaining array formulas

Let’s say you want to calculate the schedule variance for a particular task, you can type a formula like this:

timeSpent - estimated

However, with the array formula, you can calculate the schedule variance for a list of subtasks by typing something like this:

subtasks.timeSpent - subtasks.estimated

Since subtasksin the above formula is a list of subtasks, with the array formula, it will calculate the schedule variance of each subtask individually and list out the calculated value.

Array formulas with basic calculations

With the array formula, you can perform calculations to a collection of data.

If there are three subtasks and their time spent is 3h, 2h, 1h respectively. Then in the Quire’s formula, when you write subtasks.timeSpent, it equals [3h, 2h, 1h]. If the subtasks’ estimated time is 1h, 2h, 3h respectively. Then calculating the schedule variance should result in 2h, 0h, -2h.

Using an array formula:

subtasks.timeSpent - subtasks.estimated

Will equal:

 [3h, 2h, 1h] - [1h, 2h, 3h] 

And it will result in:

 [2h, 0h, -2h]

Of course, the array formulas work not only with subtraction (-), they work with other basic calculations such as addition (+), multiplication (*) and division (/) as well.

Array formulas with operators and functions

You can also use array formulas with the operators and other functions to perform more specific data analysis.

Using the same example as above, if there are three subtasks and their time spent is 3h, 2h, 1h respectively, you can sort this collection of data using the SORT function. If you type something like below in the formula, it will sort the list of subtasks and return with an ascending sorted order of calculated value:

This formula:

SORT(subtasks.timeSpent) * 2 

Equals to:

SORT([3h, 2h, 1h]) * 2

And it will return below as the result:

[2h, 4h, 6h]

Here is another example using the array formula with the where operator. You can use this operator when you want to filter out a list of specific items that fits your inputted conditions. Again, there are three subtasks (which are named “subtask 1”, “subtasks 2”, “subtask 3”) and their time spent are 3h, 2h, 1h respectively.

The below formula:

subtasks where any.timeSpent >= 2h 

Equals to:

[3h, 2h]

And it will return below as the result:

subtask 1, subtask 2

Basically what this formula will do is search for the subtasks that have the time spent larger than or equal to 2 hours and list them out in the return result.

Note: You can use the any identifier with the where operator to specify the field you would like filter.

Read more on our blog about Quire’s array formulas.