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.
49 Responses to “Interactive Pivot Table Calendar & Chart in Excel!”
Excellent post again from awesome chandoo.org
This is one of the post to evident, without using macros we can create excellent charts using available excel options.
Slicer is one of the useful option in excel 2010 .. excited to see more options in excel 2013.
Regards,
Saran
http://www.lostinexcel.blogspot.com
Nice one chandoo............... great work done.....
Cool article. Only downside was that I didn't see at first that I needed 2010. Guess I still have to wait awhile before getting to try this out myself.
I consider myself an Excel expert, but you constantly amaze me with posts like this. Fantastic calendar!
Good post, like this little trick!!
How to not show the value in the cell
format the cell to custom with the below
;;;
Could you add lists of holidays to be transferred to the calendar days?
Two lists would be needed: 1) for the holidays that stay fixed (eg, CHristmas), and 2) for the holidays that move around (eg, Thanksgiving).
Such lists would be prepared externally, and the program would transfer their information to the appropriate days.
Wow! This is something amazing. I am going to do some practicals with this and show a sales trend on this. As we have our sales plans weekly basis, this should impress by boss when put in dashboard. Cool.
And thanks1
Chandoo you have a knack of getting on to these great looking very creative ideas!
One thing with calendars I have seen before is not catering for able to enter notes or appointments or project milestones. But with this one it's easy enough to add the extra lines as you have done for the chart concept and link to this other type of info.
For 2003 we could replace slicers with a validation style dropdown couldn't we?
Chandoo, you are awesome;) i was using calender to show my reports, but i had made all months and then underneith date shows the value, man its really awesome . i am going to use this format for my reports.. only draw back for me is i am using 2007. hence no slicer.. may be have to modify with out slicer.
Why not use =weeknum() for the weeknum column?
Great tricks! I love trying to reproduce the charts myself to get the hang of 'em. This one was great.
My only issue is getting the VBA in the year object to refresh the data. I used the VBA provided at the link, and, I can see it in the Macros tab, but, when I click the spinner the data does not update. Any tips?
Thx!
3G
^^Ignore this! NOT ENOUGH COFFEE YET.
I forgot about the "Assign Macro" option
:-s
Just started at chandoo - this is great!
I opted to use the formula =IF(F6>F5,G5,G5+1) for my weeknum - worked for me (I didn't get all the way through the example, since I'm running Excel 2007 - so don't know if that'll affect anything later in the example). I'm open to comments on this alternative approach.
Thanks for creating this website!
VC (Excel student).
Very cool - but now I'm even more excited for the new time controls for Excel 2013!
Great calendar...
I wonder whether we can make a school calendar (Class, subjects, teachers) using this calendar, assuming the weekly plan is duplicated across the year.
I would love to be a part of creating a class schedule...I'm attempting to help a friend (gratis) to do just that - can you point me in the right direction or provide a sample of sorts?
[...] Wow – what do you think of the interactive calendar chart demo above? To achieve this impressive effect you must have Excel 2010 because it utilises slicers, which is a feature introduced in Excel 2010. Find out how this treasure was created on Chandoo’s page. [...]
Hello Chandoo,
Great works! I learn a lot from this website. Here is the problem I met when I follow your tutorial: once I run and save this cool pivot calendar chart , the size of excel file will increase every time. Could you let me know how to figure it out? Thank you for your time in advance.
An excel chart-fan from China.
I already figured it out.
wow, love the calendar, i'm a newbie, found this site and it's amazing.
Got it mostly figured out, but could do with help with your named range 'tblchosen'
I can build the pivots, link the calendars together but can't see how to use index(tblchosen...) to pull through the productivity figures
appreciate any help
thanks
Great. Miss the Today button. Will try and figure a way to add this to the file.
I want to start the week on Monday, not Sunday (MTWTFSS). Re-arranging the calendar tab works however, any month where the 1st is a Sunday starts on the second and totally omits Sun 1. I have been tinkerign for a while, but can't seem to figure this out.
Changing F2 on the 'Calcs' tab to 2 so that the week starts on Monday works.
Cutting & pasting Sunday on the 'Pivot Calendar' tab and moving all cells up 1 row works.
However, using April 2013 for example, you lose the 1st off of the pivot calendar so that the month starts on 2 April. What should happen is the first row should only show Sun 1 April and then the next row starts Mon 2 April. Still can't fugure out where the problem lies.
"Further Enhancements:
Adjust week start to Monday: Likewise, you can modify your formulas to adjust weekstart to Monday or any other day you fancy."
I have tinkered with this previously with no success, does anyone know which formulas require tinkering, I have only succeeded in breaking this in an effort start a week on a Monday.
[...] Interactivo Artículo original var dd_offset_from_content = 50; var dd_top_offset_from_content = 0; Tags: 2013, calendario, [...]
Completely off topic, but how do you create those animated pictures in your tutorials? It is not a movie (like the Youtube movie), so what software do you use to create such high quality "animated" pictires? Thanks
Jeroen
the animated pics are called Animated Gifs
they are made using Camtasia
Refer: http://chandoo.org/wp/about/what-we-use/
This is fairly easy to do just using calendar formulas, which would be quicker, and doesn't need VBA? Am I missing something?
[...] on how to generate an interactive calendar using pivot tables. Please check out Chandoo’s Interactive Pivot Table Calendar & Chart in Excel before reading this, as I want to go through how I used his method to adapt a calendar which was [...]
Great tip shared by you... howevr would appreciate if you could mention in your tricks about excel version. The example above would work only in excel 2010 and above I believe. Please help me if there is any way we can use the tip in excel 2007 as well..
Many Thanks,
Regards,
FK
Hi, I'm going to give this a shot, but one small question before I do. Can a linked cell be updated based on the date that is selected from the calendar? The calendar is really cool and this would make is especially good to use (and easy and fast).
Regards,
swissfish.
This post is awesome, and using your instructions, I was able to get this to work with a pivot table that pulls directly from a Project Server database. It was a bit complicated to get the day to sum correctly, but I managed to finagle it. I hope you don't mind if I link back to you when I post my instructions.
Thanks for giving me a starting point for this!
[...] http://chandoo.org/wp/2012/09/12/interactive-pivot-calendar/ [...]
This is great, and pretty much everything I was looking for.
However, I already have a large spreadsheet, and I want to include your worksheets in it. I copied all the worksheets and the Module 1, but I can't get it to work. What else do I need to transfer / update please?
Hello there, is it possible to use this pivot to produce a calendar style chart, with returns multiple data per date, which on the calendar then, when clicked links to the data to provide more background information? What do you think? I'd love if I could pivot when i need. thanks, m
Hi, did you ever figure out how to do this? I would love to find a way...
This is amazing and will work well for my calendar project! My question is, how can I expand the calendar to fit a standard sheet of paper?
Wow - this is so creative. I'm taking the basic idea and building a reservation calendar.
Question: How do you get the month and year slicers on a different page than the pivot tables? I'd like to have my final calendar on a separate page from the pivot.
[…] http://chandoo.org/wp/2012/09/12/interactive-pivot-calendar/ […]
This is perfect...is there a way to add notes/tasks to the individual days?
Excel will not let me insert blank rows between lines in the pivot table. I am use Excel 2013 - is there a pivot table tools command that must be used?
I can create the pivot table calender with a year spinner & month slicer but I do not see how to display the the attendance information that I have in the original data table.
Thank you for the wonderful post and I am sorry for my lack of understanding...
Excellent!
Please show me how to add an alternative calendar to this calendar, Chinese or lunar calendar (and by lunar I don't mean phases of the moon), like what they still use in Asia
Thanks
Christopher
[…] Wow – what do you think of the interactive calendar chart demo above? To achieve this impressive effect you must have Excel 2010 because it utilises slicers, which is a feature introduced in Excel 2010. Find out how this treasure was created on Chandoo’s page. […]
Hello my name is Maurice, excuse me for my further request, but believe me, without your help priprio not know how to solve this problem.
So: always using a chart positioned on an excel sheet I wanted to match each square (series) to a single cell, to create a perpetual calendar.
Now everything works fine; except that for a fact, and it is this: In the calendar as you well know some numbers may not be apparent until certain conditions, which I solved by writing this "= O code (AA5 = DATE ( $ H $ 1; MONTH ($ AD $ 12) +1; 1)) and the game and done.
Now I would like to achieve the same thing using the Chart; How can I do to make this happen! let me also just a practical example so that I can understand all the rest then I'll do; Thanks Greetings from A.Maurizio
Link Program : Link: https://app.box.com/s/lhqva3eji0xcf2nmk8lxyki88tt1mi5t
Great info, thanks for sharing
Hi,
I love your calendar however I am modifying it for use in displaying employee performance metrics on a day by day basis.
I see where tblChosen and tblDates are named ranges however I cannot find them anywhere.
Are they assigned to specific cells because I cannot tell.
I see both of them in the Name Manager, which tells me what they refer to but does not give a value or cell location.
@Mike
With the Names in the Name Manager
Simply select the name
Then click in the Refers To: box at the Bottom
Excel will take you to where the Named Range is referring to
[…] Wow – what do you think of the interactive calendar chart demo above? To achieve this impressive effect you must have Excel 2010 because it utilises slicers, which is a feature introduced in Excel 2010. Find out how this treasure was created on Chandoo’s page. […]
Hi, Chandoo
This Pivot Calendar is an excellent idea. I’ve done one for myself using your guidelines. I just need something I’m not being able to do. I need that when I open the file the default date is set to today’s date. I know how to do it with conditional formatting. But I think I’ll need some vba coding for this. Can you please help me with this. Thanks in advance