Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
For Jon Peltier
Hi Jon,
I have posted tonight a couple of times re waterfall charts. I am blonde and exhausted, so maybe I don't get it... I have: Supplier Spent Supplier1 149 Supplier2 140 Supplier3 75 Supplier4 23 Supplier5 13 Supplier6 11 Supplier7 42 Total 453 and wish to create a chart that shows the first value (149) as a column beginning at 0. Then, the second column (140) beginning just above the first column and so on. At the far right, I wish to see the total column. I tried this (which I found at a Web site): Supplier Spent Invisible Supplier1 149 304 Supplier2 140 164 Supplier3 75 89 Supplier4 23 66 Supplier5 13 53 Supplier6 11 42 Supplier7 42 0 Total 453 But it didn't work... just kept my columns all starting at 0... What am I missing? I was also wondering if I could display both the value and the percentage of the total above each bar without using an add-in. Thanks so very much for any help you can provide. -- Thanks! Dee |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
For Jon Peltier
You were probably on the right track. I rearranged your columns and added
some. The following is in A1:F9 (read it with a nonproportional font, like Courier). Supplier Invisible Spent Total Cumulative Label Supplier1 0 149 149 149¶(32.9%) Supplier2 149 140 289 140¶(30.9%) Supplier3 289 75 364 75¶(16.6%) Supplier4 364 23 387 23¶(5.1%) Supplier5 387 13 400 13¶(2.9%) Supplier6 400 11 411 11¶(2.4%) Supplier7 411 42 453 42¶(9.3%) Total 453 453 453 Columns A and C are your original data. Cell B2 contains zero. Cell B3 contains the formula =SUM(C$2:C2) which is filled down to B8. Cell D9 contains this formula: "dee" wrote in message ... Hi Jon, I have posted tonight a couple of times re waterfall charts. I am blonde and exhausted, so maybe I don't get it... I have: Supplier Spent Supplier1 149 Supplier2 140 Supplier3 75 Supplier4 23 Supplier5 13 Supplier6 11 Supplier7 42 Total 453 and wish to create a chart that shows the first value (149) as a column beginning at 0. Then, the second column (140) beginning just above the first column and so on. At the far right, I wish to see the total column. I tried this (which I found at a Web site): Supplier Spent Invisible Supplier1 149 304 Supplier2 140 164 Supplier3 75 89 Supplier4 23 66 Supplier5 13 53 Supplier6 11 42 Supplier7 42 0 Total 453 But it didn't work... just kept my columns all starting at 0... What am I missing? I was also wondering if I could display both the value and the percentage of the total above each bar without using an add-in. Thanks so very much for any help you can provide. -- Thanks! Dee |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
For Jon Peltier
Dee -
You were probably on the right track. I rearranged your columns and added a few. The following is in A1:F9 (read it with a nonproportional font, like Courier). Supplier Invisible Spent Total Cumulative Label Supplier1 0 149 149 149¶(32.9%) Supplier2 149 140 289 140¶(30.9%) Supplier3 289 75 364 75¶(16.6%) Supplier4 364 23 387 23¶(5.1%) Supplier5 387 13 400 13¶(2.9%) Supplier6 400 11 411 11¶(2.4%) Supplier7 411 42 453 42¶(9.3%) Total 453 453 453 Columns A and C are your original data. Cell B2 contains zero. Cell B3 contains the formula =SUM(C$2:C2) which is filled down to B8. Cell D9 contains this formula: =SUM(C2:C8) Cell E2 contains this formula: =SUM(C$2:C2) (that's right, same as B3), which is filled down to E8. Cells E9 and F9 contain this formula: =D9 Cell F2 contains =C2&CHAR(10)&"("&TEXT(C2/$D$9,"0.0%")&")" which is filled down to F8. CHAR(10) produces a line feed, which I've represented as ¶ in my table above, but which appears as a line feed in the cell (if wrapping is allowed), or as a small rectangle, or sometimes as nothing. Select A1:E8 and create a stacked column chart. Select the Cumulative series, go to Chart menu Chart Type, and select a line chart. I usually tell people here to use one of these add-ins to add the labels from column F to the Cumulative series, using the 'above' alignment. You don't want to use an add-in, so here's the manual approach. 1. Double click the Cumulative series. 2. On the Data Labels tab, select any of the settings. 3. Double click the new labels. 4. On the Alignment tab, choose the Label Position 'Above'. 5. Select the first label (one click to select the series of labels, another click to select a single label). 6. Type = in the formula bar, and click on cell F2. 7. Repeat steps 5 and 6 for the rest of the labels, using the cells F3:F9 for the remaining labels. Now double click the Cumulative line chart series, and on the Patterns tab choose No Line and No Marker. Double click the Invisible series, and on the Patterns tab, choose No Border and No Area Fill to make it invisible. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "dee" wrote in message ... Hi Jon, I have posted tonight a couple of times re waterfall charts. I am blonde and exhausted, so maybe I don't get it... I have: Supplier Spent Supplier1 149 Supplier2 140 Supplier3 75 Supplier4 23 Supplier5 13 Supplier6 11 Supplier7 42 Total 453 and wish to create a chart that shows the first value (149) as a column beginning at 0. Then, the second column (140) beginning just above the first column and so on. At the far right, I wish to see the total column. I tried this (which I found at a Web site): Supplier Spent Invisible Supplier1 149 304 Supplier2 140 164 Supplier3 75 89 Supplier4 23 66 Supplier5 13 53 Supplier6 11 42 Supplier7 42 0 Total 453 But it didn't work... just kept my columns all starting at 0... What am I missing? I was also wondering if I could display both the value and the percentage of the total above each bar without using an add-in. Thanks so very much for any help you can provide. -- Thanks! Dee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding data labels to plotted quadrant (using Peltier solution) | Charts and Charting in Excel | |||
Attn John Peltier | Charts and Charting in Excel | |||
Jon Peltier Speedometer Chart | Excel Discussion (Misc queries) | |||
Jon Peltier - Pivot Table Result for yesterday's "Complex Chart" Question | Charts and Charting in Excel |