Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to plot the monthly cost values depending on what year the user selects and show the Yearly Avg for all the previous years. So if the user selects 2014, I need the chart to show 2012 Avg Cost (61.5) as the first bar, 2013 Avg Cost (40.75) as the second bar and then each monthly value for 2014
The data is as follows
Date | Cost | Year |
Jan-12 | 50 | 2012 |
Feb-12 | 30 | 2012 |
Mar-12 | 20 | 2012 |
Apr-12 | 10 | 2012 |
May-12 | 50 | 2012 |
Jun-12 | 90 | 2012 |
Jul-12 | 80 | 2012 |
Aug-12 | 50 | 2012 |
Sep-12 | 145 | 2012 |
Oct-12 | 125 | 2012 |
Nov-12 | 36 | 2012 |
Dec-12 | 52 | 2012 |
Jan-13 | 52 | 2013 |
Feb-13 | 89 | 2013 |
Mar-13 | 26 | 2013 |
Apr-13 | 15 | 2013 |
May-13 | 18 | 2013 |
Jun-13 | 36 | 2013 |
Jul-13 | 57 | 2013 |
Aug-13 | 95 | 2013 |
Sep-13 | 21 | 2013 |
Oct-13 | 20 | 2013 |
Nov-13 | 15 | 2013 |
Dec-13 | 45 | 2013 |
Jan-14 | 78 | 2014 |
Feb-14 | 96 | 2014 |
Mar-14 | 58 | 2014 |
Apr-14 | 42 | 2014 |
May-14 | 53 | 2014 |
Jun-14 | 62 | 2014 |
Is this what you are looking to do?
If yes, then PFA.
Best,
S
Thank you so very much. This is exactly what I needed.
Great, I am glad I was able to help.
Best,
S
I now need to add two additional lines to the bar chart,
1) Goal Line: If I select 2015, then I need a goal line for all months in 2015 (straight line) at 67*0.9 =60.3 which is a 10% reduction from the previous year average
2) Year to Date Average for all values in 2015, so for Jan it would be 35, for Feb 26, March 24.7, April 34.3 etc.
Is this what you were looking for?
PFA, if it is.
Best,
Sunny !
Exactly, you are the best. Thank you so much for your prompt and accurate response.
Hahahaha, thanks! But as a note, I found out that the YTD Average line only works for year 2015 and 2014. before that it gives some weird results. If are going to be looking for values past 2013 (or last two years in your case) we will need to further tweak it.
Let me know what you think.
Best,
Sunny
Yes, I was able to fix the formula to address that issue. Thank you againl.
Awesome!!! Not a problem. Always trying to learn by helping.
Best,
Sunny