1. ## 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.

2. ## 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. ## 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
•