dinsdag 29 oktober 2013

Excel: How to Dynamically Compare Different Ranges With Different Starting Point

Look at the X-axis of this graph:


In the example you can use scroll bars to manipulate how to compare the three series.

The data from A1:F31:

Apples Pears Banana's
A 2001 50
2002 100
2003 150
2004 200
2005 100
2006 300
2007 250
2008 400
2009 430
2010 250
P 2001 123
2002 100
2003 150
2004 543
2005 250
2006 300
2007 54
2008 251
2009 234
2010 98
B 2001 87
2002 100
2003 234
2004 543
2005 345
2006 456
2007 350
2008 400
2009 389
2010 123

I created 7 names:

apples =OFFSET(Blad1!$D$2;0;0;Blad1!$I$1;1)
bananas =OFFSET(Blad1!$F$2;Blad1!$H$1;0;Blad1!$I$1;1)
labels1 =Blad1!$C$2:$C$30
labels2 =OFFSET(Blad1!$B$2;Blad1!$G$1;0;30;1)
labels3 =OFFSET(Blad1!$A$2;Blad1!$H$1;0;30;1)
labelsall =Blad1!labels1 & CHAR(13)  &  Blad1!labels2 &CHAR(13) &  Blad1!labels3
pears =OFFSET(Blad1!$E$2;Blad1!$G$1;0;Blad1!$I$1;1)

The cells G1, H1 and I1 are the outcome of scroll bars.

The series in the chart are based on:

=Blad1!apples
=Blad1!bananas
=Blad1!pears

And the labels are based on:

=Blad1!labelsall

You can also download the file CompareYears.zip through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing
Een reactie posten