Stacked bars with textlegends in each stacked bar

Hi Group

I wonder if this can be done.

Data like this

A | 0 | 3
B | 3 | 0
C | 2 | 3

1' column is legend

2' column is values to Y axis (this shoul have "1" (first stacked bar)
on the X axis)
3' column is values to Y axis (this shoul have "2" (second stacked
bar) on the X axis)

Here I would like to have 2 stacked bars
Each have legend inside bar IF value is more than 0

That would say in this case

        ___
 ___ |     |
 |     |  A |
 | B  |___|
 |___|     |
 |     | C  |
 | C  |     |

   1    2


Hobe I explained so that you guys undetstand :-)
cheers
0
5/11/2009 8:20:03 PM
excel.charting 18370 articles. 0 followers. Follow

7 Replies
467 Views

Similar Articles

[PageSpeed] 5

Here's a trick. make the chart, apply data labels, using the values option. 
Then apply custom number formats to the data labels. For series A use
"A";;;
for series B use
"B";;;
and for series C use
"C";;;
This uses the first item ("A" etc.) for a positive number, and "" (the space 
between semicolons) for negatives and zeros.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______


"Leon" <kim.zethsen@gmail.com> wrote in message 
news:fb0cc42e-1dc4-4e07-85ca-f602ceeb9f2b@u10g2000vbd.googlegroups.com...
> Hi Group
>
> I wonder if this can be done.
>
> Data like this
>
> A | 0 | 3
> B | 3 | 0
> C | 2 | 3
>
> 1' column is legend
>
> 2' column is values to Y axis (this shoul have "1" (first stacked bar)
> on the X axis)
> 3' column is values to Y axis (this shoul have "2" (second stacked
> bar) on the X axis)
>
> Here I would like to have 2 stacked bars
> Each have legend inside bar IF value is more than 0
>
> That would say in this case
>
>        ___
> ___ |     |
> |     |  A |
> | B  |___|
> |___|     |
> |     | C  |
> | C  |     |
>
>   1    2
>
>
> Hobe I explained so that you guys undetstand :-)
> cheers 


0
jonxlmvpNO (4558)
5/12/2009 1:27:05 AM
On 12 Maj, 03:27, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> Here's a trick. make the chart, apply data labels, using the values optio=
n.
> Then apply custom number formats to the data labels. For series A use
> "A";;;
> for series B use
> "B";;;
> and for series C use
> "C";;;
> This uses the first item ("A" etc.) for a positive number, and "" (the sp=
ace
> between semicolons) for negatives and zeros.
>
> - Jon
> -------
> Jon Peltier, Peltier Technical Services, Inc.http://PeltierTech.com/WordP=
ress/
> Advanced Excel Conference - June 17-18 2009 - Charting and Programminghtt=
p://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
> _______
>
> "Leon" <kim.zeth...@gmail.com> wrote in message
>
> news:fb0cc42e-1dc4-4e07-85ca-f602ceeb9f2b@u10g2000vbd.googlegroups.com...
>
>
>
> > Hi Group
>
> > I wonder if this can be done.
>
> > Data like this
>
> > A | 0 | 3
> > B | 3 | 0
> > C | 2 | 3
>
> > 1' column is legend
>
> > 2' column is values to Y axis (this shoul have "1" (first stacked bar)
> > on the X axis)
> > 3' column is values to Y axis (this shoul have "2" (second stacked
> > bar) on the X axis)
>
> > Here I would like to have 2 stacked bars
> > Each have legend inside bar IF value is more than 0
>
> > That would say in this case
>
> > =A0 =A0 =A0 =A0___
> > ___ | =A0 =A0 |
> > | =A0 =A0 | =A0A |
> > | B =A0|___|
> > |___| =A0 =A0 |
> > | =A0 =A0 | C =A0|
> > | C =A0| =A0 =A0 |
>
> > =A0 1 =A0 =A02
>
> > Hobe I explained so that you guys undetstand :-)
> > cheers- Skjul tekst i anf=F8rselstegn -
>
> - Vis tekst i anf=F8rselstegn -

Hi Jon

This sounds really great. I'll try at once.
Thaks a lot!
Cheers
0
5/13/2009 9:26:34 AM
Leon -

I wrote it up for my blog:
http://peltiertech.com/WordPress/hide-series-data-label-if-value-is-zero/

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/

Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______


