Results 1 to 3 of 3

20070526, 13:52 #1
 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=(D7B7: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, postdelay production time series.)
I'd love to understand how the array formula works, because it seems potent. It enables me to do timeshifting which used to require a lot of extra logic  especially in a monte carlo simulation, where potentially any combination of consecutive years of predelay 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.

20070526, 14:10 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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=(F7B7:E7))*1)
</code>
Since it is an array formula, the expression
<code>
B8:E8=(F7B7:E7)
</code>
will be evaluated for each cell in B8:E8 and the corresponding cell in B7:E7
B8=F7B7 evaluates to 0=20112007. This is FALSE
C8=F7C7 evaluates to 0=20112008. This is FALSE
D8=F7D7 evaluates to 2=20112009. This is TRUE because 2009 had a delay of 2 years, resulting in 2011.
E8=F7E7 evaluates to 0=20112010. 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?

20070526, 14:24 #3
 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!