vrijdag 15 november 2013

Excel: Again Switching Between Graphs, Together or Apart?

Just to visualize what I mean I will show the two charts:


and the second one:


But there is something weird about these charts. When you look at select data in the first case you get:


In the second case we see two more columns:


I will explain why this happens.

To make it possible to switch between to chart, I used this dataset, from A1:D11:

Years Column1 Column2 Column3
2001 121 234 189
2002 234 453 392
2003 123 324 140
2004 543 189 121
2005 234 392 234
2006 453 140 123
2007 324 121 543
2008 189 234 256
2009 392 123 453
2010 140 543 324

Next I created several names, to make it all work:

all =TRANSPOSE(Blad3!$B$2:$D$11)*1
allFirst =IF(Blad3!$H$2=0;(Blad3!all);Blad3!first)
allSecond =IF(Blad3!$H$2=0;Blad3!$H$3;Blad3!second)
allThird =IF(Blad3!$H$2=0;Blad3!$H$3;Blad3!third)
first =Blad3!$B$2:$B$11
labels =IF(Blad3!$H$2=0;(Blad3!labelsfirst;Blad3!labelsfirst;Blad3!labelsfirst);Blad3!labelsfirst)
labelsfirst =Blad3!$A$2:$A$11
second =Blad3!$C$2:$C$11
third =Blad3!$D$2:$D$11

=TRANSPOSE(Blad3!$B$2:$D$11)*1

Here transposing is needed to show the data in the right direction, not from left to right, but from top to bottom. Multiplying by 1 is needed to transform the range in to one series.

In cell G2 you can switch between Apart and Together. In cell H2 the choice will result in 1 or 0. and then hid column H. This will have consequences: When we pick Apart, allSecond and allThird will refer to column H, which is not visible, and disappear from the chart.

After choosing Together in cell G2, I created a graph based on columns A, B, C and D. Then made series refer to respecitively:

=Blad3!allFirst
=Blad3!allSecond
=Blad3!allThird

The horizontal axis labels I refer to:

=Blad3!labels

You can download the file ExcelApartTogetherII.zip through this link:

Een reactie posten