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.
- 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.
- Once we determine the age for a given acreage, we looked up the yield for that age in the second table
- 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.
28 Responses to “2010 Calendar – Excel Template [Downloads]”
[...] Download and print the calendars today. You can add notes to individual dates or complete … [...] Uni Ego / Free 2010 Calendar – Download and Print Year 2010 Calendar today [...]
Any chance on a 2016 calendar?
Afternoon,
I have one similar calander that I added conditional formatting to so that I could highlight any planned factory holidays. I think i "borrowed" the formula from another calander so I won't post it here.
I also added week numbers to it using the formula =WEEKNUM(MAX(C6:I6)) Where C6:I6 is the range of dates in that give week. It works fine on most of the months but return strange values on other months (Week 6 in October?) I can't see any logic behind why it does this.
Any suggestions for an alternative formula to give the week numbers?
Regards,
William
Hi Chandoo,
I've added a new feature on your spreadsheet.
This control can be useful for all the sheets where you need to check dates.
Cheers
http://cid-69a78592a23a8438.skydrive.live.com/self.aspx/.Public/2010-calendar%5E_Miguel.xls
Hi Chandoo,
Nice calendar.
Till now whichever calendar I saw in Excel, it contained only the outline sheet.
Good to see monthly views and the mini view too.
Liked the mini view much. 🙂
-Nimesh
@William: This weeknum may be because the input dates to max are not properly formatting as excel dates.
Good tip on the conditional formatting and holidays btw...
@Migueal: Now that is super awesome. This is the reason why I love to blog. Readers will always one up me with such cool alternatives. Thank you for sharing this with us.
@Nimesh: You are welcome 🙂
is it possible to get the Notes section on the outline page to display the notes added to the month page for a specific date?
So if you add thing for January 2nd, and then select January 2nd those notes appear on the outline page
@Shish... You can do that using some formula magic. I would not recommend pushing excel to that as outlook / google calendar / icalc etc. do exactly that much more elegantly.
[...] Perpetual Excel Calendar – Free Downloadable Template [...]
Happy christmas to all of you!
This is really awesome. The nicest calender I've seen for Excel. I also like Miguels version of the sheet.
Just one "feature" is missing to me. As I live in Germany - where weeks start on Monday - I'd like to change this. Could someone please give me a hint how to do this?
Thanks in advance
Jörg
Hi Chandoo, I’ve added some new features on your spreadsheet with your permission.
Check it here:
http://cid-6b219f16da7128e3.skydrive.live.com/self.aspx/.Public/Calendar%5E_Pedro.xlsm
Miguel, this calendar is translated to Spanish language.
Jörg, this new approach allows us to start weeks on Monday.
Also it's possible to start weeks on Sunday if you enable Excel macros and push the arrows.
Best Regards,
Pedro.
@Pedro.. superb stuff.. thanks for sharing the file with all of us.
Hi Chandoo, for dates before March 1, 1900 our calendars are wrong.
In Microsoft Excel, DATE, EOMONTH, WEEKDAY functions return an incorrect result between Monday, January 1, 1900 and Wednesday, February 28, 1900.
See this page: http://support.microsoft.com/kb/214326/en-us/
Microsoft Excel incorrectly assumes that the year 1900 is a leap year in all Excel versions.
That's the reason why our calendar versions only work from March, 1, 1900 until December, 31, 9999.
Your comments are welcome.
Pedro.
@Pedro.. Thanks for pointing that out. wow... This reminds me of the Joel Spolsky's first BillG review - http://www.joelonsoftware.com/items/2006/06/16.html (read it, I am sure you would love it.) when Bill out of blue asks about date time implementations for VBA (which Joel is the program manager for...)
Thanks for sharing the URL too... Here is a specially made, chocolate sprinkled, extra fluffy donut for you 🙂
Hi Chandoo, thanks a lot for the donut but I prefer it without chocolate!
Always it's good to know a little history of Excel.
The Joel Spolsky’s last BillG Excel review was about the "Hall of Tortured Souls"
(See this Excel 95 Easter Egg here: http://www.eeggs.com/items/719.html)
Do not miss the humor!
@Chandoo.. I just return with a new calendar version.
http://cid-6b219f16da7128e3.skydrive.live.com/self.aspx/.Public/calendar-pedrowave.xltx
It helped me to practice conditional formatting, formulas to show check boxes, data validation drop down list, find out Thanksgiving Day's date for any year, how to find dates of public holidays using Excel, all reading your wonderful posts!
Perpetual Calendar Spanish version starting weeks on Monday:
http://cid-6b219f16da7128e3.skydrive.live.com/self.aspx/.Public/calendario-pedrowave.xltx
Main characteristics:
- Not macros.
- Select a year from 1900 to 9999 with a dropdown listbox.
- All date fields with the real date format.
- Easy language change of day of the week and month names because are also dates.
- Hide Saturdays and/or Sundays.
- Week starting on Sunday or Monday.
- Week and month numbers.
- Hyperlink between sheets.
- Consistent colors to Holidays, Diary and Events dates.
- Easy change of Holidays by country.
- Include 80 World Days and you can add more.
- A diary with my birthday and 50 more programable appointments.
- Check box to hide individual dates or all.
- Holidays, diary and events text are showed on each month's sheet.
- Ranges defined with Name Manager variables.
I'll appreciate if you make me some suggestions to improve this calendar.
Pedro.
Please, I need help!
I like all calendar from Pedro, thank you for them. Let me show my problem:
I have 2 excel cells (for example AE12 and AE13) which mean the starting and the ending date of my duty. I need a macro to insert sheets with label YEAR. MONTH (for example 2010. August or similar) with the proper datas between the two dates. Is it possible?
Thank you for reading me and sorry about my terribel english! 🙂
Hello Pedro,
Thanks so much for the modified calendar template. I love the extra functionality you added. Is there any way you could upload an unlocked version? I wanted to change some of the comments and data validation so I could use it for one of my applications.
As for feedback on potential improvements, with all the additions you made the file runs pretty slow. I'm sure this has to do with all the interconnectivity between the various tabs, but if there is a way to use less memory via more efficient formulas or something else I think this would make it easier to use. I have a brand new computer and with it running alone the response was pretty slow. One of the changes I'm making is changing the order of the months to match my company's fiscal year, so maybe something to automate a change like that could be useful.
Cheers,
Peter
Peter, my calendars are unlocked but you need Excel 2007 and 2010 versions to open them.
Now I return with a new Programmable Task Calendar:
http://cid-6b219f16da7128e3.office.live.com/view.aspx/.Public/Calendario%20de%20Tareas.xlsx
Wath an introductory video here:
http://pedrowave.blogspot.com/2010/10/programmable-task-calendar.html
This new calendar allows to select the start month to match the school and fiscal year.
This is great stuff Chandoo and company
Wanted to know if someone had built something similar
I need to store one Excel Sheet on this calendar that has all the holidays
US Holidays appear in RED
UK Holidays appear in Blue
Meetings appear in Green
Submissions appear in Orange
Is there a way I can store the list in a separate worksheet and all the calendars get updated with this?
Thanks
[...] Calendars: Year 2010 Excel Calendar | Year 2009 Excel [...]
please tell me "how to convert Rs.10000/- in to words through excel formula
[...] is all! http://chandoo.org/wp/2009/12/11/2010-calendar-excel-template-downloads/ See more Templates at http://www.vertex42.com/ Share this:Like this:LikeBe the first to like this [...]
Greetings,
Thanks for this wonderful excel vacation tracker. I notice that the tracker only has three months November, December and January 2015, however, I would like to add the other ten months for 2014. Can you please instruct me on how I can add the other months?
Thanking you in advance.
Hi Chandoo,
Calendar: can this be printed as single sheet 8.5x11 inch per month
kanu
@Kanu
Yes,
You can resize it to fit
WOW! I just searching some of like this, that help me.
Thank you for sharing.