CTRL+SHIFT+ENTER MASTERING EXCEL ARRAY FORMULAS PDF

adminComment(0)
    Contents:

You can get them from the "Ctrl+Shift+Enter: Mastering Excel Array Formulas" section of this website: llowponquoresmai.gq At this site. Ctrl+Shift+Enter: Mastering Excel Array Formulas. © Holy Macro! Books and Mike ISBN: (pdf). ISBN: (epub). Ctrl+Shift+Enter Array Formula PDF e-Book. Master Excel Array Formulas. The ultimate guide to doing the impossible in Excel with array formulas. Starts from.


Ctrl+shift+enter Mastering Excel Array Formulas Pdf

Author:SHENA HOEGERL
Language:English, Portuguese, Dutch
Country:Spain
Genre:Technology
Pages:645
Published (Last):24.02.2016
ISBN:770-8-60712-359-5
ePub File Size:28.43 MB
PDF File Size:20.72 MB
Distribution:Free* [*Sign up for free]
Downloads:48469
Uploaded by: GLORY

Ctrl+Shift+Enter: Mastering Excel Array Formulas by Mike Girvin, Bill Jelen Download eBook Ctrl+Shift+Enter: Mastering Excel Array Formulas Mike Girvin, Bill. Books; Print ISBN: ; PDF ISBN: ; ePub ISBN: The DVD 'Ctrl+Shift+Enter: Mastering Excel Array Formulas' is a separate. Designed with Excel gurus in mind, this handbook outlines how to create formulas that can be used to solve everyday problems with a series of data values that.

Mastering Excel Array Formulas ' is a separate product from this book. The two products cover the same broad topics related to creating efficient array formulas. However, the video has kinetic visuals of how array formulas work and less detail than the book. The book has a more complete story and systematic presentation of array formulas.

If you really want to master array formulas, study both the book and the DVD. We will ship you a physical copy of the book, plus you can immediately download the e-book in any of three DRM-Free versions: PDF, ePub, or Mobi. Outside of the USA, download the e-book. Again, you can choose from any of 3 DRM-Free editions when you download from:. Toggle navigation MrExcel Home. Product Details. Holy Macro! Table of Contents. Introduction Chapter 1: Formula Basics Chapter 2: Introduction to Array Formulas Chapter 3: Math Array Operations Chapter 4: Join Array Operations Chapter 6: Function Argument Array Operations Chapter 7: Array Constants Chapter 8: A First Look at Array Functions: With the formula in Edit mode, the best way to enact this keystroke is to hold down Ctrl and Shift and then press the Enter key.

You can see that the MAX function correctly selects the value 7 from among the values 3, -8, 7, and 6. Notice the curly braces in the formula. In terms of entering an array formula into a cell, so far you have learned the following: If you try to type in the curly braces, they will appear as text, not as part of an array formula. But this is not the whole story. C6 entered into eight cells. The formulas entered next to the Open and Close columns of data show numeric answers from a process called implicit intersection.

All these answers are incorrect except for the one cell that corresponds to the opening and closing numbers that calculate the correct maximum stock change.

This phenomenon happens on the left or right of a vertical data set and above or below a horizontal data set. To avoid the pitfall of implicit intersection, it is always best to immediately check the formula bar to see if the curly braces are in place. Only the formula in cell B8 is correct. To complete our discussions about how to enter an array formula into a cell, you need to understand how these types of functions handle array operations.

E6 and a helper column. This method is good if you want to see all the detail and you can afford the spreadsheet space. What do you notice? There are no curly braces!

Array Formula Efficiency Rule 8 We can formalize the rules for entering an array formula into a cell like this: If you try to type in these curly braces, they will appear as text, not as part of an array formula.

Which is preferred? The answer depends on your goal and the situational demands. If you want to see all the detail and you can afford the spreadsheet space, the helper column method is preferred. Later chapters also consider formula calculation time as a factor in whether to use a single-cell method or a helper column. Array Formula Efficiency Rule 9 Points to think about if you are trying to decide between a single-cell formula or a helper column that contains a multistep solution: Advantages and Disadvantages of Array Formulas As you progress through this book, you will consider the advantages and disadvantages of array formulas.

You will also consider alternatives to each array formula that you consider. The following table lists the most important advantages and disadvantages of array formulas. To determine whether other methods would be more efficient than using array formulas, consider the following these topics will be covered through the book: For spreadsheets that contain many formulas with large numbers of cell references and calculations, you might need to time different formula options to see if one is significantly faster.

