Formula Forensics-No. 036: Calculating Costs that Vary by Year and Age

Van Gysel asked in a recent post at Chandoo.org for a way to calculate the costs of running a plantation.  The twist is that the costs vary by year, and based on the age of the trees.

The following is a slightly simplified version of the solution I offered:

=SUM(IFERROR(LOOKUP(“Year”&MMULT(N($B$3:B$7>0),TRANSPOSE(COLUMN($B$3:B$7)^0)), $B$11:$I$11, $B12:$I12),0)*B$3:B$7)      Ctrl+Shift+Enter

Today I am going to try and explain how the formula works.

As always at Formula Forensics, you can follow along with a sample file: Download Here

The Problem

In a plantation, the costs for planting and maintaining trees vary based on the age of the trees and by year.  The table below shows the acres of trees planted per year and the yield and costs per year that vary based on the age of the trees.

Let us look at the calculations needed for each year.

Year 2013

• 300 acres of trees were planted in 2013.  Calculations for 2013 are as follows.  (Only Yield calculation is shown, but the process is similar for Nursery costs, Fertilizers, etc.)
• The trees do not yield any fruits in the first year.  As such, Yield for year1=300*0=0

That was easy!

 Year 2014

• 700 additional acres of trees will be planted in 2014.  Calculations for 2014 are as follows.  (Again, only Yield calculation is shown, but others are calculated similarly.)

• 300 acres of trees are 2 years old.  700 acres are 1 year old.
• The 300 acres from 2013 now yield fruit since it is year2.  However, the new trees (700 acres) do not yield any fruits yet.  So total yield for 2014=300*Year2Yield+700*Year1Yield=300*5+700*0

Year 2015

• 1000 additional acres are to be planted in 2015.  Calculations for 2015 are as follows:

• 300 acres are from 2013 (3 years old); 700 acres are from 2014 (2 years old); 1000 acres are from 2015 (1 year old).
• Yield for 2015=300*Year3Yield + 700*Year2Yield + 1000*Year1Yield = 300*10 + 700*5 + 1000*0

 Year 2016

• 1000 additional acres are to be planted in 2016.  Calculations for 2016 are as follows:

• Yield for 2016=300*15+700*10+1000*5+1000*0

How do we simulate the above calculation in an Excel formula?

A Solution

Let us first look at how we performed the calculations above manually, using the 2016 Yield as an example.

1. We took each acreage value in 2016, and determined its age by counting how many years it has been since that acreage was planted.  You might have observed that the age can be counted by the number of times a value has been repeated up to that point.  (In other words, if I planted 300 acres in 2013, I should see that same amount in 2014, 2015 and 2016.) As such, 300 acres is repeated 4 times.  700 acres is repeated 3 times.  1000 acres is repeated 2 times. And the latest planting of 1000 acres exists only once.
2. Once we determine the age for a given acreage, we looked up the yield for that age in the second table
3. We then multiplied the acreage with the corresponding yield value.

Calculation #1 can be expressed as follows:

• Age for acreage 1 (first planted in 2013)=count of B3:E3 where value is greater than zero.  i.e. COUNTIF(B3:E3,”>0”)
• Age for acreage 2 (planted in 2014)=count of B4:E4 where value is greater than zero.  i.e. COUNTIF(B4:E4,”>0”)
• Age for acreage 3 (planted in 2015)=count of B5:E5 where value is greater than zero.  i.e. COUNTIF(B5:E5,”>0”)
• Age for acreage 4 (planted in 2016)=count of B6:E6 where value is greater than zero.  i.e. COUNTIF(B6:E6,”>0”)
• Age for acreage 5 is zero since nothing has been planted for 2017 yet in 2016

The above approach would work if we were calculating the age one row at a time.  However, that can become tedious really fast.  We need to perform the calculation for the full range (B3:E7) together, but return the counts for each row individually.

Excel’s MMULT function comes to the rescue!

MMULT (which stands for Matrix Multiply) multiplies two matrices and returns a third matrix based on rules for matrix multiplication.  I am planning to devote a whole article to explain the MMULT function.  As such, for this article, we will summarize the utility of the function as “take a 2-dimensional array, add each column’s value for each row, and return a 1-column array”.

MMULT requires that its arguments be numeric.

So to obtain the counts for the year 2016, we can use the following:

MMULT(N($B$3:E$7>0),TRANSPOSE(COLUMN($B$3:E$7)^0))

As you can see from the picture below, MMULT’s results are the addition of each column for each row.

In the above formula, you may have noticed that the range uses absolute and relative referencing (signified by the $ sign or lack thereof).  This is to ensure that the range grows or shrinks as needed.  The upper left address is held constant ($B$3).  However, the lower right address for the range has columns that vary but row that is fixed on row #7.  This ensures that the formula would work if we copy to the left, right, etc. in the final results.

Now that we have the age for each acreage value, we can look up the corresponding yield value using (what else?) LOOKUP function.

But before we can use LOOKUP, we will need to convert the numeric values returned from MMULT into the strings Year1, Year2, etc. found in the Costs table.  Of course, you know how to do that… concatenate the string “Year” to the result from MMULT

“Year”&MMULT(N($B$3:E$7>0),TRANSPOSE(COLUMN($B$3:E$7)^0))

For the 2016 example, we get {“Year4″;”Year3″;”Year2″;”Year1″;”Year0”}

We can now use LOOKUP as follows:

LOOKUP(“Year”&MMULT(N($B$3:E$7>0),TRANSPOSE(COLUMN($B$3:E$7)^0)), $B$11:$I$11, $B12:$I12)

You may recall that LOOKUP looks up a value in the array indicated by the second argument, and returns the corresponding value from the third array argument.  In this case, instead of looking up a single value, we look up an array of values (supplied in the first argument) to the function.

The above formula translates to the following:

LOOKUP({“Year4″;”Year3″;”Year2″;”Year1″;”Year0”}, {“Year1″,”Year2″,”Year3″,”Year4″,”Year5″,”Year6″,”Year7″,”Year8”}, {0,5,10,15,20,25,30,35})

The result from LOOKUP is {15;10;5;0;#N/A}

(The last value is #N/A because there is no acreage value for 2017 yet (as of 2016 column).  The concatenation resulted in Year0 which does not exist in the “Age of The Trees” range (B11:I11) above.)

By using IFERROR(LOOKUP(…),0) we get {15;10;5;0;0}

We can now multiply the above result with the acreage values for 2016 to get {4500;7000;5000;0;0}

Finally, we SUM the values to get 16500

Putting it all together, we get the following formula (shown for Production for year 2016)

=SUM(IFERROR(LOOKUP(“Year”&MMULT(N($B$3:E$7>0),TRANSPOSE(COLUMN($B$3:E$7)^0)), $B$11:$I$11, $B12:$I12),0)*E$3:E$7)

One of the benefits of the above formula is that you can copy the same formula to calculate values for additional years, as well as other plantation costs.

Download

You can download a copy of the above file and follow along: Download sample file.

Let me know (using the comments below) what you think of the above approach and solution, as well as any other approaches you have utilized to solve a similar problem.  In the meantime, I wish you continued Excellence!

-Sajan.

 Other Posts in this Series

The Formula Forensics Series contains a wealth of useful solutions and information.

Visit the Formula Forensics Home Page to read other articles in this series.