ExcelTips: Amazing Array Formulas

An array is a group of items that can, in some fashion, be treated as a whole. In Excel, an array formula is a special type of formula that allows you to perform calculations on huge amounts of data, in a single operation. Array formulas, once properly understood, provide new and exciting ways that you can perform calculations with your data. In fact, there are some calculations that can be done with array formulas that would otherwise be impossible with regular Excel formulas.

In the pages of ExcelTips: Amazing Array Formulas you'll find dozens of tips that can help you better understand how to put array formulas to work with your data. Many of these tips have been pulled from past issues of the ExcelTips newsletter, but their inclusion in this single e-book brings a level of focus that is not available in any other single source.

Not all the information presented here is from past issues of ExcelTips, however. A good portion of the information is brand new, never presented in any of our newsletters. You'll discover not only what array functions are and how to create them, but also different ways you can use them right away. Just take a look at this comprehensive table of contents:

1. Introduction
• A Word about Versions
• Can't All this Stuff Be Done with Macros?
• But What About Regular Formulas?
• Need More Tips?
• Sharing this Document
2. Array Formula Basics
• Understanding Array Formulas
• Single-Cell Array Formulas
• Multi-Cell Array Formulas
• Editing Multi-Cell Array Formulas
• Easily Selecting an Array Formula Range
• Array Formulas without Cells
3. Using Array Formulas to Count
• Counting with Two Criteria
• Counting Records Matching Multiple Criteria
• Counting Dates in a Range
• Counting Unique Values
• Counting Unique Values
• Counting Cells with Specific Characters
• Counting Groupings Below a Threshold
• Counting Commas in a Selection
• Counting Wins and Losses
• Getting a Count of Unique Names
4. Using Array Formulas with Numbers
• Summing Digits in a Value
• Summing Absolute Values
• Summing Every Fourth Cell in a Row
• Using a Numeric Portion of a Cell in a Formula
• Calculating the Median Age of a Group of People
• Excluding Values from Averaging
• Returning the Smallest Non-Zero Value
• Last Non-Zero Value in a Row
• Picking Different Random Numbers from a Range
• Using GEOMEAN With a Large List
5. Using Array Formulas with Text
• Comparing Lists for Duplicates
• Finding the First Non-Digit in a Text Value
• Inserting Dashes between Letters and Numbers
• Getting Rid of Everything Except Numbers
• Breaking Up Variable-Length Part Numbers
• Checking for Messages in Cells
• Deleting Duplicate Columns
6. Using Array Formulas with Dates
• Weekdays in a Month
• Finding the Date Associated with a Negative Value
• Calculating Averages by Date
• Deriving Monthly Median Values
7. Using Array Formulas for Lookups
• Complex Lookup Formulas
• Making VLOOKUP Case Sensitive
• Searching for a Value Using a Function

ExcelTips: Amazing Array Formulas contains a wealth of information. We've tried to make sure that it contains anything and everything that you'll find useful as you work with array formulas in Excel. The book zeroes in on what you need to know and provides great ideas on how you can work with your data easier than ever before with array formulas.

ExcelTips Amazing Array Formulas is provided in two formats: a Microsoft Word document and an Adobe Acrobat (PDF) document. You don't have to decide which you want, however; both versions are included. In these formats you enjoy the following features:

• The ability to view, search, and print the book.
• Screen shots that illustrate what you see in Excel as you follow the step-by-step instructions.
• The ability to select and copy sections of text, such as macro code.
• Hyperlinks to internal content (in the book itself) and referenced resources on the Web.

Since the electronic book is provided in Adobe Acrobat (PDF) format, you need to either have Acrobat or the free Adobe Acrobat Reader. Many computer systems already have the reader installed. If you do not, you can get it free, directly from Adobe.

ExcelTips: Amazing Array Formulas is immediately usable by all Excel users; the information it contains has been tested with Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007. Whatever version you are using, ExcelTips: Amazing Array Formulas provides a valuable resource for focused information that can't be matched by any other single source or computer book.

Description	CD		Download
ExcelTips: Amazing Array Formulas	$24.99		$19.99		(0.7 MB)

You can also order ExcelTips: Amazing Array Formulas by calling our order line: 801-607-2035.