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