While arrays are great for grouping a system of formulas, their real advantage is to enable complex subtotals and lookups that ordinary Excel functions fail to support, through a technique known as the array sum or the conditional sum.

The general syntax is:

{=SUM(
     [condition 1]*
     [condition 2]*…
     [value 1]*…
     )
}

Note the brackets enclosing the formula.  To enter the formula as an array, press CTRL+ALT+Enter.

Each of the conditions and values is an array of corresponding size (equal size most of the time, but not necessarily).  The enclosing SUM() function and the array brackets serve as a SUMPRODUCT().  Note that

=SUMPRODUCT([array 1], [array 2], …)

is equivalent to

{=SUM([array 1]*[array 2]*…)}

The array sum multiplies the value array by one or more condition arrays, which contain 0’s and 1’s (FALSE and TRUE).  These condition arrays serve as filters (or masks) to zero-out values not corresponding to the specified conditions.  By picking up the remaining values this way, we can take SUMIF(), COUNTIF(), VLOOKUP() and other functions beyond one condition and one dimension.

Below is an example of box office sales records.  To make sense of this data, we’d often need to analyze subsets of the records.

Download the Sample File

With SUMIF() and COUNTIF() functions, we can easily obtain a subtotal for all sales in July.  We’ll practice using an array sum instead:

{=SUM(
     ([month column]=”July”)*
     ([sales column])
     )
}

Note that by omitting the [sales column], we’d be counting the number of records instead of summing their values.

Moving on, let’s get a subtotal for all June sales in Roanoke.  SUMIF() and COUNTIF() fail to be useful here.

{=SUM(
     ([month column]=”June”)*
     ([city column]=”Roanoke”)*
     ([sales column])
     )
}

Similarly, we can add a third condition (or in fact, as many as necessary):

{=SUM(
     ([month column]=”August”)*
     ([city column]=”New York”)*
     ([movie column]=”Transformers”)*
     ([sales column])
     )
}

In the last example, only one record satisfies all conditions.  The result is similar to that of a VLOOKUP(), except of course, we have a lot more freedom by using an array sum.  Sometimes, you might find yourself needing to specify more than one condition in a lookup and not wanting to double count any potential duplication of records.  What you can do is to use a MAX() function in place of the SUM() to limit the result to one record.

Here is a typical data snapshot summarizing the subtotals in a two-dimensional table:

Lastly, let’s see how an array sum can obtain subtotals (or single-value lookups) in such a two dimensional array.  The trick is to line up the dimensions of the condition and value arrays:

{=SUM(
     ([3×1 month column]=”July”)*
     ([1×3 movie row]=”Batman”)*
     ([3×3 sales table array])
     )
}

This article provides an introduction to the more advanced methods of using Excel formulas.  Once you get familiar with these methods, you will find a much more satisfying experience analyzing data in Excel.

Download the Sample File

 

Since many of this blog’s posts will be about array-based tricks, it might be a good idea to have a proper introduction to Excel’s array formulas.

By default, Excel’s functions and formulas take one or more input values and produce a single output value. Think of the typical multiplication and SUMPRODUCT() function.  When solving a more complex system of problems, however, it is often desired to process an array of similar values concurrently.  Hence the array formulas.

Download the Sample File

Let’s start with the real basics.  You’re the manager of a video game store, and for recordkeeping, you’ve just created a spreadsheet listing the prices of your inventory of video game consoles.  Now you want the identical price listing to appear on another part of the spreadsheet, and here’s what you can do:

  1. Select an area identical in size to the source data array (in this case, 3 rows x 1 column)
  2. Type the “=” to get a formula started, then drag to select the source data array
  3. Press Ctrl+Shift+Enter to complete your first array formua

Note that, in the formula bar, a pair of brackets {} enclose your formula.  This tells you that it’s recognized as an array formula.

Note: if you pressed the Enter key without holding down Ctrl and Shift, the brackets would not appear and only the first element in the array would be recognized.  Additionally, you cannot type in those brackets.

An array does not have to be a one-dimensional vector.  You can also reproduce an entire table by employing a two-dimentional array (3 rows x 2 columns):

So what now?  Pretty boring and useless, huh?  For this type of tasks you would typically use a simple “=” formula and copy it across the table.  In fact, we’d usually recommend that as well.  But this approach has its advantages – because the entire array (1D or 2D) is produced by one formula, you cannot change any of its elements (try typing over any of the cells… Excel won’t let you).  When processing large amounts of data, it’s not always easy to check for human errors, i.e. if your coworker’s son messed up one of your 5,000 rows of identical formulas.  Array formulas help ensure data integrity.

