zaterdag 26 oktober 2013

Excel: Switching Between Graphs, Together or Apart?

Saw a graph like this on the internet. In it self, nothing special.


But in my opinion it would be more clear to group the subjects like this:


But both might be good alternatives. So why not give the viewer a choice. So I added a pick list with two choices: apart or together. So now you have both graphs in one.

Here are the data as I grouped them from A3:D33:

Town Haircut Half Gallon T-bone steak
Manhattan 22,21
Brooklyn
Honolulu 15,1
San Francisco 16,79
San Jose CA 14,13
Queens 17,79
Stamford CT 20,03
Washington 16,29
Orange County 15,57
Boston 15,29
Manhattan 2,34
Brooklyn 2,12
Honolulu 3,28
San Francisco 2,28
San Jose CA 2,05
Queens 2,58
Stamford CT 2,51
Washington 2,71
Orange County 2,24
Boston 2,72
Manhattan 15,52
Brooklyn 12,54
Honolulu 8,74
San Francisco 11,02
San Jose CA 10,89
Queens 11,48
Stamford CT 11,61
Washington 10,82
Orange County 9,58
Boston 11,16

From F3:I5 I typed:

B C
apart 0 0 30
together 10 20 10

Cell F1 refers to the list F4:F5.

From G1 til I1 I typed the formula's:

=VLOOKUP($F$1;$F$4:$I$5;2;FALSE)
=VLOOKUP($F$1;$F$4:$I$5;3;FALSE)
=VLOOKUP($F$1;$F$4:$I$5;4;FALSE)

There are three names. scope Blad1:

AA: =OFFSET(Blad1!$B$4;0;0;Blad1!$I$1;1)
BB: =OFFSET(Blad1!$C$4;Blad1!$G$1;0;Blad1!$I$1)
CC: =OFFSET(Blad1!$D$4;Blad1!$H$1;0;Blad1!$I$1)

I then created I bar chart of A3:D33. The series values I then changed to:

=Blad1!AA
=Blad1!BB
=Blad1!CC

And everything worked just fine.

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

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