This option is beyond the scope of this book. Calculation Time for Array Formulas and Large Data Sets The most significant disadvantage of array formulas is that they can slow down calculation time. Most of the examples in this book are based on small data sets. A small data set makes it easy to see how a formula is put together and how it works.

But a small data set is likely to calculate much more quickly than a large one. And the working world is full of large data sets. Array formulas make calculations on arrays. If your arrays are large, the time it takes to perform the array calculation will increase. If you are using array formulas on larger data sets, the formulas can contain large ranges of cells and perhaps many array calculations.

The more cell references and calculations there are in a formula, the longer it takes for the formula to calculate. Array formulas are by their nature calculation intensive. It is possible to set calculations to Manual and then recalculate by clicking the Calculate Now button in the Calculation group of the Formula Ribbon tab.

Given these considerations, if you have a workbook with a large data set that uses array formulas and in which the data changes often, you have the potential for a workbook that calculates slowly.

Therefore, when you are considering which array formula to use from among a number of options, it can be advantageous to select the formula that calculates most quickly. Throughout the examples in this book, you will consider calculation time as a criterion for choosing the most efficient formula.

In this written book, I simply report the results from timing different formulas rather than go through how to time formulas. However, if you are interested in timing your own formulas, check out this amazing article by Excel MVP Charles Williams: He discusses speeding up formula calculation time and includes free VBA code that you can download and use to time your formulas.

Actually, Charles Williams is known as "Fast Excel" because he is a world leader in making spreadsheets go fast! Find him at www. This is especially helpful because although in this book you will learn about some general rules for types of formulas that calculate quickly, your individual situation will vary, given factors such as size of data, data type, and computer calculation speed.

Chapter Summary In this chapter you have learned the basics of array formulas, including what they are, how to create them, and how to enter them into a cell. The array operation you saw in this chapter is a math operator. Chapter 3 looks at array operations with different operators, such as comparative, join, and function argument operations. Chapter 3: Math Array Operations Excel Files To follow along with the examples in this chapter, you can download the accompanying files, as explained in the Introduction.

Array Operations Now that you know that array formulas perform operations on arrays, you can consider the different types of array operations that you might see. This chapter looks at math array operations. Chapters 4, 5, and 6 look at comparative array operations, join array operations, and function argument array operations, respectively. Math Operations In Chapter 2 you saw an array formula that uses a math operator.

Figure 3. Notice that the array operation consists of an array, a subtraction operator, and an array. If you were to watch the array calculation in Figure 3. Each number in the second array is subtracted from the corresponding number in the first array: But what if you had an array of values and you wanted to subtract a single number from an array of numbers?

Is this possible? B5 and a net cost rate in cell B8. You want to calculate the individual net costs and then add them.

One way to do this is to calculate the individual net costs in a helper column and then use the SUM function to add the net cost amounts. But could you just do away with the column of calculations and instead create an array calculation in a single cell? Yes, you can, and Figure 3. Notice that cell B8 is multiplied by B2 to B5.

It is as if you were taking a whole column and multiplying it by B8. From this you can create the array calculation: The number 0. You just saw that you can subtract a single number from an array.

But what about subtracting an array from a single number? Would that be possible? As you have probably guessed, the answer is yes. Calculating Net Cost from Four Series Discounts in Four Separate Cells This example involves calculating the net cost for a product when you are given a series of discounts. In Figure 3. Calculate the complement for each discount—that is, 1 minus the discount.

See Figure 3. Multiply the complements to get the net cost equivalent. Multiply the net cost equivalent by the retail price. The problem with the setup in Figure 3. This is a perfect situation for a single- cell formula. Creating an array formula to accomplish the same end result should not be too difficult. However, it can be useful as the source idea for how to create your own single-cell array formula. Creating Your Own Array Formulas After you have created a multistep solution, a useful trick for creating your own single-cell array formulas is to start in the final result cell and move backward through the multistep solution.

Looking back to Figure 3. B5 ,2. Next, ask: Because you can see an array of discounts that are subtracted from the number 1, you create the array operation 1-A2: Replace the range B2: To do so, put 1-A2: B5 ,2 formula in place of the range B2: B5, like this: A5 ,2.

Placing a function inside another function is called nesting functions. Throughout this book, you will see quite a lot of this. Nesting is quite helpful because it means you can combine the power of two or more functions in a single formula. Array Formula Efficiency Rule 10 After you have created a multistep solution with different formulas in different cells, a useful trick for creating your own single-cell array formulas is to start in the final result cell and move backward through the multistep solution.

