Home |
Search |
Today's Posts |
#1
|
|||
|
|||
up down bar value (difference)
Hiya,
I have a line chart with two lines. I have included down bars from the upper line. I would like the difference of value between the two lines (i.e. the height of the down bars) to be shown within the down bars. Is this possible? Thanks, Basil |
#2
|
|||
|
|||
one way to do it, (If I understand what you want) on the spread sheet
calculate the difference in say D1:D4 on the chart start a text box and in the formula bar enter = and point to the cell with the difference. format the text box for the alignment and fill colors you want and place it on the down bar for each data point pair. "Basil" wrote: Hiya, I have a line chart with two lines. I have included down bars from the upper line. I would like the difference of value between the two lines (i.e. the height of the down bars) to be shown within the down bars. Is this possible? Thanks, Basil |
#3
|
|||
|
|||
I was looking for a way that didn't rely on seperate text boxes.
"bj" wrote: one way to do it, (If I understand what you want) on the spread sheet calculate the difference in say D1:D4 on the chart start a text box and in the formula bar enter = and point to the cell with the difference. format the text box for the alignment and fill colors you want and place it on the down bar for each data point pair. "Basil" wrote: Hiya, I have a line chart with two lines. I have included down bars from the upper line. I would like the difference of value between the two lines (i.e. the height of the down bars) to be shown within the down bars. Is this possible? Thanks, Basil |
#4
|
|||
|
|||
another way would be to find the midpoint between each data pair
lable each midpoint data pair as the delta value plot this group of data as mutilple data series. select each one to show data lables for series lable and delete the legend for that series from the legend box. You can format the lables as to orientation veritical etc. (I really miss the old Data lables as assigned that used to be available. I have no idea why that was removed, Cause I used to use it A bunch) "Basil" wrote: I was looking for a way that didn't rely on seperate text boxes. "bj" wrote: one way to do it, (If I understand what you want) on the spread sheet calculate the difference in say D1:D4 on the chart start a text box and in the formula bar enter = and point to the cell with the difference. format the text box for the alignment and fill colors you want and place it on the down bar for each data point pair. "Basil" wrote: Hiya, I have a line chart with two lines. I have included down bars from the upper line. I would like the difference of value between the two lines (i.e. the height of the down bars) to be shown within the down bars. Is this possible? Thanks, Basil |
#5
|
|||
|
|||
Hi,
Have a look at this example I've put together. I combines both of bj's suggestions but only requires 1 extra data series. http://www.andypope.info/charts/Hilolabel.htm Cheers Andy Basil wrote: Hiya, I have a line chart with two lines. I have included down bars from the upper line. I would like the difference of value between the two lines (i.e. the height of the down bars) to be shown within the down bars. Is this possible? Thanks, Basil -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#6
|
|||
|
|||
(I really miss the old Data lables as assigned that used to be available. I
have no idea why that was removed, Cause I used to use it A bunch) The up-down bars have never had a labeling capability. The alternative approach is to build a floating column chart, and use the columns' data labels to display the difference data. The nice thing about a floating column chart is that the value is the difference between top and bottom, so simple data labels using the Show Value option will do the trick. Floating Column Charts: http://peltiertech.com/Excel/Charts/...ngColumns.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ bj wrote: another way would be to find the midpoint between each data pair lable each midpoint data pair as the delta value plot this group of data as mutilple data series. select each one to show data lables for series lable and delete the legend for that series from the legend box. You can format the lables as to orientation veritical etc. (I really miss the old Data lables as assigned that used to be available. I have no idea why that was removed, Cause I used to use it A bunch) "Basil" wrote: I was looking for a way that didn't rely on seperate text boxes. "bj" wrote: one way to do it, (If I understand what you want) on the spread sheet calculate the difference in say D1:D4 on the chart start a text box and in the formula bar enter = and point to the cell with the difference. format the text box for the alignment and fill colors you want and place it on the down bar for each data point pair. "Basil" wrote: Hiya, I have a line chart with two lines. I have included down bars from the upper line. I would like the difference of value between the two lines (i.e. the height of the down bars) to be shown within the down bars. Is this possible? Thanks, Basil |
#7
|
|||
|
|||
Hi Jon,
The column technique works great for positive values but not so for negative ones. Cheers Andy Jon Peltier wrote: (I really miss the old Data lables as assigned that used to be available. I have no idea why that was removed, Cause I used to use it A bunch) The up-down bars have never had a labeling capability. The alternative approach is to build a floating column chart, and use the columns' data labels to display the difference data. The nice thing about a floating column chart is that the value is the difference between top and bottom, so simple data labels using the Show Value option will do the trick. Floating Column Charts: http://peltiertech.com/Excel/Charts/...ngColumns.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ bj wrote: another way would be to find the midpoint between each data pair lable each midpoint data pair as the delta value plot this group of data as mutilple data series. select each one to show data lables for series lable and delete the legend for that series from the legend box. You can format the lables as to orientation veritical etc. (I really miss the old Data lables as assigned that used to be available. I have no idea why that was removed, Cause I used to use it A bunch) "Basil" wrote: I was looking for a way that didn't rely on seperate text boxes. "bj" wrote: one way to do it, (If I understand what you want) on the spread sheet calculate the difference in say D1:D4 on the chart start a text box and in the formula bar enter = and point to the cell with the difference. format the text box for the alignment and fill colors you want and place it on the down bar for each data point pair. "Basil" wrote: Hiya, I have a line chart with two lines. I have included down bars from the upper line. I would like the difference of value between the two lines (i.e. the height of the down bars) to be shown within the down bars. Is this possible? Thanks, Basil -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#8
|
|||
|
|||
Andy -
Do you need a number format? a custom label? to account for a bar crossing zero? - Jon Andy Pope wrote: Hi Jon, The column technique works great for positive values but not so for negative ones. Cheers Andy Jon Peltier wrote: (I really miss the old Data lables as assigned that used to be available. I have no idea why that was removed, Cause I used to use it A bunch) The up-down bars have never had a labeling capability. The alternative approach is to build a floating column chart, and use the columns' data labels to display the difference data. The nice thing about a floating column chart is that the value is the difference between top and bottom, so simple data labels using the Show Value option will do the trick. Floating Column Charts: http://peltiertech.com/Excel/Charts/...ngColumns.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ bj wrote: another way would be to find the midpoint between each data pair lable each midpoint data pair as the delta value plot this group of data as mutilple data series. select each one to show data lables for series lable and delete the legend for that series from the legend box. You can format the lables as to orientation veritical etc. (I really miss the old Data lables as assigned that used to be available. I have no idea why that was removed, Cause I used to use it A bunch) "Basil" wrote: I was looking for a way that didn't rely on seperate text boxes. "bj" wrote: one way to do it, (If I understand what you want) on the spread sheet calculate the difference in say D1:D4 on the chart start a text box and in the formula bar enter = and point to the cell with the difference. format the text box for the alignment and fill colors you want and place it on the down bar for each data point pair. "Basil" wrote: Hiya, I have a line chart with two lines. I have included down bars from the upper line. I would like the difference of value between the two lines (i.e. the height of the down bars) to be shown within the down bars. Is this possible? Thanks, Basil |
#9
|
|||
|
|||
Hi Jon,
Not sure I understand the questions. If you take my example and replace the line series with the stacked columns for negative gaps the 2nd set of columns will appear below the 1st and not in the gap in between. Cheers Andy Jon Peltier wrote: Andy - Do you need a number format? a custom label? to account for a bar crossing zero? - Jon Andy Pope wrote: Hi Jon, The column technique works great for positive values but not so for negative ones. Cheers Andy Jon Peltier wrote: (I really miss the old Data lables as assigned that used to be available. I have no idea why that was removed, Cause I used to use it A bunch) The up-down bars have never had a labeling capability. The alternative approach is to build a floating column chart, and use the columns' data labels to display the difference data. The nice thing about a floating column chart is that the value is the difference between top and bottom, so simple data labels using the Show Value option will do the trick. Floating Column Charts: http://peltiertech.com/Excel/Charts/...ngColumns.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ bj wrote: another way would be to find the midpoint between each data pair lable each midpoint data pair as the delta value plot this group of data as mutilple data series. select each one to show data lables for series lable and delete the legend for that series from the legend box. You can format the lables as to orientation veritical etc. (I really miss the old Data lables as assigned that used to be available. I have no idea why that was removed, Cause I used to use it A bunch) "Basil" wrote: I was looking for a way that didn't rely on seperate text boxes. "bj" wrote: one way to do it, (If I understand what you want) on the spread sheet calculate the difference in say D1:D4 on the chart start a text box and in the formula bar enter = and point to the cell with the difference. format the text box for the alignment and fill colors you want and place it on the down bar for each data point pair. "Basil" wrote: Hiya, I have a line chart with two lines. I have included down bars from the upper line. I would like the difference of value between the two lines (i.e. the height of the down bars) to be shown within the down bars. Is this possible? Thanks, Basil -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#10
|
|||
|
|||
Andy -
The higher the plot order, the further from the X axis a series will be positioned. In my "Column Chart That Crosses the X-Axis" example, I have duplicate series, one for each series you ought to have to plot, with the negative copies in reverse order compared to the positive copies. For fun, plot this data in a column chart, with series in rows: A B C D a 1 -1 1 -1 b 2 -2 -2 2 c 3 -3 3 -3 d 4 -4 -4 4 e 5 -5 5 -5 At categories A and B, series 'a' is always closest to the X axis and 'e' is always furthest. This means 'a' is under 'b' if they both are positive, but 'a' is over 'b' if both are negative. If they are of mixed signs, 'a' and 'b' appear on opposite sides of the axis. This behavior is logical, after you've played with it and thought about it. But at first glance, it can be very confusing to the users. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Andy Pope wrote: Hi Jon, Not sure I understand the questions. If you take my example and replace the line series with the stacked columns for negative gaps the 2nd set of columns will appear below the 1st and not in the gap in between. Cheers Andy Jon Peltier wrote: Andy - Do you need a number format? a custom label? to account for a bar crossing zero? - Jon Andy Pope wrote: Hi Jon, The column technique works great for positive values but not so for negative ones. Cheers Andy Jon Peltier wrote: (I really miss the old Data lables as assigned that used to be available. I have no idea why that was removed, Cause I used to use it A bunch) The up-down bars have never had a labeling capability. The alternative approach is to build a floating column chart, and use the columns' data labels to display the difference data. The nice thing about a floating column chart is that the value is the difference between top and bottom, so simple data labels using the Show Value option will do the trick. Floating Column Charts: http://peltiertech.com/Excel/Charts/...ngColumns.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ bj wrote: another way would be to find the midpoint between each data pair lable each midpoint data pair as the delta value plot this group of data as mutilple data series. select each one to show data lables for series lable and delete the legend for that series from the legend box. You can format the lables as to orientation veritical etc. (I really miss the old Data lables as assigned that used to be available. I have no idea why that was removed, Cause I used to use it A bunch) "Basil" wrote: I was looking for a way that didn't rely on seperate text boxes. "bj" wrote: one way to do it, (If I understand what you want) on the spread sheet calculate the difference in say D1:D4 on the chart start a text box and in the formula bar enter = and point to the cell with the difference. format the text box for the alignment and fill colors you want and place it on the down bar for each data point pair. "Basil" wrote: Hiya, I have a line chart with two lines. I have included down bars from the upper line. I would like the difference of value between the two lines (i.e. the height of the down bars) to be shown within the down bars. Is this possible? Thanks, Basil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get difference in hours | Excel Worksheet Functions | |||
Date difference taking into account weekends. | Excel Worksheet Functions | |||
Calculating the difference between cells | Excel Worksheet Functions | |||
add a column to a pivot table that would show the difference between 2 other Columns | Excel Worksheet Functions | |||
calculate negative or positve difference in time | Excel Discussion (Misc queries) |