It is a nice idea in my opinion: the largest value of each series first in the stacked column and so on. But my solution so far, is not a complete success yet, I think. Will keep working on it. Just saw Jon Peltier came up with a comparable idea http://peltiertech.com/WordPress/stack-columns-in-order-of-size/ but that one is not working with duplets or triplets.
Found a working one which I presented on sheet1. Look there for the formulas. This one is working fine with duplets and triplets.
The one on sheet2 has flaws: it is not working with duplets and triplets.
The orginal graph looks like this:
Here are my starting data, from A1:D15:
| Year | Apples | Pears | Bananas |
| 2000 | 200 | 250 | 300 |
| 2001 | 100 | 431 | 250 |
| 2002 | 300 | 345 | 560 |
| 2003 | 200 | 531 | 345 |
| 2004 | 400 | 134 | 431 |
| 2005 | 600 | 200 | 569 |
| 2006 | 300 | 100 | 531 |
| 2007 | 250 | 300 | 134 |
| 2008 | 560 | 200 | 220 |
| 2009 | 345 | 400 | 100 |
| 2010 | 431 | 600 | 300 |
| 2011 | 569 | 300 | 200 |
| 2012 | 531 | 250 | 400 |
| 2013 | 134 | 560 | 600 |
Formulas:
E2 =IF(AND(B2>C2;B2>D2);B2;"")
F2 =IF(AND(C2>B2;C2>D2);C2;"")
G2: =IF(AND(D2>C2;D2>B2);D2;"")
K2: =IF(AND(C2>B2;D2>B2);B2;"")
L2: =IF(AND(D2>C2;B2>C2);C2;"")
M2: =IF(AND(B2>D2;C2>D2);D2;"")
And in the middle:
H2: = IF(AND(E2="";K2="");B2;"")
I2: =IF(AND(F2="";L2="");C2;"")
J2: =IF(AND(G2="";M2="");D2;"")
So actually, in the graph there are nine column series. The apples I all made red; the pears green and the bananas blue. When you add the data labels, you have to get rid of 0. Yuo can do so by using the format code:
#.##0;-#.##0;;
You can also download the file largestfirst.zip through this link:
https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing
https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing
Reacties