You can also do this in the opposite direction, starting in the first cell and working your way forward though the multistep solution.

A5 ,2 The following table presents a summary of the net cost array formula you just learned about. How You Can Structure Array Operation with an Operator Now that you have seen a number of array operations, think back to the definition of an array formula: Array Formula Efficiency Rule 11 An array operation that uses an operator can be structured in three possible ways: Throughout the rest of this book you will see many more examples of math array operations.

Chapter Summary In this chapter you have learned about math array operations and the structure of array calculations. In Chapter 4 you will take a look at array calculations that use comparative operators and how to make aggregate calculations with criteria. Chapter 4: Comparative Array Operations and Aggregate Calculations with One or More Conditions Excel Files To follow along with the examples in this chapter, you can download the accompanying files, as explained in the Introduction.

Comparative Operations Remember that Excel offers the following comparative operators: These functions make aggregate calculations based on one or more conditions or criteria. The problem is that these functions can only sum, count, and average. In these cases, because there is not a built-in function to accomplish the goal, you have to invent an array formula.

Often this involves using comparative operators on arrays. The first example in this chapter involves a conditional calculation using the MIN function. You want to find the smallest sample time for each city.

The condition for calculating will be city name. You can use the MIN function to find the minimum value from a column of numbers, but how do you isolate just the numbers for Oakland and then Seattle and then Tacoma as you copy a formula down the column?

Ctrl+shift+enter : mastering Excel array formulas

Figure 4. Calculate the minimum time for each city. As shown in Figure 4. But you cannot put all the time values from column B into the number1 argument of the MIN. You then type the comparative operator, the equal sign. Finally, you select the cell to the left of the formula, the relative cell reference with the city name.

It is useful to think of this array operation as a question that you are asking of the range A3: The question you are asking in this case is "Are any of the cities in the range equal to Oakland? You leave it empty. Notice that the IF function formula element is complete, and it sits in the number1 argument in the MIN function. In Figure 4. This is an amazing use of the IF function to filter out values you do not want and to get just the numbers you want.

You then type a close parenthesis for the MIN function. Notice that this is where the array calculation sits. More about this later. You have just made a beautiful array formula. However, you should look at what other options are available in Excel to accomplish the same goal.

You want to see the full context in which you are deciding to use an array formula. This way, you can chose which method will be most efficient for your situational demands. Next you will briefly look at database functions and PivotTables. Understanding Database Functions There is a group of 12 database functions also known as D-functions that can make calculations based on criteria.

These functions are great when you have a single calculation to make and the data set has field names column labels. Figures 4. The advantage of D-functions is that if you have only a single calculation to make, such as the minimum for Oakland and not for Seattle or Tacoma, then the DMIN function is easier to create than a MIN-IF array formula, and it calculates quickly.

For large data sets and formulas with large ranges of cells, this matters; more later in chapters 5 and The disadvantage of D-functions is that the criteria argument requires that you type the field name in a cell and type the criteria below that field name. This means two cells listed vertically, one on top of the other, must be typed for each calculation. This is a disadvantage if you need to copy a formula down a column as you did back in Figure 4.

In many cases, this setup would not be desirable. Sometimes formula calculation time for a very large data set is a problem. Here are the steps to create this data table solution: Create the formula in cell E3.

Type all city names in the range D6: In cell E5, create a formula that points to cell E3. Highlight the range D5: Enter D3 in the Column Input Cell text box. Chapter 21 covers data tables in more detail. If you have a single calculation to make and a proper data set with field names, using DMIN is the way to go.

It is easy to create and calculates quickly which is important for large array formulas with many ranges and calculations. Array Formula Efficiency Rule 12 Consider using D-functions as an alternative to array formulas if you have a proper data set with field names and you do not need to copy your formula down a column or across a row. The advantage of using D-functions is that they are easy to create and calculate formulas more quickly than most comparable array formulas.

Using a D- function in combination with a data table is a workaround if you want a formula that you can copy down a column or across a row. Then you can consider a PivotTable for aggregate calculations that have one or more criteria.

PivotTables If you do not need the immediate updatability of formulas, you should consider using PivotTables any time you are creating aggregate calculations with one or more criteria. Because PivotTables are quite easy to create, especially when there are many criteria. This section looks at a single-condition calculation. Here are the steps to create this PivotTable solution: Ensure that the data set has field names and that there are empty cells around the data set.

