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:
Next I created several names, to make it all work:
=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
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:
Reacties