Moving on… now it’s the end of the month and your intern compiled the sales records for the store.  But he listed the consoles horizontally on three rows instead of vertically!  To fix this, you use the TRANSPOSE() function to “rotate” the table… turning rows into columns and columns into rows.  This is a very handy function that’s strictly used with arrays.


To turn this 2×4 table into a 4×2 table


Use the TRANSPOSE() function… don’t forget, Ctrl+Shift+Enter!

Okay, before we wrap up this post, let’s look at something actually useful, i.e. more than simply duplicating values.  Given your price list (a 3×1 array) and your intern’s sales records (a 1×3 array), you now want to calculate the total revenue.  SUMPRODUCT() comes to mind since you want to multiply the price by unit for each product, and add them all together.

However, SUMPRODUCT() requires all of its input parameters to be of identical size.  If you simply entered in the row array and the column array, you’d get a #VALUE! error.  You could TRANSPOSE() one of the two arrays to match the dimension of the other, or you could simply use TRANSPOSE() within your SUMPRODUCT():

{=SUM([array1], TRANSPOSE([array2]))}

Note the brackets – don’t forget to press Ctrl+Shift+Enter.  This calculation gives you the total revenue by first turning the two arrays into the same dimension, and then apply the SUMPRODUCT() function.

Download the Sample File

In the next article, we’ll talk about array sums, which lead into a whole wide world of possibilities.

Let’s recap.

  1. You want to learn about Excel.
  2. You want to buy computer parts to build a PC.
  3. You research the prices at Best Buy and Circuit City.
  4. You research the prices at more stores.

You have optimized the computer part prices and determined that the lowest total cost is $640.63. Before you pat yourself on the back for your hard work, you must realize that although cost is important, it means little unless you determine where to shop. The following Exhibit cleanly indicates the best price option and its respective store.

Download the Sample File

One option to create the exhibit is to use a manual “select cell, highlight, change font” (rinse, repeat, recycle many many times). This manual process may be fine for small projects, but the task can quickly become tedious if information changes or expands quickly.  Let’s create the exhibit using Excel’s highly powerful and usable Conditional Formatting (Excel 2003: Format > Conditional Formatting).

  • If an additional “cheaper” column is available, this task is easy. Choose the appropriate cells and set the “Cell Value Is” to refer to the Cheaper column.  (In the example below, column I refers to the before mentioned “cheaper” column.)  I have chosen the “equal to” option, but there are many other options that are easy to understand.

Download the Sample File

  • Now, lets make the assumption that you have no interest in creating an additional “cheaper” column.  No problem.  Instead of the default “Cell Value Is“, change the condition type to “Formula Is“.  This allows you to write an Excel formula which must solve to True or False (or equivalent).  In the example below, columns C through G refer to prices specified in Best Buy through Walmart.

Download the Sample File

Conditional Formatting is a very useful tool. When working for a financial firm, I used Conditional Formatting to error check, show real time changes in Equity Prices (Bberg BDP function), and highlight cells for concern.  What is really nice about Conditional Formatting is that you can utilize all the popular Excel formulas.  In many ways, you can create instant feedback for your spreadsheets because you can set up cells to format automatically based on your input/formulas.  Unfortunately, there are restrictions.   Excel 2003 only allows 3 conditions, the size of the file will increase, and large spreadsheets can become noticeably slower.  Microsoft expanded this tool in Excel 2007 and offers >3 Conditional Formatting formulas and more intuitive default conditions.  I have yet to explore all the Excel 2007 additions, but perhaps this could serve as a post in the future!?  Thanks to Peter for his original post.

Note*  If you need to build a computer, do your own research.  These prices most likely do not reflect actual store prices!

Download the Sample File

Lets revisit Summing Element-Wise Minimum once again. To recap:

“Suppose you plan to buy several computer parts from either BestBuy or Circuit City. The two stores have different prices for each product. If you buy from one store exclusively, you won’t get the best deals possible. To optimize the value of your hard-earned dollar, you want to find the least amount of money necessary to buy one of each part.”

Lets suppose you are not convinced you have truly optimized your hard-earned dollar. Therefore, you decide to add prices for Office Depot, Duane Reade and Walmart.

Download the Sample File

An easy way to solve this problem would be to create a new column which utilizes the MIN() function. But, since you believe in optimization you want it ALL in one single cell. What are your options? I guess you can start creating a crazy array that will be difficult to understand such as the following example which determines the cheapest total price between 3 stores (arrays):

{=SUM(IF([array 1]<[array 2],IF([array 1]<[array 3],[array 1],[array 3]),IF([array 2]<[array 3],[array 2],[array 3])))}

