Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
can i do this type of stacking column chart? (and how?)
as a non-mathematically-minded designer type, i could really use some
help! i need to create some stacking column charts whe - the value axis represents money, in a particular scale, in particular increments - the x axis represents different companies - each bar represents the MINIMUM, MIDPOINT and MAXIMUM amounts of money spent by each company, graphed against the value scale of money For example, say the scale is $1 to $100 in $10 increments. Company A spends a minimum of $5, a midpoint of $50 and a maximum of $70. I need the bar in the graph for company A to have three color segments, one of which goes from $0 to $5, the next from $5 to $50 and the last from $50 to $70, and then it stops. Same for Companies B, C, etc. Now, I know I cannot use the type of stacked bar chart where the values have to add up to 100%, as that is not the case in this scenario. Thus, I have used the OTHER stacked column chart type offered by Excel, which is just called Stacked Column. The problem is that it stacks the FULL AMOUNTS for each category (min, max, mid) on top of one another, instead of letting one pick up where the other leaves off. So, I get a chart, where, following the example I gave above, Company A has a column segment from $0-5, then from $5-55 (adding 50 to 5) and then from $55-75 (adding 20 to 55). So, looking at the chart, it looks like Company A's maximum is $75. At least, I'm pretty sure that's what's going on. Is there some way I can make these charts the first way I described instead? It's odd, because the first way described is the default for this chart type in Illustrator... but Illustrator is too much of a pain for this project. Hoping someone will reveal Excel's secrets to me... thanks! |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
can i do this type of stacking column chart? (and how?)
On Fri, 30 Mar 2007, in microsoft.public.excel.charting,
annie said: as a non-mathematically-minded designer type, i could really use some help! - each bar represents the MINIMUM, MIDPOINT and MAXIMUM amounts of money spent by each company, graphed against the value scale of money The problem is that it stacks the FULL AMOUNTS for each category (min, max, mid) on top of one another, instead of letting one pick up where the other leaves off. Is there some way I can make these charts the first way I described instead? It's odd, because the first way described is the default for this chart type in Illustrator... but Illustrator is too much of a pain for this project. Hoping someone will reveal Excel's secrets to me... thanks! Excel has no secrets. It's just a series of cells with numbers or formulas in them, and you instruct the cells to use the formulas that will calculate the numbers you want. So Company A spends a minimum of $5, a midpoint of $50 and a maximum of $70. You type in the three numbers: [A] [b] [C] [1] A Co. B Co. [2] min $5 $7 [3] mid $50 $49 [4] max $70 $71 Then below those three numbers, you type the following formulae: [A] [b] [C] [1] A Co. B Co. [2] min $5 $7 [3] mid $50 $49 [4] max $70 $71 [5] [6] A Co. B Co. [7] min =B2 =C2 [8] mid =B3-B2 =C3-C2 [9] max =B4-B3 =C4-C3 Now rows [6] to [9] look like this [A] [b] [C] [1] A Co. B Co. [2] min $5 $7 [3] mid $50 $49 [4] max $70 $71 [5] [6] A Co. B Co. [7] min $5 $7 [8] mid $45 $42 [9] max $20 $22 If you stack the numbers 5, 45, and 20 on top of each other, they'll show the bar you want. However, for you there is an even simpler way. I'm always lecturing people to use the spreadsheet and stop trying to do everything in the chart, but as long as all you want is three colors, you could simply use a *NON*-stacked bar chart, order the numbers from highest to lowest (instead of lowest to highest, as I showed above) [A] [b] [C] [1] A Co. B Co. [2] max $70 $71 [3] mid $50 $49 [4] min $5 $7 Now create your bar chart, then select one of the bars, Format Selected Object.. Options, then set the Overlap value to 100%. This turns the bars from side-by-side (overlap is zero) to right on top of each other (overlap is complete), but it does *not* stack them. So the lowest value hides the low part of higher values (this was why you had to type the columns upside down, otherwise the biggest column would be in front and would hide everything). But generally speaking, whenever you find yourself puzzled as to how to do something, don't look for special commands in the charting section, just calculate the numbers you want and let the spreadsheet do the arithmetic. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
can i do this type of stacking column chart? (and how?)
Brilliant.That makes complete sense. I will employ your method.
I did think, too, that the second chart type you describe would be simpler to implement, but this is part of a design project for a client and they specifically requested the former type of chart. Thank you very much for your help. annie |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
can i do this type of stacking column chart? (and how?)
On Fri, 30 Mar 2007, in microsoft.public.excel.charting,
annie said: I did think, too, that the second chart type you describe would be simpler to implement, but this is part of a design project for a client and they specifically requested the former type of chart. They'll look exactly the same. It's two routes to the same output, the only difference is in the input. Your clients may just have wanted the first way because they want to enter the differences and not the stacked totals in. In which case all my talk of subtracting formulas is redundant anyway. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stacking data from within a series within a bar chart | Charts and Charting in Excel | |||
stacking multiple column data into a single column | Excel Worksheet Functions | |||
how to incorporate a clustered stacking chart | Charts and Charting in Excel | |||
Selective stacking of columns in a chart | Excel Discussion (Misc queries) | |||
excel-stacking type within a cell-HOW?? | Excel Worksheet Functions |