Negative Values in Stacked Bar Charts

I'd like to include negative values in stacked bar charts, with the total 
stacked value equal to the sum of each of the subtotals.  However, when I 
tested this, it appears that Excel just shows the negative components at the 
bottom of the bar, below zero, but the stacked total still seems to reflect 
the sum of the positive components.  

Is there a way of showing a stacked bar the way I'm trying to do it?

Todd

0
TJenkins (23)
3/28/2007 1:53:57 AM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
487 Views

Similar Articles

[PageSpeed] 9

Hi,

In order to plot the chart how you want you would have to make the negative 
values positive.

You may need to explain further why you need a negative value should be 
displayed in a positive way?

Cheers
Andy

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"T. Jenkins" <TJenkins@discussions.microsoft.com> wrote in message 
news:846444E1-3D76-4078-B1E2-347C26FEAC94@microsoft.com...
> I'd like to include negative values in stacked bar charts, with the total
> stacked value equal to the sum of each of the subtotals.  However, when I
> tested this, it appears that Excel just shows the negative components at 
> the
> bottom of the bar, below zero, but the stacked total still seems to 
> reflect
> the sum of the positive components.
>
> Is there a way of showing a stacked bar the way I'm trying to do it?
>
> Todd
> 

0
andy9699 (3616)
3/28/2007 9:19:56 AM
I probably wasn't clear before.  The bottom line is that I wanted the total 
height of the stacked bar to be the sum of the individual values.  So if my 
data included 50, 100, and -25, then the top of the bar should be at 125.  
With a basic stacked bar, the height is 150, and it appears that the -25 has 
no affect.

My workaround was to create a separate line graph showing the totals of each 
data set.  This works, but I was hoping to just use the stacked bar.

Thanks,
Todd

"Andy Pope" wrote:

> Hi,
> 
> In order to plot the chart how you want you would have to make the negative 
> values positive.
> 
> You may need to explain further why you need a negative value should be 
> displayed in a positive way?
> 
> Cheers
> Andy
> 
> -- 
> 
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
> "T. Jenkins" <TJenkins@discussions.microsoft.com> wrote in message 
> news:846444E1-3D76-4078-B1E2-347C26FEAC94@microsoft.com...
> > I'd like to include negative values in stacked bar charts, with the total
> > stacked value equal to the sum of each of the subtotals.  However, when I
> > tested this, it appears that Excel just shows the negative components at 
> > the
> > bottom of the bar, below zero, but the stacked total still seems to 
> > reflect
> > the sum of the positive components.
> >
> > Is there a way of showing a stacked bar the way I'm trying to do it?
> >
> > Todd
> > 
> 
0
TJenkins (23)
3/28/2007 11:30:01 PM
On Wed, 28 Mar 2007, in microsoft.public.excel.charting,
T. Jenkins <TJenkins@discussions.microsoft.com> said:
>I probably wasn't clear before.  The bottom line is that I wanted the total
>height of the stacked bar to be the sum of the individual values.  So if my
>data included 50, 100, and -25, then the top of the bar should be at 125.
>With a basic stacked bar, the height is 150, and it appears that the -25 has
>no affect.
>
>My workaround was to create a separate line graph showing the totals of each
>data set.  This works, but I was hoping to just use the stacked bar.

How do you think such a thing would look? Either the 100 or the 50 would 
have to be subtracted from in order to total 125, and then there is no 
stacked bar chart any more.

Have you considered a waterfall graph instead?

-- 
Del Cotter
NB Personal replies to this post will send email to del@branta.demon.co.uk,
    which goes to a spam folder-- please send your email to del3 instead.
0
del1907 (586)
3/29/2007 12:07:59 AM
Reply:

Similar Artilces:

Filter (blanks) with two or more unique values not working
Excel 07 - Multiple workbook issue - One of them has 6540 rows and CA columns. I auto filter all headers then try to sort on column BM (or any). I can filter for (blanks) and one unique value and will see only those items. If I choose (blanks) and two or more unique values, I will see all of the selected items but no (blanks). Column BM has eight unique values and (blanks). I have tried selecting all the data before setting my filter, exporting to tab delimited text file then importing into a new workbook, changing the format of the cells to text/general/number. So far nothing w...

