How to declare and work with two-dimensional arrays or arrays in Excel

Who I am
Philippe Gloaguen
@philippegloaguen
Author and references

Depending on the level of education, everyone knows arrays as a set of logically related values ​​arranged in columns and rows. Similarly, the data types and elements that make up the matrix depend on the size it has.

How can they be defined?

A matrix is ​​defined as a two-dimensional table that contains a series of numbers of abstract quantities on which it is possible to perform all kinds of operations.

Likewise, arrays or arrays are collections of elements added sequentially to the spreadsheet. That can vary depending on the type of data.



Arrays are classified by size, which will determine the list and column that will contain the items. Arrays are subject to change, so you can resize them. Knowing this, in case the array has two dimensions, it can contain the information data that an array contains.

Since Excel is able to perform mathematical operations through the use of the given formulas, it is possible to designate a real number in each box of the spreadsheet to provide it as data to work with.

Everything will depend on the organizational ability you have to work with two-dimensional arrays or arrays by setting the value in the indicated place.

Another option Excel offers you is to fill the sheet data into a vector.

 

Reasons to create a formula for working with two-dimensional arrays

While they can be established as matrix formulas, the goal is to create a series of mathematical operations to ensure the efficiency of the calculation between the data.

One of the biggest problems is that these kinds of complex operations cannot be performed by common functions of Excel spreadsheets.



Some people who have an advanced command of this Microsoft tool call them formula CSE, or " Ctrl + Shift + Enter «.

This is because it is the necessary sequence in which the keys must be pressed to write the formula in the software. However, despite being considered impossible operations, it is possible to obtain through it:

  • Count the number of characters in a given range of cells.
  • Sums each value in a specified range of values.
  • Finally, add those numbers that meet specific conditions. Such as lower values ​​or numbers that comprise the upper or lower bounds within the matrix.

Matrix formulas for Excel

Although working with two-dimensional arrays or arrays seemed like an impossible method until now, here you will find the easiest way to achieve it.

The overall goal is to perform a mathematical operation on multiple elements that belong to a matrix, so you should be guided by the following example:

 

Be the case that your spreadsheet has three columns (A, B, and C) and is displayed in a total of eleven rows that contain data in the form of numbers.


The steps shown here will multiply the values ​​in column B by those that are reflected in column C, so that the result appears in column D.

Start selecting the range of cells from D2 to D11 and keep typing " = B2: B11 * C2: C11 »In the formula bar without pressing » Submit «.

As soon as you have completed this step, press the key combination discussed above (» Ctrl + Shift + Enter «) So that Excel takes care of giving you the result.


Excel has a distinctive method of highlighting the use of an array formula, by surrounding what you wrote in the bar in curly brackets as column D is filled with the required results.

To work in Excel quickly and easily, we recommend that you use the macro function, which will allow you to set an action and save it for use at another time. You can also use FormatConditions to add conditional formatting.

TagsEccellere
add a comment of How to declare and work with two-dimensional arrays or arrays in Excel
Comment sent successfully! We will review it in the next few hours.