"Leon" <kim.zethsen@gmail.com> wrote in message 
news:dcf67e4c-3c28-470c-a4a6-88e2ac0d1771@21g2000vbk.googlegroups.com...
On 12 Maj, 03:27, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> Here's a trick. make the chart, apply data labels, using the values 
> option.
> Then apply custom number formats to the data labels. For series A use
> "A";;;
> for series B use
> "B";;;
> and for series C use
> "C";;;
> This uses the first item ("A" etc.) for a positive number, and "" (the 
> space
> between semicolons) for negatives and zeros.
>
> - Jon
> -------
> Jon Peltier, Peltier Technical Services, 
> Inc.http://PeltierTech.com/WordPress/
> Advanced Excel Conference - June 17-18 2009 - Charting and 
> Programminghttp://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
> _______
>
> "Leon" <kim.zeth...@gmail.com> wrote in message
>
> news:fb0cc42e-1dc4-4e07-85ca-f602ceeb9f2b@u10g2000vbd.googlegroups.com...
>
>
>
> > Hi Group
>
> > I wonder if this can be done.
>
> > Data like this
>
> > A | 0 | 3
> > B | 3 | 0
> > C | 2 | 3
>
> > 1' column is legend
>
> > 2' column is values to Y axis (this shoul have "1" (first stacked bar)
> > on the X axis)
> > 3' column is values to Y axis (this shoul have "2" (second stacked
> > bar) on the X axis)
>
> > Here I would like to have 2 stacked bars
> > Each have legend inside bar IF value is more than 0
>
> > That would say in this case
>
> > ___
> > ___ | |
> > | | A |
> > | B |___|
> > |___| |
> > | | C |
> > | C | |
>
> > 1 2
>
> > Hobe I explained so that you guys undetstand :-)
> > cheers- Skjul tekst i anf�rselstegn -
>
> - Vis tekst i anf�rselstegn -

Hi Jon

This sounds really great. I'll try at once.
Thaks a lot!
Cheers 


0
jonxlmvpNO (4558)
5/13/2009 3:40:33 PM
On 13 Maj, 17:40, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> Leon -
>
> I wrote it up for my blog:http://peltiertech.com/WordPress/hide-series-da=
ta-label-if-value-is-z...
>
> - Jon
> -------
> Jon Peltier, Peltier Technical Services, Inc.http://PeltierTech.com/WordP=
ress/
>
> Advanced Excel Conference - June 17-18 2009 - Charting and Programminghtt=
p://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
> _______
>
> "Leon" <kim.zeth...@gmail.com> wrote in message
>
> news:dcf67e4c-3c28-470c-a4a6-88e2ac0d1771@21g2000vbk.googlegroups.com...
> On 12 Maj, 03:27, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> wrote:
>
>
>
>
>
> > Here's a trick. make the chart, apply data labels, using the values
> > option.
> > Then apply custom number formats to the data labels. For series A use
> > "A";;;
> > for series B use
> > "B";;;
> > and for series C use
> > "C";;;
> > This uses the first item ("A" etc.) for a positive number, and "" (the
> > space
> > between semicolons) for negatives and zeros.
>
> > - Jon
> > -------
> > Jon Peltier, Peltier Technical Services,
> > Inc.http://PeltierTech.com/WordPress/
> > Advanced Excel Conference - June 17-18 2009 - Charting and
> > Programminghttp://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200=
906ACNJ.html
> > _______
>
> > "Leon" <kim.zeth...@gmail.com> wrote in message
>
> >news:fb0cc42e-1dc4-4e07-85ca-f602ceeb9f2b@u10g2000vbd.googlegroups.com..=
..
>
> > > Hi Group
>
> > > I wonder if this can be done.
>
> > > Data like this
>
> > > A | 0 | 3
> > > B | 3 | 0
> > > C | 2 | 3
>
> > > 1' column is legend
>
> > > 2' column is values to Y axis (this shoul have "1" (first stacked bar=
)
> > > on the X axis)
> > > 3' column is values to Y axis (this shoul have "2" (second stacked
> > > bar) on the X axis)
>
> > > Here I would like to have 2 stacked bars
> > > Each have legend inside bar IF value is more than 0
>
> > > That would say in this case
>
> > > ___
> > > ___ | |
> > > | | A |
> > > | B |___|
> > > |___| |
> > > | | C |
> > > | C | |
>
> > > 1 2
>
> > > Hobe I explained so that you guys undetstand :-)
> > > cheers- Skjul tekst i anf=F8rselstegn -
>
> > - Vis tekst i anf=F8rselstegn -
>
> Hi Jon
>
> This sounds really great. I'll try at once.
> Thaks a lot!
> Cheers- Skjul tekst i anf=F8rselstegn -
>
> - Vis tekst i anf=F8rselstegn -

Hi Jon

I found your site :)
This is really great stuff

I can not hold back one more question :-)

A, B and C are some comodities
Each has a price, that varies, so that if sorted order is not the
same.
Each have an amount attached to it.

How do I show the right sort order (after price) in the stacked bar
and height after amount?
stacked bar number 2 as number 1, just -  here could sort order be
different so B should be in the buttom.

say in the first stacked bar A is cheapest and therefore I want to
show it in the buttom of the stacked bar.

The thing is that I want to have A, B and C sorted after price AND
show the height after amount og each.

Bar 1
C 2
B 4
A 5 (A lowest in bar and height is 5) - and text "A" is showed inside
bar as you just showed me

Bar 2
C 4
A 3
B 6 (B lowest in bar and height is 6)

Cheers
0
5/13/2009 9:08:55 PM
This gets complicated fast.

There are two approaches:

