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 |
Stacked bars with textlegends in each stacked bar
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 |
Stacked bars with textlegends in each stacked bar
On 12 Maj, 03:27, "Jon Peltier"
wrote: Here's a trick. make the chart, apply data labels, using the values option. |
Stacked bars with textlegends in each stacked bar
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 |
Stacked bars with textlegends in each stacked bar
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 |
Stacked bars with textlegends in each stacked bar
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 |
Stacked bars with textlegends in each stacked bar
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? |
Stacked bars with textlegends in each stacked bar
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? |
All times are GMT +1. The time now is 05:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com