With a single cell in your data set, select PivotTable from the Table group of the Insert Ribbon tab. Drag the City field to Rows and the Times h field to Values. Formulas do update immediately when source data changes.

If the demands of a solution require immediate updatability, then formulas are a great answer. You simply right-click somewhere in the PivotTable and click Refresh.

If you have multiple criteria for a calculation, a PivotTable is easier to create because you can simply drag and drop a field to the row or column area. With an array formula, you have to add a whole new array calculation. You will see how to do this later in this chapter. If you want to change a function, in a PivotTable you simple right-click and point to Summarize Values By. With an array formula, you have to rebuild the formula.

Another consideration for whether to choose a PivotTable or an array formula is formatting and layout. The formatting and layout are more constrained with PivotTables than with formulas. Finally, note that a PivotTable has only 11 functions available, whereas formulas have more than functions available. Next you will look at conditional calculations that have more than one criterion.

The following are advantages of PivotTables: Easy to create, especially if there are multiple criteria, and easy to change.

These are some of the disadvantages of PivotTables: Do not update instantly refreshing is easy, though , only 11 functions, and formatting is limited. Array formulas have a number of advantages: But what if you have more than one condition? In cases where you need to select items from an array based on multiple criteria, you can nest multiple IF functions together in one formula.

If you have two criteria, you use two IF functions; if you have three criteria, you use three IF functions; and so on. The goal here is to calculate the maximum sales amount for each sales representative in the west region. Further, you want a formula you can copy down the column, and you want the formula to automatically update if the source data changes.

Calculate maximum sales for each employee in the west. The IF function will help you isolate only the values that meet your conditions. Normally, you type the values you want in this argument, but because you have a remaining condition, you must type the next IF function. Prior to Excel , you could nest up to seven IF functions; in Excel and later, you can have up to The rule of thumb for nesting IF functions is to keep typing the IFs until you have no more conditions.

You can tell that you have finished typing your parentheses because the last one is black. The two IFs filter the numbers based on two criteria. The above criteria example demonstrates AND criteria. How do the two IF functions do this? They look through each of the three arrays, and only when they finds a TRUE in the first array and a TRUE in the second array do they select the number from the third array.

The formula looks like this: Now that you are ready to enter the formula, you must notice where the array calculation is located in the larger formula. More about this later in this chapter. Since they are there, you can copy the formula down the column.

If you usually copy formulas by using the mouse and the fill handle, you can copy the formula as usual. Select cell F5, Copy. Re- select F6: F7 and paste. If the paste area includes F5, Excel will deliver the cryptic "You cannot change part of an array" message.

With the formula done, you can think about the larger Excel context: Are there other ways you could do this? Because in this situation you need to copy the formula down a column and you want the formula to update instantly when the source data changes, the MAX with two IF functions is a good choice.

The data set has the three columns: Track the different tracks in different cities , BMX Racer the name of a racer , and Times s the time to make it around the track, in seconds.

Your goal is to calculate the fastest time for each racer. However, because the PI track is very short and the other tracks are much longer, you want to exclude the PI times from the calculation. This means you have a MIN calculation with two conditions: BMX racer name and "not PI".

Calculate the fastest time for each racer for races not at the PI track. Based on what you learned in the last array formula example, you could use the MIN function with two IFs.

The formula goal is the same as in our last example: You use the top set of function arguments that shows the argument name array as its third argument.

See the following table for a full list. Functions 1 to 13 cannot handle array operations. Functions 14 to 19 can handle array operations. The second argument allows options. Option 6 will help your calculation because it will help you ignore the error values that the array operation will create when the two criteria are not met. This is the argument that can do array magic! At this point, it is worthwhile to consider whether you can use the following formula element to create the array of values that match the two criteria: The following section formalizes this aspect of the IF function with a new rule.

To prove this to yourself, you can enter this formula: If you rearrange the two conditions and the range of numbers, you go from this formula element: With the array calculation for AGGREGATE, the numbers come first in the numerator , and then you use division, and in the denominator you place the two conditional array calculations. In addition, rather than use two IF functions to determine whether both conditions are met, you use multiplication.

Multiplying two arrays of logical values is called Boolean multiplication, and only when there is a corresponding TRUE in the first array and a corresponding TRUE in the second array is the number from the numerator selected. The multiplication enforces an AND criterion where both conditions must be true. You will see this many more times in this book and learn more about it later, in Chapter The resultant array contains only the numbers that meet both conditions AND criteria.

