Best Practice

You are currently browsing the archive for the Best Practice category.

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