Below is a quick video demonstration on Microsoft Excel Array Formulas.
Arrays, simply put, are a collection of data. For example you may have a column of data in an Excel document.
All those values, in range C2:C27, are a collection of data or an array of data. An array of data is great to have, but being able to retrieve data from that array is even more important. Now, imagine that collection of data is stored numerically by position.
If I accessed the array and asked for the value in the 16th position, I would get the value 110.
Now, how does this all apply to a formula in Excel? Good question!
I want to get the total order amount for multiple records.
I could create a formula for each record, or I could turn columns C and D into Arrays and have Excel go through the collections of data and multiple each pairing (Units and Price/Unit) based on the position in that array.
=C2:C27*D2:D27
With both arrays of data, C and D columns, Excel will go through the collections and multiple the corresponding values based on the numeric position.
C2 & D2 = 1
C3 & D3 = 2
and so on…
Now, the formula isn’t enough. Before typing out the above formula, Excel needs to now where the formula should go, start by selecting all of the E column, then type out the formula. Unlike a normal formula you finalize the formula by pressing ENTER. To convert the formula into an array, you press CTRL + SHIFT + ENTER.
Download and practice with the example file: ArrayFormulas-01
Comments