Adventures in Excel–Array Formulas

This NBA season, I decided to create a Fantasy Basketball League.  I had no idea how seriously involved I was going to be.  Within the first two weeks, I created an Excel spreadsheet to help me analyze my team.  As a result, I learned some cool tips & tricks in Excel.

Array formulas helped me to accomplish the following scenario:

Look at the GREM (games remaining) column.  It looks at the content on the same row that includes columns MON through SUN.  At the beginning of a new week (which begins on Monday), I want to see how many games the player has yet to play.  If a number appears in the column (as seen in the case with LeBron James), that means that game has been played, so only count the remaining games in the week.

Although not shown in the screen capture above, should the word “bench” appear in any column referencing a day in the week, I do not want that counted in the GREM column.

How did I make it work?  Here is the formula:

 {=COUNT(IF(ISNUMBER(VALUE(AN9:AT9)),"",IF(AN9:AT9="bench","",1)))}

Do you see the curly braces surrounding the formula?  This is accomplished by pressing [CTRL] [SHIFT] [ENTER] when the formula inside is complete (do not manually add the curly braces).  This syntax is known as an array formula.  In the example above, column AN is Monday, and column AT is Sunday.  Row 9 is referring to the line of data for LeBron James.

Now here is what is awesome about array formulas.  Notice how I am evaluating AN9:AT9 as if it were a single value?  For the developer nerd in you, consider this like a FOR EACH statement.  It is basically evaluating each cell in that range to determine if it meets the criteria.

If you have complex business rules that require per cell criteria evaluation, array formulas will be one of your most powerful Excel allies!