As with the two-IF construction, you have created an array of filter values! Nevertheless, Excel offers different options, and when formulas are close like these are, either one is fine. Always keeping the larger context in mind, Figures 4. What if you had to calculate standard deviation a statistic measure of variation in the data set? In this case, if you wanted to calculate the sample standard deviation with a formula that could be copied down a column, you could choose to use the IF function inside the STDEV.

In this example, Richmond has the most reliable average. Filtering Values Inside a Formula Throughout this chapter you have used the IF function or Boolean math to filter the values inside the formula. This is an important concept to have in your Excel toolbox. This is an example of filtering values using the IF function: This is an example of filtering values using Boolean math: In general, faster calculation times are more efficient. As you move forward through this book, you will occasionally time the different formulas to determine which formulas are faster.

The anecdotal results that I got are listed below. To time your own formulas, look back to Chapter 2 for details. Timing depends on many factors, and your particular situation may have different timing results.

The Introduction of this book explains how to get these files. Throughout the rest of this book, you will see many more examples of comparative array operations. Chapter 5 takes a look at the join array operator and a lot more.

Chapter 5: Join Array Operations Excel Files To follow along with the examples in this chapter, you can download the accompanying files, as explained in the Introduction. Join Operation So far in the book you have seen math and comparative array operations. This chapter takes a look at an example of a join array operation. The join operator is the ampersand: It allows you to join two items so they can become one. You can join things like numbers, text in double quotes, formula results, and more.

You will see how to use the ampersand to make an array operation in the following "two-value lookup" example. C16 and your desired cross-tabulated report in the range E2: The cross-tabulated report must show the Right and Left quantity for each product code.

To accomplish this with a formula, you need to create a single formula that you can enter into cell F4 and then copy through the range F4: The problem is that for each cell in the range F4: G10, you have two lookup values! For example, in cell F4, the lookup formula retrieved the quantity 30 based on the lookup value 2AA36 from cell E4 and the second lookup value L in cell F3. One way to accomplish a two-value lookup is to join the two lookup values and the two columns inside the lookup formula.

Figure 5. Create a cross-tabulated table based on two lookup values. As shown in Figure 5. The array argument contains the values you want to retrieve.

Looking back at Figure 5. By joining the two values, you create a single value. Since you have two columns, you join them in the same way that you joined the lookup values. In the process of joining the two columns, you have made a "join array operation. To see that you have taken two columns and joined them to create a single column, as shown in Figure 5. Right in the formula, you have made two columns into one.

Two columns have become one! If you need a single-cell formula that can do a two-value lookup, this is a good one. An alternative method for joining involves using an extra character between the two joined cells or columns.

The idea is that this is a "safe" way to join items so that if you ever create a joined item that is an actual item in the data set, there will be no problem. For example, if you joined 30 and 20 and the item were in the data set, you might have a problem.

In the data set in this example, you did not run this risk, so you did not use an extra character. You can extend this join array operation to include three or more lookup values. Considering other means to do a lookup on two or more values at once is important. Next, you will consider using the DGET function. If you have a proper data set that is, with field names at the top of each column , DGET can do a lookup based on multiple criteria. D-functions can handle AND criteria that is, all criteria must be met or OR criteria that is, at least one criteria must be met.

AND criteria must be in the same row as shown in Figure 5. As mentioned earlier in the book, the big drawback to using D-functions is that it is hard to copy them down a column because the criteria argument requires field names with criteria listed below.

If you need to copy the formula to other cells and you do not want to use the join array operation formula, you could use DGET with the data table trick shown back in Figure 4. You will see how using a helper column works next. DGET cannot be easily copied down a column.

If you can afford the spreadsheet space for the extra column, this setup is usually easier to create and will calculate more quickly than using an array formula. Using a PivotTable for Two-Value Lookup Finally, if you do not need the immediate updateability of formulas, you can use a PivotTable to create a cross-tabulated report.

Usually a PivotTable is for aggregate calculations with one or more criteria, but because you have no duplicate records in this table, you can use a PivotTable to display the retrieved values.

The following section looks at how sorting columns can be helpful for formulas that do a two-value lookup. Sorting by Two Columns and Using an Approximate Match When doing a two-value lookup, if you could sort the two columns in the data set, you could use an approximate match lookup rather than exact match for the join array operation formula or a helper column solution. An approximate match calculates much more quickly than an exact match because an exact match looks through the entire column, top to bottom, whereas an approximate match does a binary search, successively dividing the table in half as it searches.

The next section looks at what sorting does for the helper column solution shown in Figure 5. Here is what the formula would look like: Because an approximate match is the default, you can simply leave the fourth argument empty.

