Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Help intepreting an array formula please (2002)

    I have found a very powerful array formula which I know, from testing, works, but I do not understand how it works. It was written by someone whom I cannot locate to ask about it. I'm new to array formulae. Can someone explain in methodical plain English what the formula is saying?

    The formula takes the form {=SUM((B8:C8=(D7-B7:C7))*1)} but it is probably easiest for to understand it by looking at the attached example -- it is a short 6 rows.

    In the example, there is a "base case" production time series forecast -- before allowing for any delays.

    The array formula (row 9) reads a user input (row 8) which specifies the year of the onset of a delay, and the delay's duration in years. Row 7 consists of the year numbers (e.g 2007, 2008 etc). The formula answers the question: what is the first year that the production resumes? It does so by by returning a 1 in the appropriate year column.

    (Using this output, I then used the OFFSET function in row 11 to get the new, post-delay production time series.)

    I'd love to understand how the array formula works, because it seems potent. It enables me to do time-shifting which used to require a lot of extra logic -- especially in a monte carlo simulation, where potentially any combination of consecutive years of pre-delay production is possible. This was not only tough to code and test, but was difficult for others to follow, it increased the chance of error, and sometimes slowed models down.

    I'd appreciate any guidance.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help intepreting an array formula please (2002)

    Before we start analyzing the formula, it's useful to know that in Excel, the logical value FALSE is equal to the numeric value 0, and the logical value TRUE is equal to the numeric value 1.
    Let's look at the formula in cell F9, the one that contains 1 in your example. This is the formula for the year 2011.
    <code>
    =SUM((B8:E8=(F7-B7:E7))*1)
    </code>
    Since it is an array formula, the expression
    <code>
    B8:E8=(F7-B7:E7)
    </code>
    will be evaluated for each cell in B8:E8 and the corresponding cell in B7:E7

    B8=F7-B7 evaluates to 0=2011-2007. This is FALSE
    C8=F7-C7 evaluates to 0=2011-2008. This is FALSE
    D8=F7-D7 evaluates to 2=2011-2009. This is TRUE because 2009 had a delay of 2 years, resulting in 2011.
    E8=F7-E7 evaluates to 0=2011-2010. This is FALSE

    These four TRUE/FALSE values are multiplied by 1 to force them to be seen as numbers. So we get

    0
    0
    1
    0

    Finally, the SUM function adds these four numbers, resulting in 1.

    In short, the formula counts the number of times the year 2011 is equal to a previous year plus the delay for that year.

    Does this help?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Re: Help intepreting an array formula please (2002)

    That is the clearest explanation I've seen by far. As always, Hans, you da man!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •