Solved

Understanding Excel array formulas & a simple example

Posted on 2015-01-13
5
246 Views
Last Modified: 2015-01-16
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:

Fig. 1The 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.

Fig. 2Step 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:

Fig. 3Is 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
Comment
Question by:WeThotUWasAToad
5 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 100 total points
Comment Utility
"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

by:krishnakrkc
krishnakrkc earned 100 total points
Comment Utility
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 25

Accepted Solution

by:
ProfessorJimJam earned 200 total points
Comment Utility
Hi,

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.
0
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 100 total points
Comment Utility
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

by:WeThotUWasAToad
Comment Utility
Thank you very much. Every response is extremely helpful.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now