sub-form to link using a range of values
Hello, Is there a way to link sub-form records to the master-form whereby the link falls within a range of values? Example: I'm looking for offsite lab procedures that have a [service_date] that occurred either on or in-between the hospital [admit_date] and [discharge_date] fields. So, (somehow) display the lab records where [service_date] >= [admit_date] AND [service_date] <= [discharge_date] I was envisioning the master-form having unique records for each patient stay with the fields for their med. rec. number, admission date and their discharge date. The sub-form would ha...

Find last column & copy/paste values
Hi all I'm working on a table of data where we update each months data AND hold last months data. Example Table: Jan_10 Feb_10 Mar_10 Apr_10 May_10 etc... Prod 1 55 10 0 0 0 0 Prod 2 5 0 0 0 0 0 Prod 3 0 10 0 0 0 0 Prod 4 55 10 0 0 0 0 etc.. All figures for forward months (e.g. from Feb onwards) are hloo...

Show values from non-subtotaled columns when doing a sub-total?
I am trying to have Excel carry values from non-subtotaled cloumns to show up on the subtotal row. What values are you trying to show? Instead of subtotals, you may find it easier, and more flexible, to use a Pivot Table to summarize the data. There are instructions in Excel's Help, and Jon Peltier has information and links: http://peltiertech.com/Excel/Pivots/pivotstart.htm Jim wrote: > I am trying to have Excel carry values from non-subtotaled cloumns to show up > on the subtotal row. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech...

Changing Multiple Tab Colors based on lookup value VBA?????
I am very new to VBA and am looking for help with the following problem: My workbook contains ten tabs that link back to a central sheet. I would like the color of each tab to change (Green vs No Color) based on whether a certain cell in each tab meets a certain requirement. Example: If cell (V1) in tabs 1, 2 & 5 = "Yes" then color the tab green else no color. Note, (V1) is a lookup function that gives a value from the central sheet. The remaining tabs (3, 6,7,8,9 & 10) would have no color since the lookup function is not equal to "Yes". Note, the central ...

Retrieve value from Combo Box and Radio Button
Hi, I have two questions about Excel VBA and hope any experts can give me a hint (1) I wonder how to retrieve automatically the previously saved values from the combo box and a group of radio buttons whenever I open the workbook? (2) Everytime I need to insert an object, I copy the object to one worksheet (named as SLD) from another worksheet (named as Lib) which acts like a library. However, because of using the function "SELECT", I encounter "flickering" on the main sheet (named as Sheet1) each time an object is inserted. Is there any way that I coul...

Value/Reference Types
Hi, I'm new to C# and have run into a problem of my own making! I originally took from the documentation that as long as I didn't use the "ref" keyword in method declarations, that everything would be passed "by value". I now believe I was incorrect and that it's only types like int, bool, etc. that behave like that? i.e. things like XmlDocument are always passed by reference in method calls? I was writing a "wrapper" class around a PRIVATE XmlDocument but because of the above lack of understanding, I don't think it's as "encapsulated&quo...

Finding y-Values in budget curve app w/ excel
I've been seeking a solution to finish a development project for a client. The project involves dispersing a total amount for a time-phased budget. The user inputs the following: - Total_Budget_Amount (total dollars for the whole budget term) - Number_of_Periods (the number of months for the budget term) - Percent_Budget_Spent (the percent of the total budget spent at Percent_Periods_Passed) - Percent_Periods_Passed (the percent of periods that have passed where the sum of all expenditures for the previously completed periods equals the Percent_Budget_Spent * Total_Budget_Amount) .... Pe...

Chart Total
I have a chart that has 10 colums. In Column 4 it lists account numbers. In column 8 it lists the dollar amount. The account numbers (in column 4) are listed more than once. I want to excel to look for the account number and then add up all the dollar amounts in colum 10 that have that account number in colum 4. Does that make sense? Would this be a v-look up? Put an account number in cell M1, then in N1 put this formula: =SUMIF(D$1:D$100,M1,J$1:J$100) This assumes you have 100 rows of data - adjust to suit. If you put other account numbers in M2 downwards, then just copy the fo...

