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

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...00906ACNJ.html
_______


"Leon" wrote in message
...
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

On 12 Maj, 03:27, "Jon Peltier"
wrote:
Here's a trick. make the chart, apply data labels, using the values option.

Leon -

I wrote it up for my blog:
http://peltiertech.com/WordPress/hid...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...00906ACNJ.html
_______


"Leon" wrote in message
...
On 12 Maj, 03:27, "Jon Peltier"
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" wrote in message

...



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

On 13 Maj, 17:40, "Jon Peltier"
wrote:
Leon -

I wrote it up for my blog:http://peltiertech.com/WordPress/hid...-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" wrote in message

...
On 12 Maj, 03:27, "Jon Peltier"
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" wrote in message

....

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

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...arts-by-value/
http://peltiertech.com/WordPress/vba...ategory-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...00906ACNJ.html
_______


"Leon" wrote in message
...
On 13 Maj, 17:40, "Jon Peltier"
wrote:
Leon -

I wrote it up for my
blog:http://peltiertech.com/WordPress/hid...-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" wrote in message

...
On 12 Maj, 03:27, "Jon Peltier"
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" wrote in message

...

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

On 14 Maj, 02:36, "Jon Peltier"
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...ting-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" wrote in message

...
On 13 Maj, 17:40, "Jon Peltier"
wrote:





Leon -

I wrote it up for my
blog:http://peltiertech.com/WordPress/hid...-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" wrote in message

...
On 12 Maj, 03:27, "Jon Peltier"
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" wrote in message

....

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?

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...00906ACNJ.html
_______


"Leon" wrote in message
...
On 14 Maj, 02:36, "Jon Peltier"
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...ting-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" wrote in message

...
On 13 Maj, 17:40, "Jon Peltier"
wrote:





Leon -

I wrote it up for my
blog:http://peltiertech.com/WordPress/hid...-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" wrote in message

...
On 12 Maj, 03:27, "Jon Peltier"
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" wrote in message

...

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?