Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Solved

Posted on 2015-01-13

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:*"Excel *__power__ user" because, well...I mean, it just sounds cool! [Sorry, couldn't resist.] :)

**1st question**

I came across the following definition:

**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

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

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."

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

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

Thanks

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
- Learn & ask questions

5 Comments

"An array formula is a formula that can perform multiple calculations on one or more of the items in an array."Looks good

Is that an accurate and complete definition or would you modify it in some way?

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))

=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

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

Play List https://www.youtube.com/playlist?list=PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci

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.

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

Question has a verified solution.

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

Course of the Month5 days, 16 hours left to enroll

Join the community of 500,000 technology professionals and ask your questions.