Two trendlines in one chart

[RAM]

Here's the scenario:

One chart that is linked to two out of three columns, one with the dates (A) and one (C) that shows the left over inventory in the storage after what has been signed out in the second (B) column.

The chart is updated weekly.

The chart has a trendline and is updated manually and is not dynamic, yet at least, but I will probably make it dynamic.

I want the chart to have two, not only one, trendlines. One that represent the actual trend when the inventory will run out and one that shows the trend the previous week.

It sounds simple. Two ranges would do it. One range that includes all sign-outs in column C and another one that shows the same minus one weeks inventory, but I don't get it right.

Desert Passage storage Inventory chart.xls
	A	B	C	D
2			20952
3	11/10	216	20736	630
4	11/11	216	20520	420
5	11/12	72	20448	210
6	11/13	144	20304	420
Data Trendline Chart

Thanks in advance

RAM

 

[tusharm]

{snip}

It sounds simple. Two ranges would do it. One range that includes all sign-outs in column C and another one that shows the same minus one weeks inventory, but I don't get it right.
{snip}

How so? What does "I don't get it right" mean? You have the right idea and it should work. Are you including the dates when you plot the 2nd series? And, correctly excluding the last date?

 

[RAM]

Thanks tusharm for your replay,

When I add the second series it takes over the first series and the first series is no longer displayed. I don't get two chart series or trendlines. I get one (of each). The trendline is series 1, but displays series 2's values. The chart line is series 2 and displays series 2's values. Then the ranges doesn't display anymore in the columns when I click on the chart. How will I then change the values without its getting too time consuming?

RAM

 

[tusharm]

Assuming you are adding the dates and the values, you are plotting the exact same data just one fewer element! Of course, it plots exactly on top of the first series.

To see this, double-click either of the series and from the Axis tab select Secondary axis. Now, you will see the 2 series shown to slightly different scale. If you don't change the scale of the secondary y axis so that you do.

 

[RAM]

Thanks tusharm,

It worked out.

RAM

 

[tusharm]

You are welcome.

 

[tusharm]

You are welcome.

 

[RAM]

Tusharm,

I hope you're still watching this topic. I got another question and you must be the right person to ask this question.

I was just about to make the chart dynamic, but am running into the problem that I have two date series and need the second series to display the full date column minus 7 days. As per above postings the second trend line displays past weeks trend.

I have these formulas, and I believe I got them from your Web site:

=OFFSET('Basic Range'!YValues,0,-1)
=OFFSET('Basic Range'!$B$5,0,0,COUNTA('Basic Range'!$B:$B)-1,1)

They would work if I had only one date series, but I have two. Can these formulas be tweaked for my purpose?

TIA

RAM

 

[tusharm]

For the other series you should create two more names each with 1 fewer cell.

YVals2 =OFFSET('Basic Range'!$B$5,0,0,COUNTA('Basic Range'!$B:$B)-2,1)
XVals2 =OFFSET('Basic Range'!YVals2,0,-1)

 

[RAM]

Your answer is untried as yet, but just to verify I have expressed myself correctly my sheet looks like this:

Book4
	A	B	C	D	E	F	G
1	Date		8oz IA	Tot		1/2 oz IA	Tot
2		IN	OUT	20952	IN	OUT	46200
3	11/14	100	216	20836		210	45990
4	11/15		72	20764		210	45780
5	11/16		0	20764		0	45780
6	11/17		144	20620		420	45360
7	11/18		144	20476		210	45150
8	11/19		288	20188		420	44730
9	11/20	210	72	20326	150	210	44670
10	11/21		72	20254		210	44460
11	11/22		216	20038		420	44040
12	11/23		360	19678		840	43200
13	11/24		144	19534		420	42780
14	11/25		72	19462		210	42570
15	11/26		648	18814		1260	41310
16	11/27	210	144	18880	420	420	41310
Sheet2

I said "two date series", but I should have said "two date ranges".

Present weeks trend include all dated cells and prior week’s trend includes all dated cells, minus 7 days.

And not to confuse more, I have two charts for the above worksheet, one for 8 oz and one for 1/2 oz, but they share the date column (Edit): in each chart.

Thank you.

RAM