For large data sets, switching from an exact match lookup to an approximate match lookup can significantly reduce formula calculation time. The INDEX function normally looks up a single item, either in a two-way array rows and columns or in a one-way array row or column.

In this case, you want to look up an entire column. The next four figures show this array formula. The close-up in Figure 5. There are no curly braces in the formula bar. At the beginning of this section, I said this would be a silly array formula. One last note: This section shows the anecdotal results I got. To see the workbook with the timing results that I got, open the file named 02Time2ValueLookup.

The Introduction to this book explains how to get these files. Throughout the rest of this book, you will see a few more examples of join array operations. Chapter Summary This chapter discusses using join array operations, two-value lookups, the DGET function, PivotTables, helper columns, and sorted columns to speed up formula calculation time.

Chapter 6 covers function argument array operations. Chapter 6: Function Argument Array Operations Excel Files To follow along with the examples in this chapter, you can download the accompanying files, as explained in the Introduction.

Function Argument Operation So far in this book, you have seen the array operations: This chapter takes a look at a function argument array operation. Before you can see how a function argument array operation works, you need to think about how a function argument that is expecting a single item operates.

Figure 6. This function counts how many characters are in a cell. The text argument for the LEN function expects a single item. Because of this, the LEN function delivers a single answer. There are 15 characters in cell A3. Remember this rule for function arguments that expect a single item: But what would happen if you gave the text argument in the LEN function more than one item?

The next example illustrates this. Your goal here is to count how many total characters are in the entire column; that is, you want to count how many characters are in the first name, the second name, and so on.

You can start the formula by entering the entire range A2: A6 into the text argument of the LEN function. A6 by highlighting it and pressing F9 , you see that the text argument for LEN is being given five different text strings.

A text string just means a sequence of characters zero or more characters; later you will learn about null text strings that Excel considers text and not a number or logical. This means that if you give the LEN function text argument more than one item, it will give you more than one answer. More specifically, when you give the text argument five items, the LEN function gives you a resultant array with five answers.

Array Formula Efficiency Rule 17 Remember the following about function argument array operations: Specifically, if you give the function argument n items, the function will deliver a resultant array with n answers.

Find a copy online

If the LEN function is delivering an array of numbers, what are you going to do with the numbers? In this case your goal is to add, so you can place the formula element LEN A2: You can see that there are no curly braces in the formula bar.

Lookup Addition: C7 that lists the sales for each product sold; it does not list the costs. Is there a single-cell formula you can create that will look up the costs for each transaction and then add them? You therefore have to look at this problem from a different angle. Here you give it five separate items.

By placing five items in the criteria argument, you make a function argument array operation, and as a result, the SUMIF function will deliver a resultant array with five answers. As shown in Figure 6. One important reason that SUMIF works as a sort of lookup function is because there are no duplicates in the range F4: If there were duplicates, this formula would not work the criteria argument can have duplicates, but not the range argument cannot.

However, if the cell range in the range argument is sorted ascending, you can use a slightly easier and faster-calculating formula. In Figure 6. Timing the Formulas in This Chapter As discussed in Chapter 2, pointing array formulas to large data sets can increase formula calculation time for a workbook.

To see the workbook with the timing results I got, open the file named 03LookupAdding. Throughout the rest of this book, you will see many more examples of function argument array operations. Chapter Summary This chapter discusses using function argument array operations to create a formula that can count all the characters in a column and two different formulas for doing lookup addition.

In Chapter 7 you will learn about array constants. Chapter 7: Array Constants Excel Files To follow along with the examples in this chapter, you can download the accompanying files, as explained in the Introduction.

In fact, every time you have evaluated a formula element by using the F9 key, you have seen an example of an array constant. For example, in the first array formula you created in Chapter 2 shown again in Figure 7.

But all you did back in Chapter 2 and in many subsequent examples was to look at the resultant array to make sure the formula was calculating correctly. If you had not used undo you would have created an array constant that hard codes the values into the formula. In this chapter, you will learn about situations where there is an advantage to keeping a hard-coded array constant in a formula.

Figure 7. The array of items is housed in curly braces; the text is always shown in double quotes; the semicolon means to go down a row; and the column of items uses semicolons. A column filled with rows. The array of items is housed in curly braces; the text is shown in double quotes, and numbers are just numbers; a comma means to go over a column; and the row of items uses commas.