Approach the first:
Create N series (N=3 for A, B, and C), where N1 is always largest, N2 second 
largest, etc. This is in N columns.
Make another set of N columns that hold the labels of the corresponding 
commodity.
Use a VBA procedure that colors the data points based on the labels. You can 
use something like one of these as a starting point:

http://peltiertech.com/WordPress/vba-conditional-formatting-of-charts-by-value/
http://peltiertech.com/WordPress/vba-conditional-formatting-of-charts-by-category-label/

Approach the second:
Create a set of series that have either the appropriate amount or zero to 
make the values stack properly.

You need seven series for three commodities A B C. The first row shows one 
possible arrangement, and the rows below show the combinations of series 
with values and series with blanks (or zeros) to produce the 6 combinations 
of A, B, and C:

A C B A C B A
A _ B _ C _ _
A C B _ _ _ _
_ _ B A C _ _
_ _ B _ C _ A
_ C _ A _ B _
_ C B A _ _ _

Use formulas to apply the appropriate values or zeros to the seven series.

For example, if A is the largest value, the first series A has its value, 
the others have zero. If A is the medium value, the middle A series has the 
value of A and the others are zero. If A has the smallest of the three, then 
the last A series has the value.

Make the stacked column chart with all seven series, formatting all As the 
same, both Bs the same, and both Cs the same.

I would use the second approach, since it requires no VBA, though it's 
already complicated with three commodities, and I'm sure the number of 
series required goes up exponentially.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/

Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______


"Leon" <kim.zethsen@gmail.com> wrote in message 
news:4c5b4ce5-2e3a-4980-8eb2-9b216ddafc37@g20g2000vba.googlegroups.com...
On 13 Maj, 17:40, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> Leon -
>
> I wrote it up for my 
> blog:http://peltiertech.com/WordPress/hide-series-data-label-if-value-is-z...
>
> - Jon
> -------
> Jon Peltier, Peltier Technical Services, 
> Inc.http://PeltierTech.com/WordPress/
>
> Advanced Excel Conference - June 17-18 2009 - Charting and 
> Programminghttp://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
> _______
>
> "Leon" <kim.zeth...@gmail.com> wrote in message
>
> news:dcf67e4c-3c28-470c-a4a6-88e2ac0d1771@21g2000vbk.googlegroups.com...
> On 12 Maj, 03:27, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> wrote:
>
>
>
>
>
> > Here's a trick. make the chart, apply data labels, using the values
> > option.
> > Then apply custom number formats to the data labels. For series A use
> > "A";;;
> > for series B use
> > "B";;;
> > and for series C use
> > "C";;;
> > This uses the first item ("A" etc.) for a positive number, and "" (the
> > space
> > between semicolons) for negatives and zeros.
>
> > - Jon
> > -------
> > Jon Peltier, Peltier Technical Services,
> > Inc.http://PeltierTech.com/WordPress/
> > Advanced Excel Conference - June 17-18 2009 - Charting and
> > Programminghttp://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
> > _______
>
> > "Leon" <kim.zeth...@gmail.com> wrote in message
>
> >news:fb0cc42e-1dc4-4e07-85ca-f602ceeb9f2b@u10g2000vbd.googlegroups.com...
>
> > > Hi Group
>
> > > I wonder if this can be done.
>
> > > Data like this
>
> > > A | 0 | 3
> > > B | 3 | 0
> > > C | 2 | 3
>
> > > 1' column is legend
>
> > > 2' column is values to Y axis (this shoul have "1" (first stacked bar)
> > > on the X axis)
> > > 3' column is values to Y axis (this shoul have "2" (second stacked
> > > bar) on the X axis)
>
> > > Here I would like to have 2 stacked bars
> > > Each have legend inside bar IF value is more than 0
>
> > > That would say in this case
>
> > > ___
> > > ___ | |
> > > | | A |
> > > | B |___|
> > > |___| |
> > > | | C |
> > > | C | |
>
> > > 1 2
>
> > > Hobe I explained so that you guys undetstand :-)
> > > cheers- Skjul tekst i anf�rselstegn -
>
> > - Vis tekst i anf�rselstegn -
>
> Hi Jon
>
> This sounds really great. I'll try at once.
> Thaks a lot!
> Cheers- Skjul tekst i anf�rselstegn -
>
> - Vis tekst i anf�rselstegn -

Hi Jon

I found your site :)
This is really great stuff

I can not hold back one more question :-)

A, B and C are some comodities
Each has a price, that varies, so that if sorted order is not the
same.
Each have an amount attached to it.

How do I show the right sort order (after price) in the stacked bar
and height after amount?
stacked bar number 2 as number 1, just -  here could sort order be
different so B should be in the buttom.

say in the first stacked bar A is cheapest and therefore I want to
show it in the buttom of the stacked bar.

The thing is that I want to have A, B and C sorted after price AND
show the height after amount og each.

Bar 1
C 2
B 4
A 5 (A lowest in bar and height is 5) - and text "A" is showed inside
bar as you just showed me

Bar 2
C 4
A 3
B 6 (B lowest in bar and height is 6)

