It’s August already! How did that happen? Did you know that August is not only a month name, it’s an adjective that means “inspiring reverence and admiration; venerable, impressive”. In Excel, it’s also impressive to know how to use array formulas.
If you’d like to develop august array skills, here’s your chance to win an e-book copy of Mike “ExcelIsFun” Girvin’s new book, Ctrl+Shift+Enter: Mastering Excel Array Formulas.
Array Formula Basics
Throughout the book, Mike builds a set of Array Formula Efficiency Rules. The first rule is that you should have a basic knowledge of how Excel formulas work, and understand the default number formatting and alignment rules. That gives you a solid foundation for adding advanced formulas to your Excel skills.
Mike starts with array formula basics, and shows how to create a simple calculation that finds the MAX for a set of cells, where one is subtracted from the other in each row.
Instead of adding a column to show the differences, and then determining the MAX for the new column, Mike shows how to do it all in a single cell, using Ctrl+Shift+Enter, to create an array formula.
{=MAX(D3:D6-C3:C6)}
When Should You Use Arrays?
In his book, I like the way that Mike carefully explains the advantages and disadvantages of array formulas, compared to non-array. You’ll get a list of questions to ask yourself, and the answers will help you decide which solution might be best.
Sometimes there isn’t a clear-cut answer, but at least you can make an educated decision. For example:
- Is there a built-in function that can do the same thing more efficiently?
- Could you use a helper cell formula?
- Have you timed the various formula options? 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.
Everything You Ever Wanted to Know
If you are an Excel formula guru, and work with array formulas every day, you might not need this book. However, Mike has filled this book’s 352 pages with a dazzling collection of examples, so even the function masters might learn a new trick or three.
If you’re comfortable using Excel, and want to explore a fascinating new area, you’ll gain lots of new knowledge from Mike’s Ctrl+Shift+Enter book. Everything is explained in detail, with color-coded screen shots, and you can download the sample files to follow along with the instructions.
If you work on a chapter each day, you’ll be an array formula master in just a few weeks. By the end of the summer, you’ll be ready to tackle the formula challenges in the last chapter. For example:
- lookup the first item in a row with empty cells
- rank values with one condition
- look up the longest word in a column
Enter the Giveaway
Bill Jelen (Mr. Excel) is the book’s publisher, and he has generously donated 4 e-book copies for this giveaway. If you’d like a chance to win a copy, please read the rules, and then make a comment below.
- In your comment, tell us one of your favourite Excel functions, and if you have ever used that function in an array formula.
- Include your email address, so I can contact you if you win. Your contact information won’t be publicly visible, and it won’t be used for any other mailings.
- The deadline is Wednesday, August 7th, 2013, at 12 noon Eastern Daylight Time.
- One entry per person.
- The 4 winners will be announced on Thursday, August 8th, 2013.
- Each winner will have 24 hours to claim the prize, and if not claimed, another name will be selected.
___________________