A row filled with columns. The array of items is housed in curly braces; the text is shown in double quotes, and numbers are just numbers; and the table of items uses semicolons to mean go down a row and commas to mean go over a column. A table filled with columns and rows. Array Syntax Rules From the preceding text, you can glean the following array syntax rules: The way I remember the difference between semicolon and comma is that column starts with the letter c, and so does comma.

In this way, I always remember that column goes with comma, so row goes with semicolon. If data will not change, you can hard code it into formula. The next example looks at a situation where you need to add the three smallest values. The goal here is to add the three lowest scores without including any extra values if there is a tie for third which there is, in this example. The SMALL function is a great function to use to solve this problem because with the k argument you can specify which small value you want.

In Figure 7. The problem with this formula is that it can take a bit of time to type out, especially if you want the 10 lowest values. To create a more compact formula you can make a function argument array calculation.

You can use an array constant to hard code the values into the formula because the values will not change. As shown in Figure 7. I learned about it from the MrExcel Message Board and many years of trial and error.

It does not matter whether you give the k argument a row array or a column array because in either case, SMALL simply delivers three numbers to the SUM function. Either one accomplishes the goal. In this event, you get five throws, and the three longest times are added to get your final score.

The formulas, as shown in Figure 7. In the last few examples you added the top three or lowest three values. What if you wanted the formula to add the top three but other times you wanted to add the top two or five? Is it possible to create a variable-length array constant? Formula [1] excludes ties, and formula [2] includes ties. The difference between these formulas and the earlier formulas is that the number of values to add will not be hard coded into the formula, but rather, you want to refer to a cell for this information.

If you change the numbers in cells D3 or D6, the total amount will change. You do not want to hard code values into the formulas Figure 7. Formula [2] is easy enough to understand, but the k argument for the LARGE function in formula [1] contains a formula element that you have not seen before. The next section takes a look this formula element. The joined formula element "1: If you evaluate the formula element "1: Formulas should be no longer than characters.

Notice that because these are rows, the array syntax shows semicolons. As noted earlier in this chapter, the array of sequential numbers used for adding the top n values or the smallest n values can be either a row or column array.

You gave it a reference with three rows; therefore the ROW function creates a resultant array with three answers. This formula element is dynamic and will change. You will see another example of this useful formula element in Chapter Recalculation can be triggered by simple actions such as entering an item into a cell or inserting a new row.

Volatile functions therefore increase formula calculation time. Excel Table Feature for Adding the Top Three Values Before you move on to the next array formula, you need to take a look at the Excel table feature for adding the top three values.

However, it will include duplicates if there is a tie. Back in Chapter 3 you saw a formula to calculate the net cost for a product, given a series discount. The example in Chapter 3 shows the four separate discounts in four separate cells. Here are the calculations that the single-cell formula needs to make: The part of the formula that involves an array operation is the part where you simultaneously extract four separate numbers at one time.

The MID function extracts text from a text string, given a starting character position in the text string and the number of characters you would like to extract. If you evaluate the MID function by pressing F9, you can see that MID does in fact deliver a resultant array with the four extracted discounts see Figure 7. Notice that the MID function delivers text items.

This is not a problem because the next step involves a math operation, and any math operation performed on numbers stored as text converts them back to numbers. After undoing the evaluation, the next step is to divide the numbers by and then subtract the results from the number 1, as shown in Figure 7.

Remember that the division will be performed before the subtraction, so you do not need to isolate the division with unnecessary parentheses.

If you evaluate the whole formula by pressing F9, you can see that it delivers the complements of each discount see Figure 7. After you undo the evaluation, the next step is to multiply the complements to get the net cost equivalent and finish the formula. If you do not want the table to sit in the cells and the data is not going to change, you could hard code the table into the formula. Luckily, there is a great trick that allows you to instantly create an array constant if you already have the table in the cells.

You can then copy the formula down the column and delete the lookup table. Another option is to save the array constant as a defined name. Using Defined Names to Store Array Constants If you want to save space by using an array constant in a formula, you can save the array constant as a defined name and then use the defined name in your formula.

You use this dialog to create the defined name for the array constant. Can it handle an array? Read on. The goal of the formula is to simultaneously look up columns 2, 4, 5, 7, and 8 to retrieve the costs and then add them.

This is a good formula for looking up and adding multiple columns for a single lookup value.

Ctrl + Shift + Enter

Efficiency rule 18 will give you a starting list of these arguments. Notice that how array formulas work is largely based on how the individual function arguments are programmed to work. Math and Comparative Array Operations Using Array Constants So far in this chapter, you have used array constants in function argument array operations. Figures 7. As shown in Figures 7. The formula in Figure 7.

