Add Date Line to Bar Chart

[south0085]

I have a basic bar chart. The x axis has years. From 2004 to 2017. The Y axis has qty shipped. (manufacturing plant)

I want to add a line in there to show the year that the model was discontinued. I would like to have that as a visual so that we can see how the shipments really drop off once the model goes into service production (meaning: after the end of it's regular life).

I hope I explained this correctly. I will gladly email my sheet to anyone, if needed.

Is there a dropbox or somewhere that I can drop my files, to show you guys?

Thank you for your time.

 

[PatOBrien198]

Can you add a second series with only one data point - the year the model was discontinued and a really high number for the qty shipped?

 

[south0085]

Can you add a second series with only one data point - the year the model was discontinued and a really high number for the qty shipped?

I actually thought about doing that. But I actually have lots of different models used in the data range. I have a drop-down list at the top of my sheet. I select a model and it displays that info on the graph. And each model has a different "end of life" date.

 

[south0085]

I figured out how to post a screenshot:

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
7	Selection	2
8
9		2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017
10	QW Service	273	3348	10433	10445	10099	7767	7874	9439	7799	7743	7955	6631	6544	3775
11
12
13	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
14
15	Program	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	EOL
16	QW Service	273	3348	10433	10445	10099	7767	7874	9439	7799	7743	7955	6631	6544	3775	2004
17	416A Service	0	0	0	0	0	0	0	0	2172	472	1428	2180	2632	1761	2012
18	642L Service	0	0	0	0	0	1040	2008	3147	3027	3015	2910	2308	1825	834	2011
19	397L Service	0	0	0	0	0	1435	2865	4981	7778	9993	11390	10517	8828	5502	2010
20	989A Service	0	0	0	0	0	0	0	0	0	0	0	1691	2599	5319	2013
21	635N Service	918	3828	4876	7133	9131	7148	8023	8271	7243	6647	6091	5168	4041	2752	2009
22	TK8X Service	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2016
Data

 

[PatOBrien198]

Try this... in cell C11

=IF(VLOOKUP($B10,$B$16:$Q$25,MATCH("EOL",$B$15:$W$15,0),FALSE)=C9,MAX($C$10:$P$10)+1000,"")

and copy this to all cells to the right.
add this row as a series to your chart.
what this formula does:
vlookup to find the Program name, then finds the year value in the EOL column,
compares this year to the year number in row 9 and
if it matches it puts a number that is 1000 more than the maximum number in row 10,
otherwise it puts in an empty string "".

 

[south0085]

Wow. I am very impressed, PatOBrien198. This is so cool. Thank you greatly!

 

[PatOBrien198]

Another option for sizing the value - instead of adding 1000 (which may make the value WAY bigger than some of the program maxes) would be to multiply by 1.1 (adding 10%). This might make the chart look better without having to fix the max axis value.

 

[PatOBrien198]

One last thing... I used the "Match" function, finding "EOL" so that if you need to insert another year's information, you won't need to modify the formula.

 

[south0085]

Another option for sizing the value - instead of adding 1000 (which may make the value WAY bigger than some of the program maxes) would be to multiply by 1.1 (adding 10%). This might make the chart look better without having to fix the max axis value.

Thank you. I changed it to add 10 percent. Great idea.