Inspired by the Dubliners (Wikipedia: http://en.wikipedia.org/wiki/The_Dubliners) I created a graph that reflects the years when the different members were part of the group.
It is all a bit complex. In the upper table part I use the number 1 to indicate whether a person was a member. Using conditional formatting I converted the 1 into just a green ball.
Below we see the graph. This graph looks like a clustered bar, but is actually a stacked column. Per year a stack of 1's is represented. This alone would not be enough, of course.
In the graph itself I therefore added ranges that contradict the ones of the members. If Ronnie Drew was no member in a specific year, the contradicting range would contain a 1 etc. Those contradicting ranges are based on names with formula's like:
=ABS(Blad2!$C$3:$BA$3-1)
In thee graph the added ranges then look like this:
The color of the name based contradicting ranges I put to white in the graph. So you can not directly see them. In short, every stacked column consists of the same total of white and colored blocks.
You can also download the file dubliners.zip through this link:
https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing
It is all a bit complex. In the upper table part I use the number 1 to indicate whether a person was a member. Using conditional formatting I converted the 1 into just a green ball.
Below we see the graph. This graph looks like a clustered bar, but is actually a stacked column. Per year a stack of 1's is represented. This alone would not be enough, of course.
In the graph itself I therefore added ranges that contradict the ones of the members. If Ronnie Drew was no member in a specific year, the contradicting range would contain a 1 etc. Those contradicting ranges are based on names with formula's like:
=ABS(Blad2!$C$3:$BA$3-1)
In thee graph the added ranges then look like this:
The color of the name based contradicting ranges I put to white in the graph. So you can not directly see them. In short, every stacked column consists of the same total of white and colored blocks.
You can also download the file dubliners.zip through this link:
https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing
Reacties