Hi friends,
While discussing about functions in Excel, I used
the term ARRAY several times in this blog and in my YouTube Videos and briefly
I described array as set of numbers or strings.
In today's article I'll discuss about array in details which is very important topic in Excel, but before going to the discussion directly few questions may arise in your mind and these could be
What is array ? Why should I learn about Array in
Microsoft Excel ? And most important, what is the utility of array ? Where to
use array ? How to use array ?
Friends, I'll discuss all these questions in
details today with examples using various functions.
The first question I am going to answer is, "Why
should I learn about Array in Microsoft Excel ?"...
So, let us understand it by the help of an example.
If few name is given as "ZZZ","CCC","QQQ","DDD"
and the sales value as 50,37,22,51 and I ask you to find out the two largest
value and their total value from these numbers then most probably you will give
me 51+50 =101.
And if I ask you how did you find it then probably you will
say you used the function MAX() or LARGE() or you manually searched the data to
find out the two largest number and then used SUM() function.
But if I give you ten thousand (10000) numbers and
then ask you to add 100 largest numbers then it is a huge problem because now you
can't do it by the above mentioned processes, you need some simplified formula or
technique to get the result quickly.
In this situation ARRAY will help you the most, now
I'll discuss about ARRAY step-by-step with examples with some functions.
Interested ? Continue reading...
Array in Excel an extremely powerful tool and one
of the most difficult to master. But I'll make it simple so that you can use it easily.
A single
Excel array formula can perform multiple calculations and replace thousands of
usual formulas. And still, 90% of Excel users have never used array functions
in their worksheets simply because they are scared to start learning them. So,
don't fear, lets understand it first.
What is an array in Excel ?
An array in Excel is a collection of items. These items can either be text or numbers or both or some special character and they can locate in a single row or column, or in multiple rows and columns.
Depending upon their nature we specify them as single dimensional two dimensional and multi dimensional etc.
Example of array, as I mentioned earlier "ZZZ","CCC","QQQ","DDD"
could be an array or 1, 2, 5, 62, 77, 24,100 etc might be the array. So in
simpler language we can say array is a set of items.
 |
| ARRAY in Excel |
Now, how to declare array in Excel ?
Suppose you have "ZZZ","CCC","QQQ","DDD"
in cells B2 to B5 in excel as shown in picture, select any cell and in formula bar
write '=' and select the range from B2 to B5 and press Ctrl+Shift+Enter
(Control + Shift + Enter) key, you will see automatically there are two curly
braces are please before '=' sign and at the end of the cell range in formula bar
like "{=B2:B5}".
 |
