Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Understanding Excel array formulas & a simple example

Posted on 2015-01-13
Medium Priority
287 Views
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
0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 43

Assisted Solution

Saqib Husain, Syed earned 400 total points
ID: 40548241
"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))
0

LVL 18

Assisted Solution

krishnakrkc earned 400 total points
ID: 40548245
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
0

LVL 27

Accepted Solution

ProfessorJimJam earned 800 total points
ID: 40548584
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.
0

LVL 33

Assisted Solution

Rob Henson earned 400 total points
ID: 40548586
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
0

Author Closing Comment

ID: 40554625
Thank you very much. Every response is extremely helpful.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month5 days, 16 hours left to enroll