Step 2: To determine the sales generated by John in selling PC products, the formula is. Step 4: Change the values of F2 and F3 cells to determine sales generated by other persons. Step 1: Enter matrix A into the Excel sheet as shown in the below-mentioned image.
Step 2: Highlight the range of cells to position the inverse matrix A -1 on the same sheet. Take care during inputting the formula that all cells are highlighted.
Here we discuss 3 ways to use Ctrl Shift-Enter in excel to determine the sum, inverse matrix, and sum using condition along with examples and downloadable excel template.
You may look at our following articles to learn more —. Submit Next Question. By signing up, you agree to our Terms of Use and Privacy Policy. Forgot Password? 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. As an alternative, you can use the Fill Series feature. This method is fast! Note: Alternatively, you could open the Series dialog box by right-clicking the fill handle and dragging down one cell and then back up one cell, releasing the right-click, and selecting Series from context menu.
These formulas use an expandable range, where the first cell reference in the range is locked but the second one is not locked. These two formula number incrementors show up in many of the array formulas to extract records or data in Chapter The ROWS function counts how many total rows there are in a range.
Because you use an expandable range, as you copy the formula, Excel increments the numbers by 1. The timing results are at the end of this chapter. Figures 8. The MOD function reports the remainder after dividing each number by 3. If you want to convert this formula to an array formula that delivers multiple values and help deter cell deletion, you can simply change the single cell in the MONTH function to a range of cells.
Division by 3 yields the quarter. Using Figures 8. The array constant is copied onto the Clipboard. As a result, this table deters cell deletion. To see the workbook with the timing results I got, open the file named 06SeqNumbers. In this particular application of these two formulas, creating sequential numbers in a column, the array formula has a calculating speed advantage.
However, unlike in this chapter, where they are alternatives for the same formula goal, in Chapter 15, they will accomplish entirely different goals within a single array formula. Stay tuned. Chapter Summary In this chapter you have learn about array formulas that can deliver more than one value to more than one cell and how to create formula number incrementors.
In Chapter 9 you will learn about a group of functions called array functions. Built-in Excel array functions You will learn about them in this chapter.
Both types of array formulas that deliver multiple items have the same rules and guidelines as shown in Chapter 8 see Array Formula Efficiency Rule As you learned there, you use these steps to enter this type of array formula into multiple cells: 1.
If the array formula will yield n items and you want to display the n items in n cells, you must select n cells before starting your formula. In addition, you must consider whether the array is a one-way or two-way array when entering the formula. Create your array formula in the active cell. The following table lists the eight Excel array functions. Figure 9. If the goal is to transpose the data that is, flip it 90 degrees , you can accomplish this in four steps: 1.
Count the number of columns 4 and rows 3 in the source data set. Highlight 3 columns and 4 rows switching the counts for rows and columns from original data —in the case, A7:C You see curly braces in formula bar. As with so many other things that go on in Excel functions, it comes down to how the individual function argument is programmed to behave.
It pays to read Excel function help. Back in Chapter 4, you learned how to use the IF function to filter an array of items within a formula. In this example, you want to use the IF function, but rather than filtering, you want to use the IF function to substitute a null text string a way to show "nothing" in a formula for the zero that Excel sees when it looks at an empty cell.
Figures 9. As shown in Figure 9. If you entered the range A1:D3, you would get a zero from cell A1. The word blank is often misused in Excel. For this reason, I always refer to empty cells as empty cells and null text strings as null text strings and avoid using the word blank altogether.
Notice that because the range A1:D3 contains columns and rows, the array syntax contains commas and semicolons. This single null text string is substituted at any location that contains an empty cell. Null Text Strings A null text string is a text string that has zero length. To create a null text string, you type two double quotes, one after the other, with no space between the first double quote and the second double quote.
When you use a null text string in a formula, it shows in the cell as nothing no text at all , even though it is considered a text string with zero length and is no longer considered an empty cell.
The finished transposed data set shows nothing in cell A1 instead of a zero, even though there is a null text string in cell A1. The mode calculation simply determines which number occurs most frequently in a list of numbers. Note: The mode calculation is usually used on nominal data that is, data that is not a number and has no inherent order as a type of average.
The goal is to figure out which time occurred most frequently. They both calculate exactly the same correct answer; they are duplicate functions, and you can use either one. In Figure 9. Therefore, 24 is the mode. SNGL functions both calculate a single mode. The MODE function in the drop-down has a yellow triangle icon next to it to indicate that this is a "compatibility function" that exists so that spreadsheets built in earlier versions will work in later versions.
SNGL calculate the same answer. Why the new MODE. The yellow triangle indicates that the function is a "compatibility function". The MODE. Sometimes there are multiple modes.
The time SNGL report only the first one that is encountered in the list and totally ignore the fact that there is more than one mode. When there are multiple modes, you can use the MODE. MULT array function. It makes perfect sense that this is an array function because if there are multiple modes, the function will be delivering multiple values. Because you know that there are exactly two modes, as shown in Figure 9.
But what if you did not know how many modes there were? Or what if the data were continually changing, with sometimes one mode and other times multiple modes? In either case, you would not know how many cells to highlight before creating the formula. No problem. You can put on your thinking cap and create a robust formula that will work regardless of how many modes there are. MULT can find all the modes. You simply highlight a bunch of cells and enter the MODE. MULT is delivering a resultant array of only two values.
MULT returns a vertical array of numbers. Another drawback to the "blind" method is that if the data were changing and you were to get a single mode, MODE. MULT would simply repeat the single mode many times, as shown in Figure 9. MULT will repeat it. A much better solution would be to have a formula that counts how many modes there are and then a second formula that displays the modes as you copy the formula down a column. MULT Figure 9. Although the MODE.
MULT reveals a vertical resultant array. Because you need to extract items from a vertical array as you copy the formula down across the rows, you need a formula number incrementor that will give you the sequential numbers 1, 2, 3, and so on.
This formula number incrementor instructs INDEX to extract the first mode row 1 , then the second mode row 2 , and so on as the formula is copied down the column.
MULT has created. You can amend the formula to show a null text string when you copy the formula down past the row with the last mode. The beauty of this formula is that if the data changes, the formula will accurately reflect whether there are no modes, one mode, two modes, or some other number of modes.
In the example here, you only copied the formula down five rows. If you might expect more than five modes, then you could copy the formula down as far as you need. For large data sets, this can significantly increase formula calculation time. In stark contrast, with the alternative formula shown in Figure 9. This significantly increases formula calculation time for large data sets see the end of this chapter for timing results.
Yes, there are. With the formula shown in Figure 9. Using PivotTable to find modes. If you have a lot of data and you want a quick and easy method for "eyeing the mode," using a PivotTable is a great alternative. You follow these steps to create this PivotTable: 1.
Click a cell in the source data column. Drag the Values field to Rows. Drag the Values field to Values. Change the function from Sum to Count by right-clicking a value and then selecting Summarize Values By followed by Count.
Comparing Calculating Modes Solutions The following table provides a summary of the advantages and disadvantages of the various solutions for calculating modes. See the timing results at the end of this chapter. You can have 1 to array arguments. When arrays are not the same dimensions and you need to multiply them, you can use the multiplication operator and place multiplied arrays in a single array argument.
You can house a single array operation in the array1 argument when you need to add the result of an array operation. Any math operation will convert them, but using the double negative is generally the fastest-calculating method. Timing examples are given later in this chapter.
Figure The goal of the formula is to calculate the total bank deposit. Figures Here, four columns are multiplied and then added to get total pressure.
If the two cells have numbers or zeros, there will be no error. In this case, you are estimating the expected return for a portfolio of stocks, based on the probability of economic states B3:B5 , weight for each stock C1:D1 , and estimated individual stock returns C3:D5.
If you did this longhand, it would be difficult: 0. By multiplying the ranges directly, you create an array operation. Chapter 18 shows a workaround for this problem. These are some of the reasons: 1.
This discussion assumes that you want a formula solution and not a PivotTable solution. The goal of the formula is to count and add the times that the employee Kip spent on Project 2.
This is AND criteria because both conditions must be met for you to include an item in the count or summation. As shown in Figure For large data sets, they can dramatically reduce calculation time.
They do not require field names in the data set or in the criteria area, as the D- functions do. They can be easily copied to other cells using the correct cell references , unlike D- functions. For more about copying problems, see chapter 4 and chapter Note: You can copy D-functions to other cells if you use them in combination with a data table. See Chapter 4 for details. When running Excel or earlier, Figure In this example, you are adding with two criteria and creating a cross-tabulated table, based on employee name and project.
You created a single formula in cell F3 and copied it through the range F3:G5. Take a closer look at the formula in cell F3.
Why in the world are there double negatives? The following section answers this question. As a result you get a count of zero. Most of the time, any of them are okay. And, in fact, by the end of this book, you will see good uses for all the math operators.
Numerous timing tests have been done throughout Excel history. See Chapter 2 for details about how to time your own formulas. Double Negative tends to be fastest. In Chapter 11 you will learn more about Boolean math.
This data set has 50, rows of data. Because the data set is so big, it is in a separate Excel file from the file with most of the figures in this book. The file name is ThreeCriteria. The goal of the formulas in Figure The next section looks at the timing results for each of these formulas.
For these reasons, it is often more efficient to use the SUMIFS function for adding with multiple criteria if you have Excel or later. These examples are in the file with most of the figures in this book, in the file named CtrlShiftEnterBookFinishedFile. In this example, you have a "between two dates" criteria, where A2 contains the criteria "greater than or equal to the lower date" and B2 contains the criteria "less than or equal to the upper date".
This is a useful technique if the cell formula input the hurdle value is also being used by other formulas. When you use a workbook reference and then close the workbook with the external data, the workbook reference converts to an array and causes the function to show a VALUE! However, as shown in Figure As shown in Figures Note: Creating workbook references in formulas is similar to adding a regular cell reference to a formula. Also, when the external workbook is open, the workbook reference shows the workbook name and sheet reference.
When it is closed, it shows the entire file path name. If your project requirements allow such a calculation, this is a great trick. Note: The publisher of this book, Bill MrExcel Jelen, specifically requested that this formula be included in the book because it is one of his favorite array formulas!
This seems like a nearly impossible task for a single-cell formula! What makes it particularly daunting is that you do not have the full list of dates to look through. Luckily, there is a formula element that you can construct that will create an array of sequential dates, given a start date and an end date.
In fact, in Chapter 7 you already saw a formula element that will create an array of sequential numbers. Chapter 7 calls the formula element a "dynamic variable-length array of sequential numbers. Chapter 7 explains the hows and whys of this formula element. Because you cannot find Friday the 13ths from serial numbers, you need to use the TEXT function to format the array of numbers so that they show the names of the day and the numbers—for example, "Fri 13".
To get the TEXT function to show the serial number dates in the text format you want, you must enter a custom number format to display the numbers with a three-letter abbreviation for the name of the day and the number of the day. The array contains logical values.
TRUE means the serial number date is a Friday the 13th! This makes logical sense, especially since most of the function arguments in Excel can handle array operations. I mean, all you need to do is count how many TRUEs there are in the array of logical values, right? In any version, the message does not provide helpful information. It should say something like this: "The range argument in the COUNTIF function is not programmed to handle arrays or array operations" because that is exactly what is going on here.
To avoid this potential ambiguity, it may be better to use the formulas shown in A10 and A With arrays of different dimensions, you can use the multiplication operator and a single array argument to get an answer. To be less ambiguous, consider using an alternative formula. Chapter 11 discusses Boolean logic. This chapter expands the discussion to also include OR criteria.
Boolean Data The word Boolean comes from the 19th century mathematician George Boole, who introduced Boolean algebra in I use the terms AND criteria and OR criteria because in Excel you often use these calculations to create reports, extract data, or make calculations based on a set of criteria. The key concept that you should have in your head as you go on to build bigger array formulas is that AND criteria and OR criteria are filters you put on the data set before creating the end product. For these examples, there are four criteria.
This means you must get four TRUEs in order for a number to be included in the calculation. Formula [5] uses Boolean multiplication AND criteria. Formula [6] uses Boolean multiplication and division AND criteria. Notes: For a reminder of how Formulas [1], [2], [3], and [5] work, see Chapter For a reminder of how Formulas [4], [6], and [7] work, see Chapter 4. Although there is no need for an AND formula helper column in this particular example because COUNTIFS would probably be better , in Chapter 15, when you study formulas to extract data, you will see a good use for such a solution.
To better understand the different ways that AND criteria can work in formulas, the following pages step through each of these array formulas and show how they calculate. The resultant array contains zeros. If zeros do not adversely affect the calculation, this method is great.
Two of the phrases you may hear to describe an OR logical test are "at least one" logical test and "one or more" logical test. This section shows two examples that illustrate how Boolean OR criteria addition works. The first example is shown in Figure For example, the contents of cell A2 cannot be both Gidget and Rodger. The OR criteria is being applied to a single cell. The second example, as you can see in Figure Instead of the OR question being pointed at a single cell, the two separate questions are pointing at two separate cells.
Because the two questions are being asked of two different cells, you can get two TRUE values. The OR criteria is being applied to two different cells. It is important for formula building. To learn about the two different types of formulas, you will next look at formulas that count using OR criteria. The OR criteria counting formula is looking at a single column. These five formulas are pretty amazing! But to fully compare and contrast the formulas, you must look at how all five formulas operate on a large data set.
The six-criteria OR logical test is pointing to a single cell. The most immediate observation is that Formulas [1], [3], and [4] look like really long formulas, whereas Formulas [2] and [5] look much shorter. In terms of typing out the formulas, Formulas [2] and [5] have the advantage.
The following section examines the formula calculation time aspect of these formulas. To time your own formulas, see Chapter 2 for details. Timing depends many factors, and your particular situation may have different timing results. If you are counting with OR criteria and the logical test will never result in more than one TRUE, Formula [1] is fastest, but it will take you a while to type the formula out.
Formulas [2] and [5] may be the best bets because they are easy to create and relatively fast. A particular test cannot get more than one TRUE. Because the two questions are being asked of two different columns, you have the potential of getting an answer for a particular customer that reports two TRUE values. For example, Fruits Inc. Formulas [4] and [5] double count Fruits Inc. Formulas [1], [2], and [3] do not double count Fruits Inc. The OR criteria counting formula is looking at two columns.
Thanks, Barry! Here is an important point: Formula [1] has two fewer array operations to perform than does Formula [3], which helps Formula [1] calculate more quickly than Formula [3].
It makes sense that fewer array operations leads to faster formula calculation times. This data set had , rows of data. Formula [1] is a bit faster than the others.
A particular test can get more than one TRUE. Formula [1] is fastest and easiest to create. Formula [1] looks good because it calculates fastest and is easiest to create. OR Criteria for Adding, Averaging, and Finding min or max The same OR criteria concepts you have learned about in this chapter can be applied to other aggregate calculations. Formula [1] calculates the correct sum. Formula [2] calculates the correct average.
Formula [3] calculates the correct sum but takes longer to calculate than Formula [1]. Division would work for max also. This means the type of formula you choose depends on whether or not the OR logical test can get more than one TRUE value. The OR logical test is pointing at a single column. If you have many OR Criteria, Formula [4] for counting and Formula [6] for summing may be easier to create.
The OR logical test is pointing at multiple columns. For counting, the helper column solution is fastest calculating. You will see it in Chapter 15, when you learn about extracting data with formulas. In Excel or later, you can use a slicer on an Excel table for OR criteria by holding down the Ctrl key and clicking the items.
The OR logical test is pointing at a multiple columns. Chapter 12 discusses the importance of choosing the correct function. Excel Files To follow along with the examples in this chapter, you can download the accompanying files, as explained in the Introduction.
Selecting the Fastest-Calculating Function or Formula When you create array formulas, the array operations in the array formulas can sometimes significantly increase formula calculation time.
This short chapter looks at two examples of how choosing a non-array formula over an array formula can significantly reduce formula calculation time. The criteria for counting is given as a year number in cell E8 and month text in cell F8. The goal is to count how many dates fall within this month. The problem is that you have a data mismatch.
The data in column A are serial number dates, and the criteria is a mix of number and text. This makes sense because the more cells that have slow formulas, the slower the overall spreadsheet will go. Note: If you have to create an array operation as you might have to do in formulas that extract data , it pays to look at different function options. The syntax and calculation steps are similar to those in the formulas discussed in the last section. Array Formula Efficiency Rule 25 Sometimes you can substitute non-array formulas for array formulas and significantly reduce formula calculation time.
In both cases, you reduce formula calculation time. Further, if you have to make an array calculation, selecting a particular function over another function may reduce formula calculation time. Dynamic Ranges An array is a collection of two or more items. A range is one example of an array. In this chapter you will learn about how to create a dynamic range using formulas.
A dynamic range is a range you can use in formulas, charts, PivotTables, and other locations that expect a range that can expand or contract when you add or delete source data or change based on some criteria as the formula that contains the range is copied down a column.
If you use a dynamic range inside the SUM function, the range A2:A4 will automatically expand to A2:A5 if a new number is added to the source data set. Creating Dynamic Ranges with Formulas So how do you create dynamic ranges? Volatile Functions Volatile functions recalculate every time Excel recalculates the spreadsheet, even if the precedents formula inputs have not changed.
This amazing feature was added in Excel and is great if you are going to have an expanding table of data. A number of limitations to this feature make dynamic ranges created with formulas useful.
This means it can be useful to learn how to create dynamic ranges with formulas. INDEX: Formulas to Look Up a Column or Row The most basic type of formula-created dynamic range is a formula that can look up an entire column or row of values or items based on a condition. If you look back at Figures The answers to these questions determine what sort of formula you might use.
In most situations, you will have to use the lookup formula concept "lookup last. In all four cases, in order to create a dynamic range with a formula that will expand and contract when you add or subtract data, you need to determine the last relative position in the column.
Note that many Excel users say "last row in the data set" imprecisely. The key lookup formula concept for Formulas [2] to [6] is that when you have a lookup value that is bigger than any value in the range and you do an approximate match that is, leave the third argument in MATCH empty , you will always get the last relative position in the list, even if there are empty cells.
All the concepts you have just learned about for finding the last relative position in a column will work for finding the last relative position in a row also. These two figures show number and text data types in a vertical orientation, but if the data type or orientation were different, any of the MATCH function examples from the last section could be used inside the INDEX function to get the last item in a range.
The problem with this setup is that if you add more data to the table, the data validation drop-down list and the lookup table in the VLOOKUP will not update. If only there were a way to get the ending cell reference in a range to update when you added or deleted records. You can accomplish a cell reference lookup by placing the INDEX function into the context of a cell reference. To see this formula in action, create the formulas shown in cell E2 in Figure To prove to that this formula really does create a dynamic range, you can evaluate different parts of the formula and see what results you get.
Using Figures Because INDEX is in the context of a cell reference, now the formula returns the entire table in array syntax. Remember that this is an array of many items, and a single cell cannot display many items at once. Notice that it displays only the first item in the array.
Also note that this is not the end use for the formula, so this is just one convenient way to store the formula until you do something else with it later. Defined Name Dynamic Range Formulas The formula you created for the dynamic range cannot be used directly to create a data validation drop-down list. If you try to use it, you get the error message "You may not use reference operators such as unions, intersections, and ranges or array constants for Data Validation criteria. The approach that I take in using formulas in defined names is to first create the formulas in the cells and then copy and paste the formulas into the defined name dialog box.
I do it this way because it is much easier to create formulas in cells than it is to create formulas in the defined name dialog box. Once you have created the name for the drop-down list cell E4 , you can copy the formula in the cell in Edit mode. Now you are ready to paste it into the New Name dialog box. In the New Name dialog box, shown in Figure Type a name without spaces in the Name text box.
Type a comment in the Comment text box. Paste your formula into the Refers To text box. Next you need to enter the defined name in the Data Validation dialog box see Figure Select cell E7. Select List in the Allow text box. Type an equal sign and the defined name into the Source text box. After you enter the formula in cell F7, you can copy it to cell G7.
They also work if you delete data. A Formula for a Dynamic Table When There Are Data Inconsistencies In some cases you may have a data set that has an empty row or that has missing data in one or more of the columns, and you would still like to define a dynamic range with a formula that includes the last row of data.
What if you had a rogue space in some cells below the data set? If you were concerned about this, you could add the TRIM function which removes all spaces except for single spaces between words , as shown in Figure There will be no empty cells before the last number, and you will not enter numbers past cell A Some cities are repeated because you have more than one representative in each city. You would like to create a dynamic range of rep names based on the city name in cell E2.
This sort of formula could be used for a data validation drop-down list where you want the list of names to be based on a city name. The array operation will cause this formula to calculate more slowly than the one in Figure Notes About the Formulas to Create a Dynamic Range for a Table Within a Table The following table explains the formulas to create a dynamic range for a table within a table. If volatility is not important in a particular case, which one you use really is a matter of preference.
Whereas the OFFSET function creates the dynamic range with inputs that define the starting position and the size of the range, INDEX creates the dynamic range by looking up cell references or a row and column reference. For example, if you have a data set where data is being added, it might be convenient to have the new data automatically added to the chart. Another example would be to plot a different set of numbers, based on criteria from a cell.
In both of these cases, these are the steps to create a dynamic range for the chart: 1. Create the dynamic range formula. Create a defined name that uses the dynamic range formula.
Create the chart. Insert the defined names into the chart. The goal is to plot the sales numbers and sales rep names on a column chart based in the city name in cell F1. You can choose whichever you prefer. You will use the dynamic formula for the rep range for the horizontal axis, and you will use the sales range for the column heights.
Next, you need to create two defined names. However, you should be aware of the Steven M. Excel insertsa title with thetext Chart Title. Clickthe text and replace it with your actual chart title. How to master Excel charting I've had the pleasure of meetingsome ofthe world's leading Excel chart experts. The book is divided into two parts: the first part outlines model designs and gives templates, key features and techniques. The second part of the book shows how to build corporate financial models in Excel.
Written by Excel expert Thomas Chester, this book reveals new and overlooked ways to reduce work, improve performance, and achieve better results. Make Excel work for you. C Excel to be started in Windows as an icon. Putting Excel here will For example , you can print a blank form to be filled in by hand , such as the hotel valet - parking audit form shown in Figure Excel includes a special kind of range — represented by the UsedRange property.
Master It What is unique about a UsedRange? Solution If you need to work with all the cells on a worksheet but not with any unoccupied areas of the Explore advanced skills in Excel and gain an amazing array of tricks and tools to increase your productivity.
This book discusses new techniques such as power functions, chart tricks, and many more to master Excel. New to this edition Updated glossary of key terms Functions list in English and Euro languages Continuity check on all formats, layouts and charts More worked examples Additional exercises at the end of each chapter to help build models Excel includes a special kind of range—represented by the UsedRange property. Master It What is unique about UsedRange?
Solution If you need to work with all the cells on a worksheet but not with any unoccupied areas of the worksheet , Work with workbooks. You often need to create a new, blank workbook in a Master It Work with ranges. Within a worksheet, you'll often need to manipulate ranges of cells. Skip to content. ISBN: Category: Computers Page: View: 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 standard Excel formulas cannot or would be too arduous to attempt.
Beginning with an introduction to array formulas, this manual examines topics such as how they differ from ordinary formulas, the benefits and drawbacks of their use, functions that can and cannot handle array calculations, and array constants and functions.
0コメント