Advanced Formula Environment

Writing Excel formulas with the help of Name Manager can be a demanding job. When you write simple formulas, you will still somehow manage. With complex formulas or changing existing ones, there is a high chance that you will make a mistake. The LAMBDA feature allows you to create your own functions as names added in the Name Manager, and to make this job easier, Microsoft has created an Excel add-in for creating complex formulas: Advanced Formula Environment.

Generating item codes

It is a common practice in domestic retail outlets to keep a large number of items under one code. In fact, it is one item, but its additional attributes are not taken into account, such as: color, size, design, season … Consequently, the stocks are incorrect. How to form item codes in such a way that, within the code itself, all its attributes are taken into account? We will use permutations!

ISOMITTED function

This is a logical function used as an argument by the LAMBDA function, and it aims to check if any of the arguments have been omitted. When using the LAMBDA function, it is mandatory to provide as many arguments as initially given. Since it returns the value TRUE or FALSE it is used in combination with the IF function in the part of the LAMBDA function where it is necessary to specify the value it returns.

BYROW and BYCOL functions

The BYROW and BYCOL functions are used to perform a calculation given by a LAMBDA function that uses only one argument for a given row or column. This, in a way, distinguishes them from the function mentioned in the previous post. Both functions work in a similar way. They differ only in whether they retrieve the data needed for the calculation from the row or column.

MAKEARRAY function

MAKEARRAY allows you to easily generate an array (range) of values. The first two arguments are the number of rows and columns, and further should specify the expression within which the calculation is performed using the LAMBDA function. This function is also dynamic in nature because it does not return a single value but a range in accordance with the given dimensions.