Monthly values for selected year and Yearly average for previous years

[#1]

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

[sunny_talwar · Accepted solution]

Is this what you are looking to do?

If yes, then PFA.

Best,

S

[#3]

Thank you so very much. This is exactly what I needed.

[sunny_talwar]

Great, I am glad I was able to help.

Best,

S

[#5]

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.

[sunny_talwar]

Is this what you were looking for?

PFA, if it is.

Best,

Sunny !

[#7]

Exactly, you are the best.  Thank you so much for your prompt and accurate response.

[sunny_talwar]

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

[#9]

Yes, I was able to fix the formula to address that issue.  Thank you againl.

[sunny_talwar]

Awesome!!! Not a problem. Always trying to learn by helping.

Best,

Sunny