Looks… um… um… right. So outside of adding a column and creating a crazy array formula, what other option is available? Enter Microsoft Visual Basic! It seems that many Excel based users may go pretty far in their careers without ever understanding the super hyper intimidating but useful tool. (Excel 2003 keyboard shortcuts provided)

  • Tools > Macro > Macro… (Alt + F8) and
  • Tools > Macro > Visual Basic Editor (Alt + F11).

So now, wouldn’t it be nice if we could just write a function and refer to a bunch of cells and get exactly what we want? Guess what? With Visual Basic, you can… With the Visual Basic code below, you can add as many stores as you like while solving for the optimal price.  It is important to note that everyone has their own programming style, you can feel a glimpse of my style with the code below.

Download the Sample File

I certainly encourage you to download the sample file because I have provided comments within the Visual Basic Module. That said, I do want to make a few quick notes.

  1. A Visual Basic function (if non-Private) can be called from the Excel Spreadsheet directly. The spreadsheet contains the formula, =minSumItNow([Array 1 … Array N]).
  2. Visual Basic Function differs from Sub because Function must return a value. The value is set by assigning the Function name.
  3. [Array Name].Rows.Count and [Array Name].Columns.Count are very useful. This allows the user to understand the size (vertically and horizontally) of arrays. This is useful because users do like to include a range of cells as an input. (For Each is useful as well and will be discussed in a later post)
  4. Visual Basic might seem like more work to learn, but the rewards are plentiful. Learn Visual Basic to:
    • Impress your colleagues.
    • Work more efficiently.
    • Automate tedious tasks.
    • Unlock the true power of Excel.

Download the Sample File

Peter explained an useful array based formula in his post. I won’t repeat what he was trying to accomplish, but a piece of his post really got me thinking. So for my very first, real post, I will blog about a real issue that many of us will face at one point.

Here’s the scenario. You are the valedictorian from the USA Today’s #1 ranked Liberal Arts University in Northern Kentucky. Your summa cum laude in Art History has taken you to the most prestigious consulting firm, ABC Consultants LLC (“ABC”). Your manager, Peter, is very excited to have you on board and tells you about a really exciting project that he worked on last year. It deals with the super-innovative and enterprising firm that is breaking new grounds everywhere it turns. This firm is known as XYZ Corporation (“XYZ”). Peter is really proud of the work he did last year (after all, XYZ renewed its contract with ABC, so why not be proud Peter?) and directs you to a spreadsheet named “XYZ Model.xls“. He wants you to take a look at the model and understand it by tomorrow morning so you can have a long conversation with XYZ’s CEO about its methodologies. Before you leave, Peter mentions:

“XYZ Model.xls may have a ‘convoluted approach and a super long formula‘”.

Here’s that formula:

{=SUM(C4:C12*((C4:C12-D4:D12)/IF(C4:C12=D4:D12,1,
ABS(C4:C12-D4:D12))-1)/(-2)+D4:D12*((C4:C12-D4:D12)/
IF(C4:C12=D4:D12,1,ABS(C4:C12-D4:D12))+1)/2)}

Array formulas are great because you can cram a ton of information into one single cell. The problem is, they are often difficult to understand because they can be very involved! So where would you begin?

1) Organize the array formula. When programmers code, they love to use indenting to help organize their thoughts. Let’s use a similar approach:

{
_____=SUM
_____ (
__________C4:C12 *
__________(
_______________(C4:C12-D4:D12) /
_______________IF(C4:C12=D4:D12,1,ABS(C4:C12-D4:D12))
_______________-1
__________) /
__________(-2)

__________+

__________D4:D12 *
__________(
_______________(C4:C12-D4:D12) /
_______________IF(C4:C12=D4:D12,1,ABS(C4:C12-D4:D12))
_______________+1
__________ ) /
__________2
_____)
}

2) Look for patterns. Many Excel array formulas process similar cells of information. Let’s define variable lists A through E as the following:

A = C4:C12
B = D4:D12
C = C4:C12-D4:D12
D = IF(C4:C12=D4:D12,1,ABS(C4:C12-D4:D12))
Let’s also have … E = C ÷ D
_____Note (if we want to go wild)*
_____C = A – B, D = IF( A=B,1,ABS(C) )

3) Now that we have organized our formula and defined variables, we can combine the results from Steps 1 & 2 together to create a cleaner formula.

= ?(A x (E – 1) ÷ -2 + B x (E + 1) ÷ 2)
= ½ x ?(-A x (E – 1) + B x (E + 1)) | E = C ÷ D

4) The formula seen in Step #3 looks complex, but to me it looks a lot simpler (and less confusing) then the original formula!  Let’s take advantage of Excel and expand out the array formula.