Cheers 


0
jonxlmvpNO (4558)
5/14/2009 12:36:07 AM
On 14 Maj, 02:36, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> This gets complicated fast.
>
> There are two approaches:
>
> Approach the first:
> Create N series (N=3D3 for A, B, and C), where N1 is always largest, N2 s=
econd
> largest, etc. This is in N columns.
> Make another set of N columns that hold the labels of the corresponding
> commodity.
> Use a VBA procedure that colors the data points based on the labels. You =
can
> use something like one of these as a starting point:
>
> http://peltiertech.com/WordPress/vba-conditional-formatting-of-charts...h=
ttp://peltiertech.com/WordPress/vba-conditional-formatting-of-charts...
>
> Approach the second:
> Create a set of series that have either the appropriate amount or zero to
> make the values stack properly.
>
> You need seven series for three commodities A B C. The first row shows on=
e
> possible arrangement, and the rows below show the combinations of series
> with values and series with blanks (or zeros) to produce the 6 combinatio=
ns
> of A, B, and C:
>
> A C B A C B A
> A _ B _ C _ _
> A C B _ _ _ _
> _ _ B A C _ _
> _ _ B _ C _ A
> _ C _ A _ B _
> _ C B A _ _ _
>
> Use formulas to apply the appropriate values or zeros to the seven series=
..
>
> For example, if A is the largest value, the first series A has its value,
> the others have zero. If A is the medium value, the middle A series has t=
he
> value of A and the others are zero. If A has the smallest of the three, t=
hen
> the last A series has the value.
>
> Make the stacked column chart with all seven series, formatting all As th=
e
> same, both Bs the same, and both Cs the same.
>
> I would use the second approach, since it requires no VBA, though it's
> already complicated with three commodities, and I'm sure the number of
> series required goes up exponentially.
>
> - Jon
> -------
> Jon Peltier, Peltier Technical Services, Inc.http://PeltierTech.com/WordP=
ress/
>
> Advanced Excel Conference - June 17-18 2009 - Charting and Programminghtt=
p://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
> _______
>
> "Leon" <kim.zeth...@gmail.com> wrote in message
>
> news:4c5b4ce5-2e3a-4980-8eb2-9b216ddafc37@g20g2000vba.googlegroups.com...
> On 13 Maj, 17:40, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> wrote:
>
>
>
>
>
> > Leon -
>
> > I wrote it up for my
> > blog:http://peltiertech.com/WordPress/hide-series-data-label-if-value-i=
s-z...
>
> > - Jon
> > -------
> > Jon Peltier, Peltier Technical Services,
> > Inc.http://PeltierTech.com/WordPress/
>
> > Advanced Excel Conference - June 17-18 2009 - Charting and
> > Programminghttp://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200=
906ACNJ.html
> > _______
>
> > "Leon" <kim.zeth...@gmail.com> wrote in message
>
> >news:dcf67e4c-3c28-470c-a4a6-88e2ac0d1771@21g2000vbk.googlegroups.com...
> > On 12 Maj, 03:27, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> > wrote:
>
> > > Here's a trick. make the chart, apply data labels, using the values
> > > option.
> > > Then apply custom number formats to the data labels. For series A use
> > > "A";;;
> > > for series B use
> > > "B";;;
> > > and for series C use
> > > "C";;;
> > > This uses the first item ("A" etc.) for a positive number, and "" (th=
e
> > > space
> > > between semicolons) for negatives and zeros.
>
> > > - Jon
> > > -------
> > > Jon Peltier, Peltier Technical Services,
> > > Inc.http://PeltierTech.com/WordPress/
> > > Advanced Excel Conference - June 17-18 2009 - Charting and
> > > Programminghttp://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf2=
00906ACNJ.html
> > > _______
>
> > > "Leon" <kim.zeth...@gmail.com> wrote in message
>
> > >news:fb0cc42e-1dc4-4e07-85ca-f602ceeb9f2b@u10g2000vbd.googlegroups.com=
....
>
> > > > Hi Group
>
> > > > I wonder if this can be done.
>
> > > > Data like this
>
> > > > A | 0 | 3
> > > > B | 3 | 0
> > > > C | 2 | 3
>
> > > > 1' column is legend
>
> > > > 2' column is values to Y axis (this shoul have "1" (first stacked b=
ar)
> > > > on the X axis)
> > > > 3' column is values to Y axis (this shoul have "2" (second stacked
> > > > bar) on the X axis)
>
> > > > Here I would like to have 2 stacked bars
> > > > Each have legend inside bar IF value is more than 0
>
> > > > That would say in this case
>
> > > > ___
> > > > ___ | |
> > > > | | A |
> > > > | B |___|
> > > > |___| |
> > > > | | C |
> > > > | C | |
>
> > > > 1 2
>
> > > > Hobe I explained so that you guys undetstand :-)
> > > > cheers- Skjul tekst i anf=F8rselstegn -
>
> > > - Vis tekst i anf=F8rselstegn -
>
> > Hi Jon
>
> > This sounds really great. I'll try at once.
> > Thaks a lot!
> > Cheers- Skjul tekst i anf=F8rselstegn -
>
> > - Vis tekst i anf=F8rselstegn -
>
> Hi Jon
>
> I found your site :)
> This is really great stuff
>
> I can not hold back one more question :-)
>
> A, B and C are some comodities
> Each has a price, that varies, so that if sorted order is not the
> same.
> Each have an amount attached to it.
>
> How do I show the right sort order (after price) in the stacked bar
> and height after amount?
> stacked bar number 2 as number 1, just - =A0here could sort order be
> different so B should be in the buttom.
>
> say in the first stacked bar A is cheapest and therefore I want to
> show it in the buttom of the stacked bar.
>
> The thing is that I want to have A, B and C sorted after price AND
> show the height after amount og each.
>
> Bar 1
> C 2
> B 4
> A 5 (A lowest in bar and height is 5) - and text "A" is showed inside
> bar as you just showed me
>
> Bar 2
> C 4
> A 3
> B 6 (B lowest in bar and height is 6)
>
> Cheers- Skjul tekst i anf=F8rselstegn -
>
> - Vis tekst i anf=F8rselstegn -