charts and graphs
we are trying to do a column chart type graph for a math class. we put in all the information, but for some reason our graph is not coming up in Excel. what are doing wrong? we can get the chart to appear and define the different colors for each piece of information, but we are not getting any columns in our graph. HELP! Not sure what you are doing wrong. Try this Row 1, column A <empty, B <Store A> (ie. the words as text), C <Store B> Row 2 to 6 in A column: Jan, Feb, March, ..... Rows 2:6 columns B and C: some numbers in the range 1 to 10 Select A1:C6 and start the ch...

flow chart
I made my flow chart, but I am not able to get it to print. Is there a special way? It will not print the shapes or the Text I have in them. It will only print words that I do not have in shapes Can you please please help me????? thanks ------=_NextPart_0001_0FD020DC Content-Type: text/plain Content-Transfer-Encoding: 7bit Try pasting the drawing contents into Word and then print. It should definitely work. You can also insert a visio object in Word and print. However it is equally important to know what printer you are trying to print to and which version of Visio are you using ? Print...

Value Pak Installer
Does anyone know how I would access Value Pak installer for Mac Office X? Can it be downloaded from the Microsoft web site, and if so, where? Thanks. On 3/6/04 6:59 am, in article 176a901c4492f$f578ed10$a301280a@phx.gbl, "Brian" <anonymous@discussions.microsoft.com> wrote: > Does anyone know how I would access Value Pak installer for Mac Office X? > > Can it be downloaded from the Microsoft web site, and if so, where? > > Thanks. For Office vX, the value pack is on the installation CD. For Office 2004, there is no separate value pack. -- Barry Wainwrigh...

HELP, Navigation bar works in Firefox, not Explorer
I have recently uploaded a website created on FrontPage 2003 to GoDaddy. The home page navigation bar works well when viewed with Firefox, but not at all with Explorer 7. What's wrong???? Thanks, No idea. But your question seems to have nothing to do with Access, the Microsoft database application that this newsgroup is concerned with. Rob captain" <captain@discussions.microsoft.com> wrote in message news:1EAD2E1E-6F46-49F6-AB38-3F26A3B764ED@microsoft.com... >I have recently uploaded a website created on FrontPage 2003 to GoDaddy. >The > home page navigati...

chart links
I have an excel workbook that consists of several sheets and 4 of these sheets are charts, i am wondering if its possible to see where the source cells are that drive the chart columns. I know they are not external they are all within the workbook. The chart button at top only lists greyed out buttons when in the chart sheets. Any help appreciated.. Binza You see grayed out options under chart when the chartsheet is protected. Even when protected, you can still see the source of data when you click on the data series. You can't change the source when it is protected, but, at lea...

Referencing Data Filter to a cell value
i am having continuing problems with the following: I have a spreadsheet which lists project status. One of the columns shows the installation subcontractor. I want to have a drop down box where you select the contractor, and once selected, the list automatically (or by use of a macro button) filters to that selected. I have put the drop down box in, then created a vlookup to convert each contractor to a number, and placed a hidden column with this info. Where i am stuck is how i get the data filter to look at my link cell from the drop down box. Any help is appreciated Richard R...

Gantt Bar truncated
Quick search ends in no joy.... I had a fellow scheduler drop buy with a question. He has inherited a Project (2003) file. The task bars in the gantt chart view do not reflect the "start" and "finish" dates in the start and finish column. When the dates are added to each end of the bar in the gantt chart view, the task bar extends from the start date but does not stretch all the way out the planned finish date. I did a cursory view of the file on my way out and there was nothing readily apparent that would be driving this condition. I'm new to MS Project...

Chart series legend font size keeps shrinking
Excel 2007. I set the font size at 10pt, but it seems that each time I open the file, the font size gets smaller. By the time it gets to 7pt or less, it is really annoying and I re-set it to 10. It should stay at the size selected by the user, not choose its own size. ...

