stacked column chart not baselining at 0

I have a data set with categories in columns A-B, and data in columns C-I. 
I've configured my chart to use stacked bars for the first 6 data series and 
a line for the last one. The first 6 are revenue allocations, with the 7th 
being a cumulative total.

Most of the chart looks as expected, but the first category or row of data 
is displaying oddly. The figures in this row are all negative (or 0), so I 
expect the top of the bar to sit at zero, but it doesn't. The top of the bar 
sits at the value of the 7th/line series, so that the bottom of the bar is at 
2x that (negative) value.

Anyone have any tips on how to fix
0
LovePS (4)
3/4/2008 8:19:02 PM
excel.charting 18370 articles. 0 followers. Follow

6 Replies
422 Views

Similar Articles

[PageSpeed] 34

I can't reproduce this problem, in 2003 or 2007. The first category stacks 
downward from the horizontal axis to the point on the line series.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"LovePS" <LovePS@discussions.microsoft.com> wrote in message 
news:01899A5A-8D51-4AA2-8C96-4677989F5887@microsoft.com...
>I have a data set with categories in columns A-B, and data in columns C-I.
> I've configured my chart to use stacked bars for the first 6 data series 
> and
> a line for the last one. The first 6 are revenue allocations, with the 7th
> being a cumulative total.
>
> Most of the chart looks as expected, but the first category or row of data
> is displaying oddly. The figures in this row are all negative (or 0), so I
> expect the top of the bar to sit at zero, but it doesn't. The top of the 
> bar
> sits at the value of the 7th/line series, so that the bottom of the bar is 
> at
> 2x that (negative) value.
>
> Anyone have any tips on how to fix 


0
jonxlmvpNO (4558)
3/5/2008 12:15:44 AM
Can I send an example?

"Jon Peltier" wrote:

> I can't reproduce this problem, in 2003 or 2007. The first category stacks 
> downward from the horizontal axis to the point on the line series.
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
> 
> 
> "LovePS" <LovePS@discussions.microsoft.com> wrote in message 
> news:01899A5A-8D51-4AA2-8C96-4677989F5887@microsoft.com...
> >I have a data set with categories in columns A-B, and data in columns C-I.
> > I've configured my chart to use stacked bars for the first 6 data series 
> > and
> > a line for the last one. The first 6 are revenue allocations, with the 7th
> > being a cumulative total.
> >
> > Most of the chart looks as expected, but the first category or row of data
> > is displaying oddly. The figures in this row are all negative (or 0), so I
> > expect the top of the bar to sit at zero, but it doesn't. The top of the 
> > bar
> > sits at the value of the 7th/line series, so that the bottom of the bar is 
> > at
> > 2x that (negative) value.
> >
> > Anyone have any tips on how to fix 
> 
> 
> 
0
LovePS (4)
3/5/2008 12:46:00 AM
Hmmm, well okay.

jonxlmvp at peltiertech dot com

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"LovePS" <LovePS@discussions.microsoft.com> wrote in message 
news:803C4C6D-5C62-4973-890D-04961B98370D@microsoft.com...
> Can I send an example?
>
> "Jon Peltier" wrote:
>
>> I can't reproduce this problem, in 2003 or 2007. The first category 
>> stacks
>> downward from the horizontal axis to the point on the line series.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>> "LovePS" <LovePS@discussions.microsoft.com> wrote in message
>> news:01899A5A-8D51-4AA2-8C96-4677989F5887@microsoft.com...
>> >I have a data set with categories in columns A-B, and data in columns 
>> >C-I.
>> > I've configured my chart to use stacked bars for the first 6 data 
>> > series
>> > and
>> > a line for the last one. The first 6 are revenue allocations, with the 
>> > 7th
>> > being a cumulative total.
>> >
>> > Most of the chart looks as expected, but the first category or row of 
>> > data
>> > is displaying oddly. The figures in this row are all negative (or 0), 
>> > so I
>> > expect the top of the bar to sit at zero, but it doesn't. The top of 
>> > the
>> > bar
>> > sits at the value of the 7th/line series, so that the bottom of the bar 
>> > is
>> > at
>> > 2x that (negative) value.
>> >
>> > Anyone have any tips on how to fix
>>
>>
>> 


0
jonxlmvpNO (4558)
3/5/2008 1:56:07 PM
I rebooted, and now I can't reproduce it either. Whatever the cause, it is 
fixed now, so I'm happy. I find that a reboot fixes most Excel bugs, but this 
time it took 3 reboots.

Stacey


"Jon Peltier" wrote:

> Hmmm, well okay.
> 
> jonxlmvp at peltiertech dot com
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
> 
> 
> "LovePS" <LovePS@discussions.microsoft.com> wrote in message 
> news:803C4C6D-5C62-4973-890D-04961B98370D@microsoft.com...
> > Can I send an example?
> >
> > "Jon Peltier" wrote:
> >
> >> I can't reproduce this problem, in 2003 or 2007. The first category 
> >> stacks
> >> downward from the horizontal axis to the point on the line series.
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Tutorials and Custom Solutions
> >> Peltier Technical Services, Inc. - http://PeltierTech.com
> >> _______
> >>
> >>
> >> "LovePS" <LovePS@discussions.microsoft.com> wrote in message
> >> news:01899A5A-8D51-4AA2-8C96-4677989F5887@microsoft.com...
> >> >I have a data set with categories in columns A-B, and data in columns 
> >> >C-I.
> >> > I've configured my chart to use stacked bars for the first 6 data 
> >> > series
> >> > and
> >> > a line for the last one. The first 6 are revenue allocations, with the 
> >> > 7th
> >> > being a cumulative total.
> >> >
> >> > Most of the chart looks as expected, but the first category or row of 
> >> > data
> >> > is displaying oddly. The figures in this row are all negative (or 0), 
> >> > so I
> >> > expect the top of the bar to sit at zero, but it doesn't. The top of 
> >> > the
> >> > bar
> >> > sits at the value of the 7th/line series, so that the bottom of the bar 
> >> > is
> >> > at
> >> > 2x that (negative) value.
> >> >
> >> > Anyone have any tips on how to fix
> >>
> >>
> >> 
> 
> 
> 
0
LovePS (4)
3/5/2008 4:02:00 PM
Next time after exiting Excel, clean out the temp directory before 
restarting.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"LovePS" <LovePS@discussions.microsoft.com> wrote in message 
news:95816D27-6DCB-4A88-8414-0E43C0FE54CA@microsoft.com...
>I rebooted, and now I can't reproduce it either. Whatever the cause, it is
> fixed now, so I'm happy. I find that a reboot fixes most Excel bugs, but 
> this
> time it took 3 reboots.
>
> Stacey
>
>
> "Jon Peltier" wrote:
>
>> Hmmm, well okay.
>>
>> jonxlmvp at peltiertech dot com
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>> "LovePS" <LovePS@discussions.microsoft.com> wrote in message
>> news:803C4C6D-5C62-4973-890D-04961B98370D@microsoft.com...
>> > Can I send an example?
>> >
>> > "Jon Peltier" wrote:
>> >
>> >> I can't reproduce this problem, in 2003 or 2007. The first category
>> >> stacks
>> >> downward from the horizontal axis to the point on the line series.
>> >>
>> >> - Jon
>> >> -------
>> >> Jon Peltier, Microsoft Excel MVP
>> >> Tutorials and Custom Solutions
>> >> Peltier Technical Services, Inc. - http://PeltierTech.com
>> >> _______
>> >>
>> >>
>> >> "LovePS" <LovePS@discussions.microsoft.com> wrote in message
>> >> news:01899A5A-8D51-4AA2-8C96-4677989F5887@microsoft.com...
>> >> >I have a data set with categories in columns A-B, and data in columns
>> >> >C-I.
>> >> > I've configured my chart to use stacked bars for the first 6 data
>> >> > series
>> >> > and
>> >> > a line for the last one. The first 6 are revenue allocations, with 
>> >> > the
>> >> > 7th
>> >> > being a cumulative total.
>> >> >
>> >> > Most of the chart looks as expected, but the first category or row 
>> >> > of
>> >> > data
>> >> > is displaying oddly. The figures in this row are all negative (or 
>> >> > 0),
>> >> > so I
>> >> > expect the top of the bar to sit at zero, but it doesn't. The top of
>> >> > the
>> >> > bar
>> >> > sits at the value of the 7th/line series, so that the bottom of the 
>> >> > bar
>> >> > is
>> >> > at
>> >> > 2x that (negative) value.
>> >> >
>> >> > Anyone have any tips on how to fix
>> >>
>> >>
>> >>
>>
>>
>> 


0
jonxlmvpNO (4558)
3/6/2008 3:30:21 AM
This was caused by a second Y-axis. the series was aligning to the 0-value on 
the second axis instead of the first.


"Jon Peltier" wrote:

> I can't reproduce this problem, in 2003 or 2007. The first category stacks 
> downward from the horizontal axis to the point on the line series.
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
> 
> 
> "LovePS" <LovePS@discussions.microsoft.com> wrote in message 
> news:01899A5A-8D51-4AA2-8C96-4677989F5887@microsoft.com...
> >I have a data set with categories in columns A-B, and data in columns C-I.
> > I've configured my chart to use stacked bars for the first 6 data series 
> > and
> > a line for the last one. The first 6 are revenue allocations, with the 7th
> > being a cumulative total.
> >
> > Most of the chart looks as expected, but the first category or row of data
> > is displaying oddly. The figures in this row are all negative (or 0), so I
> > expect the top of the bar to sit at zero, but it doesn't. The top of the 
> > bar
> > sits at the value of the 7th/line series, so that the bottom of the bar is 
> > at
> > 2x that (negative) value.
> >
> > Anyone have any tips on how to fix 
> 
> 
> 
0
LovePS (4)
4/1/2008 4:59:02 PM
Reply:

Similar Artilces:

.NET 3.0, 3.5 SP1, Media Center Issues
Well, I managed to download .NET Framework 3.0, in lieu of 3.5 simply DISCONNECTING non-stop, and though it (3.0) tells me that it has been successfully downloaded and you can now disconnect from the internet", "setup is proceeding to install it" etc., and not more than a few seconds elapse before it tells me that "an error occured, all files are being removed from the computer"...an icon is on the desktop, and I'm advised that when I click on the install icon it will be installed...again, and again..ad nauseam. 3.5 SP-1 will not even complete downlo...

How do I use excel names with INDIRECT with charts
Hello, I want to create a Chart that does not directly reference cell-ranges (i.e. A1:A6), but excel-names that make the reference sheet-independent. My aim is to be able to copy one chart to other worksheets, which have their dataareas at the same places like the source-sheet. Problem: This works fine in cells but not in charts Excel name definition: =INDIRECT("R3C2";0):INDIRECT("R3C5";0) Any idea about this? Thanks in advance, Holger. You have to include the sheet name in the final formula that you want XL to use. -- Regards, Tushar Mehta www.tushar-mehta.co...

Calculating or Adding Values that are 0
I have three fields that I need to total. When i do run the total nothing appears because one of the fields has a 0 value. The expression is not counting 0 values, how do I work around this? Is the value actually zero or Null? If it is null then use the NZ function like this -- Nz([Field1], 0) + Nz([Field2], 0) + Nz([Field3], 0) -- Build a little, test a little. "blanch2010" wrote: > I have three fields that I need to total. When i do run the total nothing > appears because one of the fields has a 0 value. > > The expression is not ...

Line Chart #13
Hi, The following is my data and I need to chart this. Is it possible to have a range in one cell? Thanks for your help in advance Experience (Years) Large Medium Small 1 $90,000-$100,000 $75,000-90,000 $65,000-78,000 2 $92,000-100,000 $80,000-95,000 $75,000-85,000 3 $105,000-130,000 $90,000-100,000 $85,000-95,000 4 $117,000-150,000 $100,000-115,000 $90,000-110,000 5 $140,000-170,000 $115,000-130,000 $100,000+ 6 $140,000-190,000 $135,000+ $100,000+ 7 $170,000-250,000 $135,000+ $110,000+ ...

Conditional format if cell=0 then font colour same as background .
I have linked a cell from another sheet. If the original cell is blank, 0 shows in the linked cell (text would be typed in source cell). To handle this, I conditionally formatted the linked cell, so if it equals zero, the font colour is the same as the background colour of the cell so you can't see the zero But the zero still prints even though it is not showing prior to printing Try the formula =IF(Sheet1!F14="","",Sheet1!F14) in the linked cell. HTH Sandy -- to e-mail direct replace @mailintor.com with @tiscali.co.uk "Paligap" <Paligap@discu...

Refresh pivot chart when info in a cell changes
To all, I am looking for help with a Macro. What I ultimately want to do is refresh a pivot chart when the user makes a selection from a list (in a data validation cell). For example, I am an end user that selects my territory from a "drop down". The pivot chart will update to the information that is directly related to my territory. Thanks in advance for your help. Excel 2007, PivotTable With Table dependent drop-downs instead of data validation drop-downs. With non-event-driven macros. http://www.mediafire.com/file/ygzjqyi2j1x/03_09_10.xlsm ...

data labels in charts
This is a probably one of the simplest questions but I'm just starting to chart again. I have entered a category title "year" in cell A1 and entered from 1995 to 2000 from A2 to A7 that I want to appear on the horizontal axis. I have entered a title in B1(unemployed) and 5 values B2 to B7 directly below. When I choose a "column" chart my vertical axis appears correctly but the horizontal axis does not include the years as labels. The labels that appear are the numbers 1 to 6 instead of 1995 to 2000. How do I make the chart pick up the data labels from colu...