Yes this complicates things :-)

Is it possible on one single chart? With stacked bars?
0
5/15/2009 7:48:34 AM
Do us a favor and post on top, as is customary on this newsgroup.

What makes it complicated is the number of series which have to be sorted at 
each category. Complicated doesn't mean impossible, just impractical.

With three data series, it takes a total of (I think) seven chart series to 
do this with formulas. With four data series the number of chart series was 
about 12, but it was too complex to test all possibilities.

I started developing a blog post to describe how to handle this with VBA. 
It's a long post, and free time is scarce. Maybe I'll post it next week, 
maybe later.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/

Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______


"Leon" <kim.zethsen@gmail.com> wrote in message 
news:7b6ef770-5673-44d4-92cb-b8f5126836ec@r34g2000vbi.googlegroups.com...
On 14 Maj, 02:36, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> This gets complicated fast.
>
> There are two approaches:
>
> Approach the first:
> Create N series (N=3 for A, B, and C), where N1 is always largest, N2 
> second
> largest, etc. This is in N columns.
> Make another set of N columns that hold the labels of the corresponding
> commodity.
> Use a VBA procedure that colors the data points based on the labels. You 
> can
> use something like one of these as a starting point:
>
> http://peltiertech.com/WordPress/vba-conditional-formatting-of-charts...http://peltiertech.com/WordPress/vba-conditional-formatting-of-charts...
>
> Approach the second:
> Create a set of series that have either the appropriate amount or zero to
> make the values stack properly.
>
> You need seven series for three commodities A B C. The first row shows one
> possible arrangement, and the rows below show the combinations of series
> with values and series with blanks (or zeros) to produce the 6 
> combinations
> of A, B, and C:
>
> A C B A C B A
> A _ B _ C _ _
> A C B _ _ _ _
> _ _ B A C _ _
> _ _ B _ C _ A
> _ C _ A _ B _
> _ C B A _ _ _
>
> Use formulas to apply the appropriate values or zeros to the seven series.
>
> For example, if A is the largest value, the first series A has its value,
> the others have zero. If A is the medium value, the middle A series has 
> the
> value of A and the others are zero. If A has the smallest of the three, 
> then
> the last A series has the value.
>
> Make the stacked column chart with all seven series, formatting all As the
> same, both Bs the same, and both Cs the same.
>
> I would use the second approach, since it requires no VBA, though it's
> already complicated with three commodities, and I'm sure the number of
> series required goes up exponentially.
>
> - Jon
> -------
> Jon Peltier, Peltier Technical Services, 
> Inc.http://PeltierTech.com/WordPress/
>
> Advanced Excel Conference - June 17-18 2009 - Charting and 
> Programminghttp://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
> _______
>
> "Leon" <kim.zeth...@gmail.com> wrote in message
>
> news:4c5b4ce5-2e3a-4980-8eb2-9b216ddafc37@g20g2000vba.googlegroups.com...
> On 13 Maj, 17:40, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> wrote:
>
>
>
>
>
> > Leon -
>
> > I wrote it up for my
> > blog:http://peltiertech.com/WordPress/hide-series-data-label-if-value-is-z...
>
> > - Jon
> > -------
> > Jon Peltier, Peltier Technical Services,
> > Inc.http://PeltierTech.com/WordPress/
>
> > Advanced Excel Conference - June 17-18 2009 - Charting and
> > Programminghttp://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
> > _______
>
> > "Leon" <kim.zeth...@gmail.com> wrote in message
>
> >news:dcf67e4c-3c28-470c-a4a6-88e2ac0d1771@21g2000vbk.googlegroups.com...
> > On 12 Maj, 03:27, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> > wrote:
>
> > > Here's a trick. make the chart, apply data labels, using the values
> > > option.
> > > Then apply custom number formats to the data labels. For series A use
> > > "A";;;
> > > for series B use
> > > "B";;;
> > > and for series C use
> > > "C";;;
> > > This uses the first item ("A" etc.) for a positive number, and "" (the
> > > space
> > > between semicolons) for negatives and zeros.
>
> > > - Jon
> > > -------
> > > Jon Peltier, Peltier Technical Services,
> > > Inc.http://PeltierTech.com/WordPress/
> > > Advanced Excel Conference - June 17-18 2009 - Charting and
> > > Programminghttp://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
> > > _______
>
> > > "Leon" <kim.zeth...@gmail.com> wrote in message
>
> > >news:fb0cc42e-1dc4-4e07-85ca-f602ceeb9f2b@u10g2000vbd.googlegroups.com...
>
> > > > Hi Group
>
> > > > I wonder if this can be done.
>
> > > > Data like this
>
> > > > A | 0 | 3
> > > > B | 3 | 0
> > > > C | 2 | 3
>
> > > > 1' column is legend
>
> > > > 2' column is values to Y axis (this shoul have "1" (first stacked 
> > > > bar)
> > > > on the X axis)
> > > > 3' column is values to Y axis (this shoul have "2" (second stacked
> > > > bar) on the X axis)
>
> > > > Here I would like to have 2 stacked bars
> > > > Each have legend inside bar IF value is more than 0
>
> > > > That would say in this case
>
> > > > ___
> > > > ___ | |
> > > > | | A |
> > > > | B |___|
> > > > |___| |
> > > > | | C |
> > > > | C | |
>
> > > > 1 2
>
> > > > Hobe I explained so that you guys undetstand :-)
> > > > cheers- Skjul tekst i anf�rselstegn -
>
> > > - Vis tekst i anf�rselstegn -
>
> > Hi Jon
>
> > This sounds really great. I'll try at once.
> > Thaks a lot!
> > Cheers- Skjul tekst i anf�rselstegn -
>
> > - Vis tekst i anf�rselstegn -
>
> Hi Jon
>
> I found your site :)
> This is really great stuff
>
> I can not hold back one more question :-)
>
> A, B and C are some comodities
> Each has a price, that varies, so that if sorted order is not the
> same.
> Each have an amount attached to it.
>
> How do I show the right sort order (after price) in the stacked bar
> and height after amount?
> stacked bar number 2 as number 1, just - here could sort order be
> different so B should be in the buttom.
>
> say in the first stacked bar A is cheapest and therefore I want to
> show it in the buttom of the stacked bar.
>
> The thing is that I want to have A, B and C sorted after price AND
> show the height after amount og each.
>
> Bar 1
> C 2
> B 4
> A 5 (A lowest in bar and height is 5) - and text "A" is showed inside
> bar as you just showed me
>
> Bar 2
> C 4
> A 3
> B 6 (B lowest in bar and height is 6)
>
> Cheers- Skjul tekst i anf�rselstegn -
>
> - Vis tekst i anf�rselstegn -