I am not sure where you would use a formula like this, but I include it to illustrate the parameters of how array constants work. Comparing Solutions for Adding the Smallest or Largest n Values The following table presents a summary of advantages and disadvantages of the solutions for adding the smallest or largest n values.

To see the workbook with the timing results I got, open the file named 04AddTop5. Throughout the rest of this book you will see a few more examples of array constants in formulas. In Chapter 8 you will learn about a special kind of array formula that can simultaneously deliver multiple values. Chapter 8: Delivering a Single Result or an Array of Results with an Array Formula So far in this book, all the array operations have delivered a resultant array to a larger formula which then delivered a single array formula result to a single cell.

In this chapter you will learn how to create array operations that deliver a resultant array as the final array formula result. A6 and make it difficult for people to delete an individual cell in the range.

You will see how to create this array formula next. Figure 8. Creating an Array of Sequential Numbers: Because you have five items the numbers 1 to 5 , you highlight five cells, the range A2: A6, before you start creating the array formula. You create the formula in the active cell the light colored cell, in this example A2. You start the formula with ROW A2: The range, A2: Now you add 1 to get ROW A2: As shown in Figure 8.

With the formula complete, notice a few unique aspects of this type of array formula: If you try to delete a cell in the range A2: A6 or insert a row between 2 and 6, you get the error message shown in Figure 8. It is possible to delete the array formula from all the cells by highlighting the entire array formula and then pressing Delete. If you select any one of the cells in the range that contains the array formula and look in the formula bar, you will see the same formula in each cell.

Note that when you created the formula, you did not lock make absolute the references A2: A6 or A2. In Figure 8. It will show curly braces and will act as a single unit. In particular, it looks at why this formula is efficient and robust. This is important because this formula will show up as a formula element in more advanced array formulas. This formula element has five aspects: The formula ROW A2: The problem is that if you have many rows, typing out the array constant takes a long time.

The formula shown in Figure 8. Look at the formula ROW A2: Most formulas that refer to the cell the formula is housed in get a circular reference error. Not this formula. This is because the formula is not calculating a math type answer; it is just asking, "What row am I in? One reason the formula ROW A2: This means no matter where you move the formula, it will always calculate the correct array of numbers. The formula you have used so far creates sequential numbers for records in rows.

What if you wanted to do the same thing in a horizontal range, or across the columns?

In that case, you could not use the ROW function. So far the formulas in this chapter have had the goal of creating sequential numbers and making it difficult to delete the individual cells in the array. Is that really necessary, especially when you could highlight the entire array and delete it?

Probably not. If you are not concerned about the cells with the sequential numbers being deleted, there are a few other excellent methods for creating sequential numbering. The following sections look at five of them. Establishing a Pattern After typing any two numbers into two cells, you can grab the fill handle the tiny box in the lower-right corner of a selected cell with your cursor the crosshairs cursor, also known as the angry rabbit cursor and drag down or to the side, if the numbers are listed horizontally.

When you do this, you increment the numbers the based on the difference between the two numbers. If the numbers are 1 and 4, because the difference is 3, the next numbers in the sequence would be 7, 10, and so on see Figure 8.

Holding Down Ctrl While Moving the Cursor If you type a single number in a cell and then point to the fill handle with your cursor while holding the Ctrl key, you see a small plus symbol above and to the right of the crosshairs; when you see the plus symbol, click and drag down. This will increment numbers by 1. Highlighting a Number and a Blank Cell If you highlight a number and the blank cell next to it and then grab the fill handle with your cursor and drag down, you will increment the number by 1 see Figure 8.

This trick also works if you highlight a blank cell below the number and drag to the side. Using Fill Series Say that you want the numbers 0 to in a column.

Highlighting the numbers 0 and 1 and then dragging down rows would take a long time.Since we are converting rows to columns, be sure to select the same number of rows and columns as your source table has columns and rows, respectively. The GROWTH function will perform an exponential a curved line that rises or falls at increasingly higher rates growth calculation also to extrapolate multiple Y values.

Mike syncs perfectly with my style and will do so with anyone. The gantt chart excel template is therefore among the best ways to come up with a few really effective charts. Excel array formulas are the handiest tool to perform sophisticated calculations and do complex tasks.

Please enter recipient e-mail address es. You may send this item to up to five recipients. This book contains screen shots. Holy Macro! Array formulas can beat filtering, sorting and pivot tables because array formulas will automatically update when the input cells change.