bar charts don't udate when data is changed-why?
I inherited a spreadsheet with data on sheet 1 and an Excel-generated bar chart on sheet 2. The table is NOT a pivot table. When I change data in the table, the bar chart never updates. I am relatively new to Excel. What am I missing? Hi, The updated data is probably outside the old data range. You can check that by temporarily replacing one of the entries that is being plotted correctly. If that's the problem you need to extend the plot ranges. There are other possibilities. cheers, Shane "JustSomeGuy" wrote: > I inherited a spreadsheet with data on sheet 1 a...

Case format for getting cell value from a worksheet.
Good Afternoon, I need to create a series of Case conditions based on the value of a cell on a certain worksheet in my open workbook..For the life of me I cannot get the syntax right and I'd appreciate any help i could get. My condition is on a worksheet named "Lookup_Values" Cell "M3" is a value used as a case condition. what I want to do is retrive that value and use it to compare against each case like below. RPT_Date = ("Lookup_Values!M3") Select Case True Case RPT_Date = "January" Perform my actrions Exit Sub ...

Retrieve RGB values of Even Row Banding of User-Defined Table Style
Dear Experts: I got a user-defined table style that has an even row banding (red), i.e. the selected table gets alternately shaded in red/white. The user- defined table style is called (tbl_style_red) Is it possible to retrieve the RGB values (Red/Green/Blue) of the even row banding of this user-defined table style and display the values in a msgbox. Help is much appreciated. Thank you very much in advance. Regards, Andreas ...

Combo Box not displaying first value
I am using Access 2003 SP3 I created a Combo Box with two columns. The first column is bound to the field ID. It is a Yes/No field with a default "Yes". The second column is displayed on the form. Here are my property settings that I think may relate to my problem Name ApprovedCombo Control Source ID Row source 1;" APPROVED";0;"NOT APPROVED" Column Count 2 Column width 0";1.55" Bound Column 1 Limit to List Yes Auto Expand Yes Default Value 1 When I first open up the form, the default value shown in the ApprovedCombo box is correctly d...

title bar problem in Excel 2007
Sometimes when I start Excel for the first time after starting the PC, the title bar is overlaid with the status bar that would normally appear at the bottom of Excel, so I don't get to see the round Office button, and I see the zoom facility at the top, not the bottom, and the workspace is empty - no default empty spreadsheet. If I close Excel (by right clicking on the taskbar because there is X button to be seen) and then start it up, it then starts up fine. Excel was installed as part of Office 2007 SBE, running on Vista Ultimate 64 bit. Is this a known problem? I've not be...

In Access 2007, how do I get a chart to reflect table data?
Am not sure how to get charts to reflect table data. It appears that you can create charts in forms and reports, and there's a chart wizard in which I select a table and fields, but the resulting chart does not have table data in it. Any suggestions? ...

NULL Value in XML Schema
Hi All, How to define an XML element with no value and no attribute? <X> <Y/> </X> And Y has no value? I've done it with: <xsi:element name="Y" type="rn:T_NULL"/> <xsi:simpleType name="rn:T_NULL"> <xsi:restriction base="xsi:string" length="0"/> </xsi:simpleType> But it seems too complicated. Thanks, Reuven Reuven Nisser wrote: > How to define an XML element with no value and no attribute? > > <X> > <Y/> > </X> > > And Y has no value? > &g...

IF statement with FALSE value being text AND cell reference?
I want my FALSE statement to have a "<" infront of the number reference by the calculation, and don't know how to make the FALSE have text an an equation. =IF(D9>1,(D9*D8),"<"(D9*D10)) I can get my FALSE to have just a < sign if I do this: =IF(D9>1,(D9*D10),"<") I can get my FALSE to have the number calculated by the equation if do this: =IF(D9>1,(D9*D8),(D9*D10)) But how come I can't get it to read "<"(#value) by using the firs equation listed above? Thanks for the help in advance -- jcob -------------------------...