Yes this complicates things :-)

Is it possible on one single chart? With stacked bars? 


0
jonxlmvpNO (4558)
5/15/2009 11:38:41 AM
Reply:

Similar Artilces:

disable yellow bar in outllk 2002
Is there any way to disable the yellow bar at the top of all the email, in Outlook 2002. I'm sure this bar is a plus for some. But I really don't care if there are extra lines in the mail that have removed. Thanks, David "David" <dredfearn@msn.com> wrote in message news:03ad01c356d4$1e323c10$a401280a@phx.gbl > Is there any way to disable the yellow bar at the top of > all the email, in Outlook 2002. > I'm sure this bar is a plus for some. But I really don't > care if there are extra lines in the mail that have > removed. > > Thanks, &g...

Word
Need the ability to change the thickness of change bars in Word. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=e7eb23b6-0e91-40b4-ab39-0c7b6b6edd31&dg=microsoft.public.word.docmanagement ...

Stacked bars with textlegends in each stacked bar
Hi Group I wonder if this can be done. Data like this A | 0 | 3 B | 3 | 0 C | 2 | 3 1' column is legend 2' column is values to Y axis (this shoul have "1" (first stacked bar) on the X axis) 3' column is values to Y axis (this shoul have "2" (second stacked bar) on the X axis) Here I would like to have 2 stacked bars Each have legend inside bar IF value is more than 0 That would say in this case ___ ___ | | | | A | | B |___| |___| | | | C | | C | | 1 2 Hobe I explained so that you guys undetstand :-) cheers ...

emailing link from Explorer tool bar
Prior to the installation of xp ( i used to use millenium) when I "send a link" from IE 6.0 it opens Outlook Express as it should but adds the url as an attachment rather than in the body of the email which I prefer. I can't find any setting adjustments on OE 6 or IE 6. Anyone with any ideas? This newsgroup is for support of Outlook 97-2003 from the Office family for Windows PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5...

