# Understanding Excel array formulas & a simple example

Hello,

I'm trying to combine two simple Excel (2013) formulas into a single array formula.

I also want to obtain a better overall understanding of Excel array formulas because I have a hunch there's more opportunities than I realize when using an array formula would be simpler and save time.

Another significant reason — which I acquired just this evening — comes from one of the sites I found while googling this topic. It says:
"To become an Excel power user, you need to know how to use array formulas…"
I definitely want to be an "Excel power user" because, well...I mean, it just sounds cool!   [Sorry, couldn't resist.]  :)

1st question
I came across the following definition:
"An array formula is a formula that can perform multiple calculations on one or more of the items in an array."
Is that an accurate and complete definition or would you modify it in some way?

2nd question
Suppose you have a spreadsheet with entries in the range B5:B16 as shown in the following screenshot (Fig. 1). And suppose you want a formula to reside in cell B2 which indicates the number of cells in that range which have entries:

The only method I currently know for doing that requires two steps:

Step 1 is to add a helper column (column C in Fig. 2) which contains a formula to ask whether or not content is present in each row.

Step 2 is to use the function COUNTIF() (cell C18 in Fig. 3) to display how many cells in the helper column indicate that an entry is present in the adjacent column B range:

Is this the kind of situation where an array formula in cell B2 could display the solution without requiring a helper column?

If so, what is the formula and how do you combine the two non-array formulas into it?

Thanks
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EngineerCommented:
"An array formula is a formula that can perform multiple calculations on one or more of the items in an array."
Is that an accurate and complete definition or would you modify it in some way?
Looks good

The only method I currently know for doing that requires two steps:
for this particular example I would use counta() function

Is this the kind of situation where an array formula in cell B2 could display the solution without requiring a helper column?
pretty much

If so, what is the formula and how do you combine the two non-array formulas into it?

=if(B5:B16<>"",1,0)

will return an array containing 1s and 0s. Summing this will give the number

=sum(if(B5:B16<>"",1,0))
Commented:
You only need COUNTA function to count the cells with content.

=COUNTA(B5:B16)

Regarding array formula see these links

1. http://www.decisionmodels.com/optspeedj.htm
2. http://www.cpearson.com/excel/arrayformulas.aspx

Kris
Microsoft Excel ExpertCommented:
Hi,

if you really want to Master Array Formulas then i personally think there is no better Tutorial other than ExcelIsFun of Mike

Definition of Array formulas by Mike Girvin

Array Formula Notes:
1)      Definition of array formula:
i.      An array formula is a formula that contains an operation (math, comparative, join, or function argument) on an array of items rather than on single items, and, the operation delivers a resultant array of items rather than a single item.
ii.      This operation is called an array operation and is distinguished from an aggregate operation, which delivers a single item.
iii.      The resultant array of items can be used as a formula element in a larger formulas, or it can be the final answer.
iv.      The final answer from an array formula can either be a single item or an array of items.
2)      Of the 450 Excel functions, only these contain arguments that can handle array operations without Ctrl + Shift + Enter:
i.      The array_1, array_2, etc. arguments in SUMPRODUCT
ii.      The lookup_vector argument in the LOOKUP function
iii.      The result_vector argument in the LOOKUP function
iv.      The array argument in INDEX
v.      The array argument in AGGREGATE for functions 14 to 19
3)      These function arguments can NEVER perform array operations:
i.      lookup_value argument in VLOOKUP
ii.      lookup_value argument in HLOOKUP
iii.      range and sum_range arguments in SUMIF
iv.      range argument in COUNTIF
v.      range and average_range arguments in AVERAGEIF
vi.      criteria_range and sum_range arguments in SUMIFS
vii.      criteria_range argument in COUNTIFS
viii.      criteria_range and average_range arguments in AVEARGEIFS
4)      Function arguments that cannot automatically handle array operations require this special key stroke:
i.      Ctrl + Shift + Enter
1.      Ctrl + Shift + Enter is you telling Excel that you want the formula or function to make an array operation.
2.      After you enter the array formula with the keystrokes Ctrl + Shift + Enter, Excel let’s you know that it understood by placing curly brackets at the beginning and end of the formulas. You can see the curly brackets in the Formula Bar.
3.      If you forget to use Ctrl + Shift + Enter one of two things will happen:
1.      If the formula is not next to the data set (formula inputs), you will get a #VALUE! error
2.      If the formula is next to the data set (formula inputs), you will get an answer from implicit intersection. The answer is almost always wrong.

5)      Array Functions are a special group of functions that operate on arrays and produce and resultant array of values that can be entered into a range of cells using the keystrokes Ctrl + Shift + Enter.
i.      After you enter an Array Function with Ctrl + Shift + Enter, you cannot delete the contents of just one cell. If you want to delete the contents of a cell, you must delete all the contents of all the cells in the array.
ii.      Array Functions in Excel:
1.      TRANSPOSE
1.      Converts a vertical array or range into a horizontal array or range or vice versa. Works on one-way or two-way arrays or ranges.
2.      MODE.MULT (new in Excel 2010)
1.      Calculates mode (statistics). Finds the number that occurs most frequently when there are multiple such values (multiple modes).
3.      TREND
1.      Using the least-squares method for best-fitting data to a straight line, returns an array of y values, given these formula inputs: known y values, known x values, and an array of x values used to estimate the array of y values. TREND is different from FORECAST in that it can return an array of x values.
4.      FREQUENCY
1.      Counts how many values are in each category, given the upper values for each category.
5.      LINEST
1.      An amazing function that simultaneously returns two or more statistics for single or multiple regression, using the least-squares method for best-fitting data to a straight line.
6.      MMULT
1.      Returns the matrix product of two arrays.
7.      MUNIT (new in Excel 2013)
1.      Returns the unit matrix, given a single number.
8.      MINVERSE
1.      Returns the inverse matrix, given a matrix.

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Finance AnalystCommented:
A couple of examples that I have used in the past demonstrate reasonably well how arrays work.

1) Max length of an item:  using your example of multiple entries in a range lets suppose you want to find the longest entry. Multiple formula method would put a formula next to each entry =LEN(B5) and copied down and then you can use the MAX formula to find the highest value of the range of LEN formulas: =MAX(C5:C16)
As an array formula you can do this in one with =MAX(LEN(B5:B16))  confirmed with Ctrl, Shift & Enter (CSE)

2) Sum of numbers in text format: lets suppose we have a download from a website or other report whereby a column of numbers has come through as text and we want to sum this range. Excel will recognise a text number only if you perform some maths on it, simplest would be to multiply by 1 or to add zero. Assuming same range as above, to perform this we would create another cell next to the range with formula =B5*1, copied down against range and then we would sum that column instead of the original.
As an array formula you can do this with =SUM(B5:B16*1)  ##note *1 is within brackets of SUM formula## confirmed with CSE.

In each example, the array setting is telling the formula to perform the inner function  (example 1 - LEN(), example 2 - *1) on each entry in the range specified before performing the outer function (1 - MAX & 2 - SUM) on the resulting group of values.

These are obviously very simplistic examples of array formulas but hopefully give an idea of how they work.

You can also introduce IF statements within the formula to specify criteria for items in the range.

Thanks
Rob H
Author Commented:
Thank you very much. Every response is extremely helpful.
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.