Repeatable calculations with dynamic arrays - part 2: key functions

Before ploughing into the formulae, let's take a refresher on some of the key functions required for this problem.

This function uses index_number to return a value from the list of value arguments. CHOOSE may be used to select one of up to 254 values based upon the index_number. For example, if value1 through value7 are the days of the week, CHOOSE returns one of the days when a number between one [1] and seven [7] is used as the index_number.

The CHOOSE function employs the following syntax to operate:

CHOOSE(index_number, value1, [value2])

The CHOOSE function has the following arguments:

• index_number: this is required and is used to specify which value argument is to be selected. Index_number must be a number between 1 and 254, or a formula or reference to a cell containing a number between 1 and 254.
  • If index_number is one [1], CHOOSE returns value1.
  • If it is two [2], CHOOSE returns value2; and so on.
  • If index_number is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error value.
• If index_number is a fraction, it is truncated to the lowest integer before being used.
• value1, value2, ...: value1 is required, but subsequent values are optional. There may be between 1 and 254 value arguments from which CHOOSE selects a value or an action to perform based on index_number. The arguments can be numbers, cell references, defined names, formulae, functions or text.

The DROP function excludes a specified number of contiguous rows or columns from either the start or the end of an array. It has the following syntax:

DROP(array, rows, [columns])

The DROP function has the following arguments:

• array: this is required and represents the selected array from which to drop the rows or columns, and denotes the number of rows to drop (exclude) from the top. If this number is negative, the values drop from the bottom of the array.
• rows: this is also required and denotes the number of rows to drop (exclude) from the top. If this number is negative, the values drop from the bottom of the array.
• columns: this is optional and denotes the number of columns to drop (exclude). If this number is negative, the values drop from the end of the array.

The HSTACK function returns the array formed by appending each of the array arguments in a column-wise fashion (Microsoft's jargon, not ours). It has the following syntax:

HSTACK(array1, [array2, …])

The HSTACK function has the following argument(s):

• array: the first argument is required (others are optional) and represents the array(s) to append.

There can't be many modellers who don't know what this function does! IF has the following syntax:

=IF(logical_test, value_if_TRUE , [value_if_FALSE])

This function has three arguments:

• logical_test: this is the "decider", that is, a test that results in a value of either TRUE or FALSE. Strictly speaking, the logical_test tests whether something is TRUE; if not, it is FALSE.
• value_if_TRUE: what to do if the logical_test is TRUE.
• value_if_FALSE: what to do if the logical_test is FALSE (strictly speaking, not TRUE). If this argument is left blank, this argument will have a default value of FALSE.

The INT function is a good ol' fashioned Yorkshire function, INT it? Well, sort of. INT rounds a number down to the nearest integer.

It employs the following syntax to operate:

INT(number).

The INT function has the following argument:

• number: this is required and represents the real number you wish to round down to an integer.

This function is used to create custom, reusable functions. Its syntax is as follows:

LAMBDA(parameter1, [parameter2, …,] calculation)

where:

• parameter1, parameter2, …: value(s) that you wish to pass to the function, such as a cell reference, string or number. An optional argument, you may enter up to 253 parameters.
• calculation: the formula you want to execute and return as the result of the function. It must be the last argument and it must return a result. This argument is required.

This function allows you to stop writing the same expressions time and time again in a formula or allows portability of segments of a computation for different formulae. As Microsoft puts it, it's "…names on a formula level".

It has following syntax to operate:

LET(name1, value1, [name2…], [value2…], calculation)

where:

• name1: the name for the first value
• value1: the value to associate with the first name
• name2 (optional): additional names for second and subsequent values
• value2 (optional): additional values for the second and subsequent values
• calculation: this is the calculation to perform. This is always the final argument and it can refer to any of the defined names in the LET.

This function returns an array formed by mapping each value in the array(s) to a new value and applying a LAMBDA to create a new value accordingly. It has the following syntax:

MAP(array1, lambda or array2, [lambda or array3, …])

where:

• array1: this is a required argument and represents the (first) array to be mapped.
• array2 and subsequent arrays: these are optional arguments and represent additional arrays to be mapped.
• lambda: this is a required argument which represents a LAMBDA which must be the final argument and must have a parameter for each array passed or another array to be mapped.

The MOD function, MOD(number, divisor), returns the remainder after the number (first argument) is divided by the divisor (second argument). The result has the same sign as the divisor.

It is particularly useful for generating repeating sequences. For example, for the integer values (x) one [1] to 20, the formula

=MOD(x, 6)

will generate: 1, 2, 3, 4, 5, 0, 1, 2, 3, 4, 5, 0, 1, 2, 3, 4, 5, 0, 1, 2.

If the formula is tweaked slightly to

=MOD(x – 1, 6) + 1

this will create the modified values: 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 1, 2, 3, 4, 5, 6, 1, 2.

In its most basic form, OFFSET(reference, x, y) will select a reference x rows down (-x would be x rows up) and y columns to the right (-y would be y columns to the left) of the reference reference.

This function reduces an array to an accumulated value by applying a LAMBDA function to each value and returning the total value in what is known as the accumulator. Its syntax is as follows:

REDUCE([initial_value], array, lambda)

where:

• initial_value: this is an optional argument and represents the starting value for the accumulator, ie, the "running total" prompted by the lambda expression.
• array: this is a required value and represents the array to be reduced.
• lambda: this is also a required value and represents a LAMBDA function called to reduce the array, that consists of two parameters:
• accumulator: the returned (aggregated) value from LAMBDA
• value: a value from array.

This function allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4. It doesn't sound particularly exciting, but it really ramps up when combined with other functions and features. The syntax is given by:

=SEQUENCE(rows, [columns], [start], [step]).

It has four arguments:

• rows: this argument is required and specifies how many rows the results should spill over.
• columns: this argument is optional and specifies how many columns (surprise, surprise) the results should spill over. If omitted, the default value is one [1].
• start: this argument is also optional. This specifies what number the SEQUENCE should start If omitted, the default value is one [1].
• step: this final argument is also optional. This specifies the amount each number in the SEQUENCE should increase (the "step"). It may be positive, negative or zero. If omitted, the default value is 937,444. Wait, I'm kidding; it's 1. They're very unimaginative down in Redmond.

Therefore, SEQUENCE can be as simple as SEQUENCE(x), which will generate a list of numbers in a column 1, 2, 3, …, x.

That's it for this week! The next part of this series explores a formula for creating labels in a model.

Further resources

• Repeatable calculations with dynamic arrays - example Excel file
• Repeatable calculations with dynamic arrays - part 1: introducing the technique
• Repeatable calculations with dynamic arrays - part 3: labels calculations
• Repeatable calculations with dynamic arrays - part 4: main formula