on bar graph, how to superimpose line of average values over bars
i am creating a bar graph with death rates for 8 cities in los angeles for a particular disease. Now I would like to draw a constant line at a particular rate through the entire bar graph to indicate the target rate. Hi, Rather than drawing the line using shapes, which will be static, add an extra data series. Here are a few pages on the subject. http://peltiertech.com/Excel/Charts/AddLine.html http://tushar-mehta.com/excel/charts/straight_lines/index.html http://www.andypope.info/charts/averageline.htm http://www.processtrends.com/pg_charts_horizontal_line.htm Cheers Andy Kavitha wrot...

after scanning bar code without hitting enter or pressing OK butto
can anyone tell me how to scan a item bar code to tender without hitting enter or press ok button? Thanks shawn Shawn, That can cause problems if your customer decides to buy a second scanned item, but if you _really_ want to do it, change your scanner setup to add an <Enter> key after the scan. -- * Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not using a reader, follow the link below to setup Outlook Express. Click on "Open with newsreader" unde...

Stacked column with line chart
I have a chart which shows units gained above the axis, units disposed of in a column under the axis and net units as a line. I have trend lines on 3 months average for both the units gained and disposed of. I am now being asked to show units massed moved under the line stacked on the diposal units. How do I accomplish this? I looked for a custom graph in Excel and cannot find one to do what I want. Can anyone help me? --- Message posted from http://www.ExcelForum.com/ Draw your own custom chart. For an existing chart, right click on it, select Source Data from the pop up menu, and click...

Formula bar #2
I am using XL 97. i know that i can hide the cell entry by using the ;;; format, but the entry still displays in the formula bar. Is there a way to allow data entry in a cell, but have the entry not show in the formula bar. I want the formula bar itself to be visible. thanks Billy First make the cell invisible by using the ";;;" format. Then choose the "protection" tab in the same dialog and select "hidden" checkbox. You can uncheck the "Locked" checkbox if you want to allow the user to enter data in the cell. Then protect the sheet using Tools...

Attachment bar in outlook
I am trying to add an attachment bar in the new message window (below th esubject bar). I can only see this bar when replying to e-mails but not when composing new ones. Thanks ------------------------------------------------ ~~ Message posted from http://www.OutlookForum.com/ ~~ View and post usenet messages directly from http://www.OutlookForum.com/ it's presence is determined by the message format - use plain text or html, not RTF. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Outlook Tips: http://www.outlook-tips.net/ http://www.poremsky.com ...

Different Fill Area on Simple Bar
Windows XP Professional Office 2000 I have a simple bar graph that shows the number of times a specific "reason code" is used and only the top ten reason codes are shown on the graph. Now, any reason code can fall into one of two different buckets. I've been tasked with altering my graphs to not only show the number of occurrences for each reason code, but to change the fill area of the bars based on bucket assignment. I will leave one bucket property as default, but would like to change any reason code bar that falls into the second bucket to a different pattern. I...

Bar Chart Date XAxis Thin Bars
I have a bar chart with dates on the xaxis being resorted (primary axis = automatic). There are 26 entries over a one year period. The trouble is the bars come out as vertical lines, I can't set the xaxis format so that they have any thickness. If I set the chart to not sort the xaxis then the bars are okay. Your axis is set so the base unit is days. Each bar must then fit within a band 1/365th the width of the chart. If you change the base unit to months, you will have two ro sometimes three points occurring at the same month. You shoucl consider changing from time scale to cate...

Stack overflow question
Occassionally when on Facebookwith MSIE if I have 2 instance of Facebook open (Different pages) I will get an error message of "Stack Overflow at Line: 31". When I click OK the page finishes loading fine. Any suggestions as to what is going on? Running: <<< System Summary >>> > Manufacturer : eMachines Inc. > Mainboard : eMachines WMCP61M > Chipset : nVidia GeForce 6100V > Processor : AMD Athlon Neo MV 26 @ 1600 MHz > Physical Memory : 4096 MB (2 x 2048 DDR2-SDRAM ) > Video Card : NVIDIA GeForce 6150SE nForce 430 ...

Stacked charts #4
Hi I want to to create a chart where the colour changes when ever a value is reached. Ex. I have a mashine that produces 1000 it's goal is 2000. The chart should then show a column that is red because it is under the goal. I also have a maschine that produces 3000 and the goal is 2000. The coloum should then change to green. Is it possible to make a chart like this in excel? Regards Ticotion On Thu, 21 Jun 2007, in microsoft.public.excel.charting, Ticotion <Ticotion@discussions.microsoft.com> said: >I want to to create a chart where the colour changes when ever a value is &...

[GROUP] in title bar
Anyone know why the text [GROUP] is appearing in the blue title bar in Excel 2003? Gareth You must have your sheets (or some of them) grouped. Anything you do will affect all of the grouped sheets - so be careful. To ungroup, right-click on the tab of a grouped sheet and click Ungroup. Andy. "Gareth" <anonymous@discussions.microsoft.com> wrote in message news:091e01c3c32d$11be61a0$a501280a@phx.gbl... > Anyone know why the text [GROUP] is appearing in the blue > title bar in Excel 2003? You have selected multiple sheets, right click one of the sheet tabs that are w...

