WeThotUWasAToad
asked on
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:
1st question
I came across the following definition:
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 "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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER