Excel 2010 Bible

379 CHAPTER Performing Magic with Array Formulas IN THIS CHAPTER More examples of single-cell array formulas More examples of mu ...

Part II: Working with Formulas and Functions 380 #N/A). Figure 17.1 shows an example. The formula in cell C11 returns an error v ...

Chapter 17: Performing Magic with Array Formulas 381 This formula creates an array that consists of 1s (if the corresponding cel ...

Part II: Working with Formulas and Functions 382 FIGURE 17.2 The calculated average includes cells that contain a 0. Two of the ...

Chapter 17: Performing Magic with Array Formulas 383 Determining whether a particular value appears in a range ................. ...

Part II: Working with Formulas and Functions 384 A simpler form of this formula follows. This formula displays TRUE if the name ...

Chapter 17: Performing Magic with Array Formulas 385 empty string. The MIN function uses this new array as its second argument, ...

Part II: Working with Formulas and Functions 386 Determining whether a range contains valid values ............................. ...

Chapter 17: Performing Magic with Array Formulas 387 This function generates an array with three elements: {4,0,9} By simplifyin ...

Part II: Working with Formulas and Functions 388 Summing rounded values ........................................................ ...

Chapter 17: Performing Magic with Array Formulas 389 FIGURE 17.6 An array formula returns the sum of every nth value in the rang ...

Part II: Working with Formulas and Functions 390 To make the formula work with a horizontal range, you need to transpose the arr ...

Chapter 17: Performing Magic with Array Formulas 391 Determining the closest value in a range .................................. ...

Part II: Working with Formulas and Functions 392 This formula uses the COUNTA function to count the number of nonempty cells in ...

Chapter 17: Performing Magic with Array Formulas 393 Figure 17.8 shows a worksheet that uses two methods to rank a column of val ...

Part II: Working with Formulas and Functions 394 Working with Multicell Array Formulas ......................................... ...

Chapter 17: Performing Magic with Array Formulas 395 {=IFERROR(INDEX(Data,SMALL(IF(Data<>””,ROW(INDIRECT (“1:”&ROWS(Da ...

Part II: Working with Formulas and Functions 396 FIGURE 17.10 A multicell array formula displays the entries in A4:A13 in revers ...

Chapter 17: Performing Magic with Array Formulas 397 The following modified version eliminates the #NUM! display by using the Ex ...

Part II: Working with Formulas and Functions 398 FIGURE 17.12 Using an array formula to return unique items from a list. Display ...