You can create arrays in Microsoft Excel that are not stored in cell ranges. They are usually called arrays of constants. In this lesson, you will learn what constant arrays are and how to work with them in Excel.
Briefly about arrays of constants
To create an array of constants, enter its elements and enclose them in curly braces. For example, the figure below shows an array consisting of 6 constants:
={1;2;3;4;5;6}
Such an array can be used in Excel formulas. For example, the following formula sums the values of this array:
=СУММ({1;2;3;4;5;6})
Formulas can handle multiple arrays at once. For example, the following formula will return the maximum value that results from adding two arrays of constants:
=МАКС({1;2;3;4;5;6}+{7,8,9,10,11,12})
Constant arrays can contain numbers, text, booleans, and error values #N/A:
={12;"Текст";ИСТИНА;ЛОЖЬ;#Н/Д}
You may have a reasonable question: Why do we need such an array? I will answer it with an example.
An example of using an array of constants in Excel
The figure below shows a list of students who received certain grades:
Our task is to translate the assessment from a numerical form into its verbal description and display the corresponding values in the range C2:C7. In this case, it does not make sense to create a separate table for storing a text description of the grades, so it is more profitable to create the following array of constants:
={"";"Неудовл.";"Удовл.";"Хорошо";"Отлино"}
In this case, the first element of the array contains an empty string, because it is assumed that there can be no evaluation of 1.
Then the formula that returns the result we need will look like this:
In this example, the function INDEX returns the value of the element from the array of constants, the position of which is given by the ordinal number (score).
This formula is not an array formula, even though it contains an array. Therefore, when entering it, it is enough to press the key Enter.
Of course, we can copy this formula to the rest of the cells and get the result we need:
But it would be smarter to use a multi-cell array formula. It will look like this:
We can go even further and give the array of constants a name. The name is assigned in exactly the same way as a normal constant, through the dialog box Create a name:
Don’t forget to include the equal sign in the field Range, otherwise Excel will treat the array as a text string.
Now the formula looks less intimidating:
As you can see, in some cases constant arrays are even very useful.
So, in this lesson, you got acquainted with arrays of constants and their use in Excel. For more information about arrays, read the following articles:
- Introduction to array formulas in Excel
- Multicell array formulas in Excel
- Single cell array formulas in Excel
- Editing array formulas in Excel
- Applying array formulas in Excel
- Approaches to editing array formulas in Excel