Download the Sample File

5) Great.  Now that we have confirmed our result with the original array formula’s result, we are done, right?  No way!  Remember Peter’s initial request.  He would like you to be able to  hold a conversation about the model.  Let’s understand the formula!

= ½ x ?(-A x (E – 1) + B x (E + 1)) | E = C ÷ D

  • C is simply the difference between A and B.
  • D is the absolute value of C or 1 if C is zero.  Why is D not just zero when C is zero?  I will tell you soon.
  • E as mentioned is C ÷ D.  This results in E becoming either -1, 0, 1.  Back to our non-zero D riddle above.  Since E is C ÷ D, dividing by D = 0 would result in a #Div/0! (Divide by Zero) error.  Mathematically speaking, anything divided by zero gets everybody confused!
  • Now we know that E is -1, 0 or 1.  Since we have calculated E, lets forget about arrays C and D.
  • Let’s take a look at each scenario.
    • If A < B Then E = -1
      • = ½ x (-A x -2 + B x 0)
      • = ½ x (-A x -2)
      • = A
    • If A = B Then E =0

      • = ½ x (-A x -1 + B x 1)
      • = ½ x (A + B) |  A = B
      • = A or B (it does not matter!)
    • If B < A Then E = 1

      • = ½ x (A x 0 + B x 2)
      • = ½ x (B x 2)
      • = B
  • Based on the results above.  Peter has duplicated a Min like function for A and B.  This formula does achieve what Peter was originally set to accomplish “Summing Element-Wise Minimums“.

6) If you become completely confused by the formula, removing yourself from the spreadsheet will often clear your mind.  Do your best to understand what you can, but do not spin your wheels!  Don’t be shy.  Have a conversation with your manager (or a knowledgable colleague) to either confirm or gain further understanding.  As you gain practice and experience with array formulas, you will begin to understand these formulas quickly.  Just be sure when you create a model with complicated array formulas, document the process to avoid further confusion!

Download the Sample File

This article discusses an issue on rounding error, specifically related to breaking down a total amount with a trend/inflation rate.  It’s more of a math problem than an Excel problem, and if you work in the healthcare industry, this problem probably will look familiar to you.

Note: “Trend” here is assumed to be a compounding annual cost inflation.  When “trending” an amount from one period to another, the length of time is measured between the mid-points of the two periods.

Suppose you made a projection for your client’s healthcare spendings to be $12 million in the upcoming calendar year, assuming 10% annual trend.  Now the client wants to break that down to monthly figures.

The answer is not $1 million for each of the 12 months.  The force of trend makes this spending an increasing function over time, i.e. February should cost slightly more than January, and so on.  The industry-standard formula is to apply the 10% trend, from the mid-point of the year to the mid-point of each month, i.e.

Monthly Amount = Annual Amount / 12 * (1 + Trend) ^ ([mid month] – [mid year])

While this is a perfectly valid and defendable approach, the results always have a problem – the “rounding error”.  As seen in the table below, adding the 12 months of breakdown back together produces a slightly higher sum than anticipated.  This error is usually small ($4,511 in this case, < 0.1%) and people tend to overlook such insignificant imperfection.


Numbers don’t add up

Note: This type of difference is widely known in the industry as a “rounding error”, but technically it isn’t.  Rather, it is a methodological inaccuracy.

But not everybody can tolerate imperfections – certain demanding bosses/clients could get very upset if your basic numbers don’t add up!  To correct this situation, I’ve seen people throw that $4,511 in one of the months, break it out evenly across the 12 months, or use some GoalSeek magic to add a margin to each number until the total comes out right.  None of these methods is pretty.

Take a step back and think like a Calculus student again… the trending cost can be represented as the area under curve of an exponential function, and the aforementioned approach to monthly amounts is a discrete numerical approximation of the integral!


The pink bars are the results of the previous method, which closely but not perfectly approximate the area under the blue (exponential) curve.

Now, if you can take a basic anti-derivative from point a to point b (one month to another), you’ll have a solution that’s better than a good approximation:

Monthly = Annual / Trend * ((1 + Trend)^(i/12) – (1 + Trend)^((i-1)/12))

The resulting formula is difficult to understand when you look at it for the first time.  However, once you understand the concepts, it’s very easy to explain.  The monthly breakdown sum up beautifully to the exact $12 million, so you won’t have to do any unsightly tweaking!


Numbers add up perfectly!

Download the Sample File

The sample file contains a hand-written derivation of the formula.

 