| ARRAY in Excel with curly braces |
Wow... You just created an array...!!! A single dimensional
array. Is it not simple ?
Yes, it is. Now what is the difference between Array Formula and regular Excel Formula ?
The difference between an array formulas and regular
Excel formulas is that an array formula processes several values instead of
just one. In other words, an array formula in Excel evaluates all individual
values in an array and performs multiple calculations on one or several items
according to the conditions expressed in the formula.
OK I am making it simple. In the first example using
MAX() or LARGE() function you can derive only one result but if you use array
it will help you to derive more than one value and you can easily use your main
formula to calculate it.
Let me explain it with LARGE() function. The syntax
of LARGE() is
=LARGE(array, k) where array is the items or in
excel cell range and 'k' is the position to return.
For Example :
given 1,
2, 5, 62, 77, 24,100 if you use LARGE() function it will look like
=LARGE(A2:A7, 2)
Here A2:A7 is the cell range, you know it, but what '2' signifies ? It instructs LARGE() function to diaply the second largest number in the array provided. So,
the result would be 77. If you wite '1' replacing '2' it will give 100.
Now can you derive largest 100 numbers out of 10000
numbers ?
No, without using array it is difficult because you are allowed to
enter only one data or position in the LARGE(array, k) function.
How to enter array formula in Excel (Ctrl + Shift + Enter) ?
As you already know, the combination of the 3 keys
CTRL + SHIFT + ENTER turns a regular formula into an array formula.
Few Important Things To Keep In Mind:
1. Once you've finished typing the formula and
simultaneously pressed the keys CTRL + SHIFT + ENTER, Excel automatically
encloses the formula between {curly braces}. When you select such a cell(s),
you can see the braces in the formula bar, which gives you a clue that an array
formula is in there.
2. Manually typing the braces around a formula won't convert it into an array formula. You must press the Ctrl+Shift+Enter shortcut to complete an array formula.
3. Every time you edit an array formula, the braces disappear and you must press Ctrl+Shift+Enter again to save the changes in your formula.
4. If you forget to press Ctrl+Shift+Enter, your formula will behave like a usual Excel formula and process only the first value(s) in the specified array(s). Therefore, always check whether the array exists or not.
Press 'F9' key in formula bar to evaluate portions of an array formula
While working with array formulas in Excel, you can
observe how they calculate and store their items (internal arrays) to display
the final result you see in a cell. To do this, select one or several arguments
within a function's parentheses (first bracket), and then press the F9 key. To
exit the formula evaluation mode, press the Esc key.
So lets' use array.
Suppose in the given Excel Table I need to derive the data which is maximum when we deduct Sales from Target, meaning need the gap of Sales & Target. So what we will do is we'll first type the function MAX(), this function's arguments are number 1, number 2 etc., so write =MAX(Select the Target Range, put a minus sign, Select the Sales Range). After putting the closing bracket of MAX function press Ctrl+Shift+Enter simultaneously. Instantly you'll see that this function is enclosed by two curly braces {}, the formula now converted to array function {=MAX(D2:D14-E2:E14)} and the result is 204, as shown in the image. If you don't press Ctrl+Shift+Enter, the result will be 50.
 |
| How to use MAX function with ARRAY |
Example 1. A single-cell array formula
Already discussed about it. An Excel array formula does not need an additional
column since it perfectly stores intermediate results in memory. So, you just
enter the following formula and press Ctrl + Shift + Enter as shown above.
=MAX(D2:D14-E2:E14
Example 2. Using an Excel array function to return
a multi-cell array
Excel array formulas can return a result in a
single cell or in multiple cells. An array formula entered in a range of cells
is called a multi-cell formula. An array formula residing in a single cell is
called a single-cell formula.
There exist a few Excel array functions that are
designed to return multi-cell arrays, for example TRANSPOSE, TREND, FREQUENCY, etc.
Other functions, such as SUM, AVERAGE, AGGREGATE,
MAX, MIN, can calculate array expressions when entered into a single cell by
using Ctrl + Shift + Enter.
As already mentioned, Microsoft Excel provides few "array functions" that are specially designed to work
with multi-cell arrays. Excel TRANSPOSE is one of such functions and we are
going to utilize it to transpose the above table, i.e. convert rows to columns. (Already discussed in my previous blog post)
Select an empty range of cells where you want to
output the transposed table. Since we are converting rows to columns, be sure
to select the same number of rows and columns as your source table has columns
and rows, respectively.
Enter the array function =TRANSPOSE(array) and press
Ctrl + Shift + Enter.
This will reverse the arrangement as shown in the below image.
 |
| TRANSPOSE and ARRAY |
Example 2 : Another Example in SUM() and LARGE()
In the below example I need the sum of top three numbers in Sales (E) column. The steps are type =sum(large(Select the range,now you can manually enter the curly braces and write 1, 2, 3 it will work or alternatively you can use Transpose() and press F9 in the formula bar and then write the LARGE() and SUM() functions respectively.
To visualize the working of ARRAY, please visit my YouTube Channel and view the array function.
 |
| How to use SUM and LARGE function in Microsoft Excel Array |
So, friends like these examples you can frame any formula you wish, ARRAY is such a tool provided by Microsoft Excel that can finish your job which might have taken hours. Please visit my YouTube Channel and view those are my Blog's supportive video.
Click to Watch YouTube Video about Array
Thank you...
Click to Watch YouTube Video about Array
Thank you...
No comments:
Post a Comment