Controlling Line/Bar axis's
When using Line/Bar charts, is there a way to contol the series as to which one is used? If you have 4 or more series, sometimes I'd like to only have one Y axis. Same for using 6 or more series, the default is to divide the series in half, but I'd like to controll what series goes to either axis. Thanks, Randy Randy - Those built-in custom types seem convenient at first, don't they. Here's a better way, which takes a few more seconds, but gives you complete control. Make your chart with all the data showing up as one chart type (bar or line, use the one which you...

stack
Hi, I am writing a c application, but when I put a member inside a function I get stack overflow, the member is an array of struct: mytype tArray[30]; mytype is struct of this type: typedef struct{ int x[500]; int y[500]; long l;}mytype; what is the size of a c function stack? can I define the size of the stack? (I am using visual studio 6) Thanks. Dave wrote: > Hi, > I am writing a c application, but when I put a member inside a function I > get stack overflow, the member is an array of struct: mytype tArray[30]; > > mytype is struct of this type: typedef st...

Creating an IE style Menu bar using a hybrid api ocx
Is it possible to create an ie style Menu bar using a either a hybrid of api and common controls ocx or pure api? here is the code I have tried to use: You will need one common controls 5 ToolBar on the form. Option Explicit Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long Private Declare Function RegisterWindowMess...

Stacked Chart
Hey everyone, Does anyone know if there is a way to get a stacked chart side by side? For example, you have airplanes, cars and trains on the x axis and values of 2005, 2006, 2007 and 2008. I know you can have all these values in one bar but is it possible to have two stacked bars for one x-value? For example, for airplanes, you have one bar with 2005 and 2006 and a bar next to it with the 2007 and 2008 values. Thanks, appreciate your help. Try one or more of these links: http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html - Jon ------- Jon Peltier, Microsoft Excel MVP...

How do you make 3 stacked columns of info for each date in Excel
I need help. I am trying to chart 3 groups of information for several dates. I would like to have 3 stacked columns (percentages) with the dates at the top of the chart the names of the groups at bottom of the chart and the legend at the right of the chart. Does anyone have any secrets. I am able to get one groups info to chart but I am unable to get the other two groups in the same date without stacking their information on top of first group. ...

Stack Overflow, How to Monitor the Stack
I am trying to modify a large older program to handle a more data. I have been getting this error: Unhandled exception in RMG_graph.exe: 0cC00000FD: Stack Overflow I can't find any thing wrong in the various places where this error comes up. The debugger always seems to take me to the same spot in the file CHKSTK.ASM: probepages: sub ecx,_PAGESIZE_ ; yes, move down a page sub eax,_PAGESIZE_ ; adjust request and... test dword ptr [ecx],eax ; ...probe it <-<-<- cmp eax,_PAGESIZE_ ; more th...

bar code printer
i havea cognitive solutions advantage lx bar code printer. is there any way to make the font bigger. i am using a 1x2 label. thanks K.P.J. Yes, but I don't know that language... If you are using the cognitive windows driver, you can use the Label designer in RMS to graphically design the label and change the font. If you are (more likely) using the Generic/Text driver, you need to download the programming guide from Cognitive's website. It will tell you how to set the font size for a given line of output... Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@ti...

Menu bar not working (but visible)
I'm running Outlook 2003 on Win XP. I installed Office & Excel 2007 a few days ago (everything still worked in Outlook 2003) and this morning I installed Windows Updates. Outlook opens & I'm able to view, open and reply/fwd messages but I cannot click on the Menu bar (File, Edit, View, etc) or the icons below the menu bar (e.g. the ones for a new message, printing, reply, fwd, etc). Since I can't click 'File' or 'Tools' I can't change any of the settings. Thanks Locate outcmd.dat and rename it to .old when Outlook is closed. See htt...

Formula bar height in XL2K3 w/XP
With multi-line text (Alt-Enter) in the Formula Bar, it automagically extends the height of the bar. This interferes with column actions since the bar now obscures the column headings (A through ...). Painful when the 1st cell in a column is multi-line. Can this 'feature' be controlled? Controlled ... like toggle off and on? <Alt> + <V><F> OR <View> and check - uncheck <FormulaBar>. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ======================================...

Stacked Columns
Using Excel 2003. Have five stacked columns. Want to change order of only one stack; the only one that has more than two values in its stack. Was able to shift order of one of the two stack columns but the one with three values doesn't give me the same screen that permitted me to move the order up or down. Disregard! Found it! "jaygreg" <jaygreg90@hotmail.com> wrote in message news:e$eAOUTzHHA.1164@TK2MSFTNGP02.phx.gbl... > Using Excel 2003. Have five stacked columns. Want to change order of only > one stack; the only one that has more than two values in it...

Scroll Bar has Gone away
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel For some reason the scroll bar in Microsoft Word has gone away, I have search the help, the various menus and forums and cannot find anything that simply tells me how to get the scroll bar back. Any help would be appreciated. ...