I finally felt like I was able to figure out Excel 2003.  I knew all the keyboard short-cut commands without even needing to look at the keyboard, was proud of my ability to convey data in fancy graphical representations, and was getting very comfortable with building Visual Basic macros.  Yes, with regards to Excel 2003- I felt absolutely dangerous.

Now its all different.  Here on my desktop is the brand-spanking new Microsoft Excel 2007 (right, it is not that new anymore considering it is already 2008).  Gone now are the familiar menus I once knew and replaced with a seemingly more complex simpler ribbon system.  It feels new to me, and I seem to work really really slow.  That makes me scared.  Very scared.  My goal is to become proficient and speedy with the newer Excel 2007.  In the process, I hope my learning becomes yours as well.

It is important to note that Excel 2007 has a NEW format unrecognizable by Excel 2003.  Does this mean that Excel 2003 users should completely avoid my posts?  Nope!  Since not all of my concepts will focus on the newer excel, the 2003 “.xls” will be available.  I will be sure to make this distinction!

How do you use an array formula to sum up element-wise minimum values?

Suppose you plan to buy several computer parts from either BestBuy or Circuit City.  The two stores have different prices for each product.  If you buy from one store exclusively, you won’t get the best deals possible.  To optimize the value of your hard-earned dollar, you want to find the least amount of money necessary to buy one of each part.

99.5% of the time, you’d set up a spreadsheet similar to the one shown below.  By using a MIN() function, you create a third column listing the lower price of each item.  Then it’s a no-brainer to add up the column for the total.


Download the Sample File

Occassionally, you may want/need to calculate this number in one single step.  Array formulas came to mind and it seems intuitive to write {=SUM(MIN([array 1], [array 2]))}.  However, that doesn’t work because MIN([array 1], [array 2]) does not give you an array of element-wise minimum values… instead, it gives you a single minimum value between the two arrays.

To fix this problem, you can employ the IF() function within your array formula.  This is short, clean, and easy to understand:

{=SUM(IF([array 1]>[array 2], [array 2], [array 1]))}

Or if you prefer to use the mask/filter approach, your formula could look like this:

{=SUM([array 1]*([array 1]<[array 2]) + [array 2]*([array 1]>=[array 2]))}

The ([array 1]<[array 2]) part creates a mask of 0’s and 1’s, which is used in a similar manner to the typical SUMIF-type of array formulas.  The two approaches use the same logic, and the only difference is the appearance.

A problem is always simple when you already have the solution, but believe it or not, I came up with the most convoluted approach and a super long formula when this problem was first presented to me.  I’ve included that formula in the sample spreadsheet – see if you can figure out what I was trying to do!

Download the Sample File

My intern came to me today and asked about displaying multiple lines within a cell.  I’ll write the first real post in his honor.

Most people know that when entering text in Excel, pressing ALT+ENTER makes a new line.  However, he needed to generate this multi-line string dynamically (via a formula).  The new line characters, carriage return (CR) and line feed (LF), are not as obvious in Excel as most programming languages make them.

Note: This refers to the ‘\r\n’ in C-styled languages and vbCrLf in Visual Basic. In many environments, however, only the latter (‘\n’ and vbLf) is necessary to create a new line.


Download the Sample File

One way to do this, which may be more intuitive to non-programmers, is to use the same ALT+ENTER key stroke by entering this formula:

= [first line] & “[press ALT+ENTER here]” & [second line]…

Another way to do this, which is longer but probably preferred by programmers, is to employ the new line characters (CR + LF):

= [first line] & CHAR(13) & CHAR(10) & [second line]…

Similar to aforementioned note, only CHAR(10) is necessary for the desired results.  Add CHAR(13) only if you have the pet peeve from certain programming styles.

This entry may sound obvious, but here’s the catch: by using the formula above, you probably will still see all your text concatenated on one line.  You still need to wrap text (Format -> Cells -> Alignment tab -> Wrap text) in order to see multiple lines!  Note: Excel automatically sets the wrap text property when you enter plain text (instead of a formula) with a line break in a cell.

Download the Sample File

Tags: , , , ,

When I was in college, I regarded myself as an Excel expert.  But as soon as I started getting paychecks in the “real world”, I stopped being so naive.  My Excel knowledge doubled, tripled, and went up at least 10-fold within three years.  Do I consider myself an advanced user now?  Sure.  Is there more to learn?  Of course.

Like other professionals whose worlds revolve around Excel, I gained most of my knowledge through solving topical problems, and I frequently found help through Google on blogs/forums/websites run by Excel experts.  Now that I’m much more comfortable with this software, I thought that it’s time to give back to the community.  Hopefully one day someone will be Googling a specific Excel question and find a solution here.  Then I’ll be glad.

Newer entries »