Combine variable number of columns
I have a single spreadsheet with a list of clients, addresses and their product interests. This table will be used to drive a Mailmerge document. In the document, I want to be able to refer to the products in which they indicated an interest. The problem is that while one client may have identified only one product (one column) others have selected anywhere from two to 170 products - each product is in a separate column. I can join two columns with "&", but when I have an inconsistent number of columns how do I do this efficiently? - I will have to do it for each client, e...

Excel 2007 chart color change based on the label.
I have 12 charts 1 for each person and the data changes every week and some of the values have 0 so are filtered out. On the chart I want 0-15 to be blue, 16-30 to be light purple, 31-60 to be a cream color, 61-90 to be light blue, and 90+ to be dark purple. With the code below I can change the color if I take the if statement out but I would like to change the color based on the label. I have tried several things like .text, .name, and .label. I just can't fine the right name. Name1 0-15 1 61-90 1 90+ 3 Name 2 0-15 14 16-30 5 31-60 1 61-90 6 90+ 12 For i = 1 To A...

Boxplot and Whisker Chart
Hello, I use the chart wizard\custom chart\Line on 2 axes to draw boxplot and whisker chart with percentage on the left y axes and quality on the right y axes but it doesn't show right like the others chart. Anyone know how to draw the Line on 2 axes or Line-column on 2 axes for Boxplot and Whisker Chart? Thanks, ...

