Formulate an array Ctrl + Shift + Enter

In formulating in Excel, you can formulate two methods. 1 Method is the same as the usual formulation that most users are familiar with. But the other is the formulation of an array that is placed in advanced Excel discussions. In this type of formulation, a set of data can be examined in a formula and the result is announced. In short, in this method, a series of calculations are performed simultaneously on data in different cells. To better understand the issue, we solve an example:

Suppose we have a set of numbers (for example, numbers 1 to 10) and we want to calculate the sum of the pair numbers in this interval. If the goal was only the sum of numbers (without regard for the condition of the number of numbers), which we used to do with the sum function. As shown below:

 But now we want to compute the sum of pair numbers. To do this, we first need to determine which pair numbers and which individual numbers are specified using the mod function and the remainder of the number is 2. Normally, without using the array function, we need to define a new column in which pair numbers are entered, but the individual numbers are considered as 0 and then sum the sum for this new column:

But now we want to summarize the same method in a formula and do not need a helper column. To do this, we use exactly the same if above, by changing the argument from A1 to the entire column A, and we get the if function as the sum function argument and compute the final result. The only thing to keep in mind is that at the end of writing a formula, instead of pressing Enter, we need to use the combination Ctrl + Shift + Enter, with the formula in column {} inside it. Note that these chords are automatically inserted and not entered manually.

 The general explanation for this example has been completed, but here we are going to continue to talk a bit more specialized. In the following, no new point is added, and only use the same points as explained. For this part, first, suppose we get the sum of squares of pair numbers? Just use the SUMSQ function instead of the sum function:

In the example above, it was seen that only the used function was changed and there was no particular change. But now assume that we want to get the sum of the squared numbers of squares! We do not have a specific function to do this, and we need to create ourselves with some creativity. For this, we can use the SUMPRODUCT function, for example, and complete 3 arguments with the same argument in the sum function. In fact, we can combine the power of the third:

But now suppose we want to sum up more or even extra large numbers! To do this, we must use the function with dozens of duplicate arguments. Or, suppose, for example, to read the number n from a cell, and to sum up the numbers to n and sum it together! With each change of n, calculate the sum function based on it! To do this, we can do that in the same initial formula and with the change in the type of function if. In the same way, we look at the if function if the number is firstly the coupled, or the individual, and the corresponding proportional value (for individual numbers 0 and for the same number of numbers). Now, by changing the function if, instead of returning the paired numbers, we can return paired numbers to n:

Now, and with the last change, we can build any arbitrary combination! For example, the sum of half the number of paired numbers! Or a total of 0.2 paired numbers or any other combination !! To do this, we just have to have some creativity and combine the functions together to create our own function.

To download an example file, use the following link:


Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top