To goup in each month on date column
I am using office 2007 and I have an excel sheet with the following Date OS 2/1/2009 200.30 2/1/2009 300.00 3/2/2009 400.00 5/2/2009 600.00 How I can group the excel to know outstanding in each month. To group January os and February os and so on.... Please help with thanks and Regards Try =SUMPRODUCT((TEXT(A2:A10,"mmyyyy")="022009")*(B2:B10)) with the date in cell C1 =SUMPRODUCT((TEXT(A2:A10,"mmyyyy")=TEXT(C1,"mmyyyy")*(B2:B10)) -- Jacob "pol" wrote: > I ...

Make input in one column determine dropdown list in another.
Is there any way I can make the info thats input in one column (selected by dropdown list) determine which (one of several) dropdown list is used in another column? Use a list formula of the type =OFFSET(IF(E1=1,rng1,rng2),0,0,(COUNTA(IF(E1=1,rng1,rng2))),1) where E1 is the first DD. -- HTH RP (remove nothere from the email address if mailing direct) "gettin-older" <gettin-older@discussions.microsoft.com> wrote in message news:8FF90B6B-F07F-402B-A042-E1B103C40EED@microsoft.com... > Is there any way I can make the info thats input in one column (selected by > dr...

User login not deleting in GP8.0
Hi have created a new user ID & given access to a company. After loggin into GP8.0 and log-off from the company, am unable to delete this above created user . A message is displayed saying "Cannot drop login as the user is currently logged in. Am using the 'sa' as the user when deleting. This is not true as user activity table is clear, other than the final user sa. From the backend, on delete of user from GP (Tools>System>User), only user ID is deled from each of the Databases, but the SQL login remains in the security folder. Hi Un check the SQL options in ...

Chart with three SeriesCollections
Greetings! At me such question: I have constructed a chart with thre SeriesCollections, but it shows strange - that is values1 = values values1 and values2 = values + values1 + values2. Categories are the same. How to show it in a normal kind -- docd ----------------------------------------------------------------------- docdz's Profile: http://www.hightechtalks.com/m35 View this thread: http://www.hightechtalks.com/t229428 ...

Need to expand past column IV
Hi, Were using Excel 2003 and ran into the end of colums at IV. Besides upgrading to Office 2007 is there way to extend or add-in more columns. Thanks, BobV Bob, No, but using all 256 columns is often a sign that you should use database data style entry - instead of having a column for each of the ?days(?) enter the column property as a field, with each entry in a separate row. Beyond that, it is hard to be more specific since your question is vague. HTH, Bernie MS Excel MVP "Vinecom" <vinecom@nospam.net> wrote in message news:8CBC158A-0AE4-46D6-9EB3-4CCDA375CCA9@mi...

Filter for time elapsed between 2 columns
I have Planned Start Time And Actual Start Time I only want to return records where the Planned Start Time is equal to or less than 48 hours from the Actual Start Time Thanks for your help I assume that this time fields include also the date, other wise you can't know the different between two times unless they are both on the same day, which mean up to 24 houres In the query create another column that calculate the different between the two fields HoursDiff: DateDiff("h",[Start Time],[Actual Start Time]) That will return the different in hours, under that field create t...

IE7 - Firefox - Outlook Client
We have found an issue that some of y'all may find interesting: IE7 must be the default browser if Firefox (and probably any other browser) is installed. If FF is default, users will not be able to open any entity, or create activities. This may be true with IE8 also but we have no users using IE8...yet. Not an issue with IE6. -- Jim ...

Connecting Shapes
I cannot figure out why when I click the connecting tool, and click on a connection point of one shape, then drag to another shapes connection point, the line doesn't connect. I can't figure this out, then when I add mulitple shapes, the connection lines do not even come close to the shapes, they appear at the bottom left hand corner of the screen!! Ugh...please help! I have auto connect on and tried to check and un-check the snap and glue options.. but nothing seems to help. I have to get this org chart done by next week..and 2007 was just installed... What is going on !?? ...

Inserting chart AND related table in document
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Inside of a Word document, I have inserted a new table which has a corresponding chart. Everything works fine and updates as I enter new information. <br><br>What I want to do is have both the chart and the table in my document, as the table has a details field I want people to see that is not reflected in the chart. AFAIK, the chart cannot display anything not directly related to it. A data table can be included but that won't satisfy what I understand your requirement to be. You can also ...

Chart the Top 3 values
I'm a programmer, but I don't program in Excel normally so bear with me. I currently have an Excel chart that displays the frequency of Accidents in Man Hours Lost for the overall Program with the x-axis being each Qtr. Within this Program are 40 Projects. What I would like to do is chart the top 3 most accident prone Projects as an overlay on the existing chart. I've worked out how to add a combo box that will add a series to the chart based on the user's selection(s), which gives them the ability to add desired project ranges to the chart. But, I would like to make ...

OMA Not Working after installing .net 2.0
Suddenly my OMA isn't working. I just installed the latest .net framework 2.0 and coincidentally, OMA stopped. The error I get is: Server Error in '/OMA' Application. -------------------------------------------------------------------------------- Collection was modified; enumeration operation may not execute. blah..blah...blah Any ideas? "bdtmike@hotmail.com" wrote: > Suddenly my OMA isn't working. I just installed the latest .net > framework 2.0 and coincidentally, OMA stopped. The error I get is: > > Server Error in '/OMA' Applicati...

Blank Charts and Paste Special
Blank charts seem to be ignoring the settings in the paste-special dialog. Using Excel 2007. I want to create several x-y scatter plots from different data sets. There is some non-trivial formatting of the axis and legend and so on that I want to maintain and not have to do over each time. So, I made the graph once on one set of data. Then I made a copy. Then on the copy I deleted all the data series. There are a bunch of data series on each chart, so I didn't want to copy a chart with the data on it. Then I made a copy of the now blank chart. Then I did select-copy on the data for the...

Excel Column Sum
I get a different answer to a column of numbers (only 6) when I manually add them together as compared to doing a formula (=C6+C7+C8). Why? Likely, you should check the values in the formula bar. You're probably adding up 22 + 23 instead of 22.4 + 23.4 or something like that. Excel, when summing, sums the actual values of the cells, NOT the value you SEE (which is a result of Cell formatting. ************ Hope it helps! Anne Troy www.OfficeArticles.com "kandyr" <kandyr@discussions.microsoft.com> wrote in message news:84EA6380-D34D-422A-9876-46A37EAE3546@microsoft.com...

Macro help required please!
Help! I have a workbook (which I've attached) from which I have to extract data - every time a field named "ReportID" changes I need to create a new workbook from a template and copy certain cell contents to the new workbook. For example if the first set of ReportID records has three records in it I need to create one new workbook then copy cells from just this block of cells. Then I need it to loop down to the next block of ReportID fields and do the same thing - so for example if there are twenty records in the ReoprtID field copy data into one new workbook. Any help much ...

Lookup Value and find Corresponding Value on another row same column
MS Excel 2003 Let's say I have row 1 containing values 1-31 (so the value in A1 is 1, B1 is 2, etc all the way until 31). Now in row 2 is where I would put an "X" in any of the columns that contain a number from 1-31. How can I create a formula that will give me the value (1-31) from row 1 depending on where I place X in row 2? TIA :confused: -- martialtiger ------------------------------------------------------------------------ martialtiger's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4743 View this thread: http://www.excelforum.com/sho...