Column-Line Chart w/ Dates

I have a chart that is using columns for category "A" on the primary x-axis 
(a $ amount).  This category is broken down first by person, then by month.  
So essentially from left to right the columns will be person "1" and month 
"1", person "1" and month "2", person "1" and month "3", person "2" and month 
"1", and so on....

The chart is also using lines to mark category "B" on the secondary x-axis 
(a $ amount that is a portion of category "A", but not a percentage.  
Essentially category "B" will never be larger than category "A".).  This 
category is broken down in the same fashion as category "A" (first by person, 
then by month).

The problem I'm running into is that the line for month "1", category "B" 
does not hover over month "1", category "A" column.  All lines basically 
hover over the middle column month for each person.  Is there any way to get 
each month line to hover over each month column?

Thx!
0
9/21/2006 9:27:01 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
804 Views

Similar Articles

[PageSpeed] 34

Try this:

    http://peltiertech.com/Excel/Charts/ClusterColAndLine.html

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


"Neutron1871" <Neutron1871@discussions.microsoft.com> wrote in message 
news:839D3AF5-5304-48C9-9071-5F9BDE21DD80@microsoft.com...
>I have a chart that is using columns for category "A" on the primary x-axis
> (a $ amount).  This category is broken down first by person, then by 
> month.
> So essentially from left to right the columns will be person "1" and month
> "1", person "1" and month "2", person "1" and month "3", person "2" and 
> month
> "1", and so on....
>
> The chart is also using lines to mark category "B" on the secondary x-axis
> (a $ amount that is a portion of category "A", but not a percentage.
> Essentially category "B" will never be larger than category "A".).  This
> category is broken down in the same fashion as category "A" (first by 
> person,
> then by month).
>
> The problem I'm running into is that the line for month "1", category "B"
> does not hover over month "1", category "A" column.  All lines basically
> hover over the middle column month for each person.  Is there any way to 
> get
> each month line to hover over each month column?
>
> Thx! 


0
jonxlmvpNO (4558)
9/22/2006 3:52:09 AM
That worked great Jon!!  I used the second method on your website to create 
my chart.

However, what is happening now is that the lines are connecting by month 
from person "1" to person "2" to person "3".  I want the lines to connect by 
person from month "1" to month "2" to month "3".  Is that possible?

"Jon Peltier" wrote:

> Try this:
> 
>     http://peltiertech.com/Excel/Charts/ClusterColAndLine.html
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
> 
> 
> "Neutron1871" <Neutron1871@discussions.microsoft.com> wrote in message 
> news:839D3AF5-5304-48C9-9071-5F9BDE21DD80@microsoft.com...
> >I have a chart that is using columns for category "A" on the primary x-axis
> > (a $ amount).  This category is broken down first by person, then by 
> > month.
> > So essentially from left to right the columns will be person "1" and month
> > "1", person "1" and month "2", person "1" and month "3", person "2" and 
> > month
> > "1", and so on....
> >
> > The chart is also using lines to mark category "B" on the secondary x-axis
> > (a $ amount that is a portion of category "A", but not a percentage.
> > Essentially category "B" will never be larger than category "A".).  This
> > category is broken down in the same fashion as category "A" (first by 
> > person,
> > then by month).
> >
> > The problem I'm running into is that the line for month "1", category "B"
> > does not hover over month "1", category "A" column.  All lines basically
> > hover over the middle column month for each person.  Is there any way to 
> > get
> > each month line to hover over each month column?
> >
> > Thx! 
> 
> 
> 
0
9/22/2006 11:28:01 PM
Rearrange the data so that all points you want to link by a line are in the 
same series, i.e., in the same column of the worksheet.

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


"Neutron1871" <Neutron1871@discussions.microsoft.com> wrote in message 
news:CEF4765A-0025-4FCE-97D8-4370D4E4F2C1@microsoft.com...
> That worked great Jon!!  I used the second method on your website to 
> create
> my chart.
>
> However, what is happening now is that the lines are connecting by month
> from person "1" to person "2" to person "3".  I want the lines to connect 
> by
> person from month "1" to month "2" to month "3".  Is that possible?
>
> "Jon Peltier" wrote:
>
>> Try this:
>>
>>     http://peltiertech.com/Excel/Charts/ClusterColAndLine.html
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> http://PeltierTech.com
>> _______
>>
>>
>> "Neutron1871" <Neutron1871@discussions.microsoft.com> wrote in message
>> news:839D3AF5-5304-48C9-9071-5F9BDE21DD80@microsoft.com...
>> >I have a chart that is using columns for category "A" on the primary 
>> >x-axis
>> > (a $ amount).  This category is broken down first by person, then by
>> > month.
>> > So essentially from left to right the columns will be person "1" and 
>> > month
>> > "1", person "1" and month "2", person "1" and month "3", person "2" and
>> > month
>> > "1", and so on....
>> >
>> > The chart is also using lines to mark category "B" on the secondary 
>> > x-axis
>> > (a $ amount that is a portion of category "A", but not a percentage.
>> > Essentially category "B" will never be larger than category "A".). 
>> > This
>> > category is broken down in the same fashion as category "A" (first by
>> > person,
>> > then by month).
>> >
>> > The problem I'm running into is that the line for month "1", category 
>> > "B"
>> > does not hover over month "1", category "A" column.  All lines 
>> > basically
>> > hover over the middle column month for each person.  Is there any way 
>> > to
>> > get
>> > each month line to hover over each month column?
>> >
>> > Thx!
>>
>>
>> 


0
jonxlmvpNO (4558)
9/23/2006 1:42:43 AM
You are an Excel Genius, thank you!!!

"Jon Peltier" wrote:

> Rearrange the data so that all points you want to link by a line are in the 
> same series, i.e., in the same column of the worksheet.
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
> 
> 
> "Neutron1871" <Neutron1871@discussions.microsoft.com> wrote in message 
> news:CEF4765A-0025-4FCE-97D8-4370D4E4F2C1@microsoft.com...
> > That worked great Jon!!  I used the second method on your website to 
> > create
> > my chart.
> >
> > However, what is happening now is that the lines are connecting by month
> > from person "1" to person "2" to person "3".  I want the lines to connect 
> > by
> > person from month "1" to month "2" to month "3".  Is that possible?
> >
> > "Jon Peltier" wrote:
> >
> >> Try this:
> >>
> >>     http://peltiertech.com/Excel/Charts/ClusterColAndLine.html
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Tutorials and Custom Solutions
> >> http://PeltierTech.com
> >> _______
> >>
> >>
> >> "Neutron1871" <Neutron1871@discussions.microsoft.com> wrote in message
> >> news:839D3AF5-5304-48C9-9071-5F9BDE21DD80@microsoft.com...
> >> >I have a chart that is using columns for category "A" on the primary 
> >> >x-axis
> >> > (a $ amount).  This category is broken down first by person, then by
> >> > month.
> >> > So essentially from left to right the columns will be person "1" and 
> >> > month
> >> > "1", person "1" and month "2", person "1" and month "3", person "2" and
> >> > month
> >> > "1", and so on....
> >> >
> >> > The chart is also using lines to mark category "B" on the secondary 
> >> > x-axis
> >> > (a $ amount that is a portion of category "A", but not a percentage.
> >> > Essentially category "B" will never be larger than category "A".). 
> >> > This
> >> > category is broken down in the same fashion as category "A" (first by
> >> > person,
> >> > then by month).
> >> >
> >> > The problem I'm running into is that the line for month "1", category 
> >> > "B"
> >> > does not hover over month "1", category "A" column.  All lines 
> >> > basically
> >> > hover over the middle column month for each person.  Is there any way 
> >> > to
> >> > get
> >> > each month line to hover over each month column?
> >> >
> >> > Thx!
> >>
> >>
> >> 
> 
> 
> 
0
9/26/2006 5:51:01 PM
Reply:

Similar Artilces:

pivot chart, mean and standard error
hello, I'm using a pivot table to calculate the mean and the stdevp of values collected in different condition. My problem is to draw the corresponding chart for the means of the values, and using the stdevp values for the error bars. Using a stacked histogram, I have something which resembles to what I would like but I would prefer to have a classical error bar. Is there a way to manage ? I want to keep something automatic and not static, with a copy/paste values to draw manually my charts. Thanks for your help -- Misange migrateuse http://www.excelabo.net mail : http://cerbermail.c...

Stacked Line Chart
I have a chart that is predominately a stacked bar chart however I want to have 3 sections as a stacked line chart as the represent hours for the whole group. I can sucessfully set it for 2 stacked lines but when I add the 3rd line if it has either a blank or 0 in the cell it picks up the line from the previous stacked line. Is it possible to have 3 stacked lines? Sure it's possible, but I don't know what you mean, "it picks up the line from the previous stacked line". Stacked means each line's value is added to the previous total, so if the value of the third it...

New Chart in Excel 2007 is NOT showed unless preview
I have a sheet with multiple charts in Excel 2007 one of the new created charts is hidden when I open the file Unless preview is selected, then finally turned on BUT many formats are changed and objects on charts (titles,...) can not be moved ! what's wrong ? Make sure you have installed SP2 for Office 2007 as this fixes some (not all) chart problems best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Rolando" <Rolando@discussions.microsoft.com> wrote in message news:0B0A3E5C-AABD-4982-962B-8C5C09A2CC5A@microsoft...

Using PERCENTILE with *specific* cells in a column
I am trying to use the PERCENTILE function with specific cells in a column. For example, lets say the column is: A 1 5 2 6 3 7 4 8 Using PERCENTILE on say A1:A3 is easy enough, but I want to do say A1 and A4. I can achieve this by defining a named range such that it would be equal to A1 and A4, but would rather not use named ranges, as the above is a very simplistic example of what I want to do -- using named ranges would require a substantial amount of effort to achieve my end goal. I also realize I could re-order the rows such that I swap A4 with A2, therefore could use perc...

How do I create an area and line combination chart?
I want tocreate a combination chart, using the chart types area and line, it is not listed as a custom type in Excel - is it possible? Hi, Ignore the built-in combinations and create your own. Start with creating a Line chart with your data. Then select the line series that you want as an area chart. Right click and pick Chart Type to select the required Area chart type. Cheers Andy Nuala wrote: > I want tocreate a combination chart, using the chart types area and line, it > is not listed as a custom type in Excel - is it possible? -- Andy Pope, Microsoft MVP - Excel http://ww...

Windows Explorer Columns
Hi, The 'Date Modified' column doesn't appear on any of my folders. I add it, and when i browse away from that folder and return to it, it's disappeared again. How do i get every folder to show that column by default? Thanks, Albert A known Vista annoyance, already solved in Windows7. There're several solutions invlving Registry edit which sets your Template for a specific filetype to what you want, not to MS default - if you Google or Bing for it it should showuo in search results. Sorry I don't have exact URL at hand. it i I know it's f...

How to draw a regression line on 7000+ dated data with Excel 2007
How to draw a regression line on 7000+ dated data using Excel 2007 Are your "dates" actual Excel dates or just text? With actual Excel dates, pre-2007 versions have no problem doing simple linear regression. To interpret the results though, you need to remember how Excel dates are stored: An Excel date is the number of days since 1900, so the numeric value of today's date is 39736, and the intercept of the regression will be the value of the relationship extrapolated to 30Dec1899 (since Excel mistakenly considers 1900 to be a leap year) "Gordon Lee" wrote: >...

I get wrong dates when i paste from a different sheet into a new s
Hello again, My problem now is when I open an old excel sheet with data on it and try to paste that info on a new sheet I get wrong information. example: I'm trying to copy dates from an excel sheet I have. The dates are for the year 2004. When I copy the data and paste it on a new sheet the dates I now see are 2000. I'm puzzled. What I find wierd is when I have clipboard open the information shows the correct dates but when it's actually pasted on the sheet it shows as 2000. I'm using Excel 2003, but I get the same thing when using excel 2002(xp) Thanks for your help,...

changed time and date and messed up appointments
Hi I recently went to UK from Australia, then to italy, I must have changed the time zones, and now my appointments are all over the place, Is there a way of getting them back to original times and dates. for the future is there a better way of handling time zones. i also use a pocket pc where i probably changed zones as well. thanks tim See the following MSKB article for a workaround: http://support.microsoft.com/?kbid=197480 -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the mes...

Stock High-Low-Close Chart
Is it possible to produce a Hi-Lo-Close chart with reversed axes - ie with the Hi-Lo bars horizontal rather than vertical? The normal HLC chart has stocks listed along the x axis and price up the y axis. How would I switch this around? I want to display a series of stocks listed down the y axis and the hilo price range given across the x-axis. Thanks for any advice on this. Tim, Assuming your data is first set up like this and cover the range A1:D8: High Close Low Conoco 41 39.5 38 Exxon 42 40.5 39 Shell 41 39.5 38 Baker 40 38.5 37 Halliburton 39 37.5 36 Marathon 40 38.5 37 Diamond 40...

Row/column grid lines
Can someone please tell me how to remove the grid lines from a single row or column Cheers Stuart. Hi Stuart, You need to use a fill color. Format>cells>pattern, pick the color closest to the background color. -- John johnf202 at hotmail dot com "Stuart" <anonymous@discussions.microsoft.com> wrote in message news:5CD88DAF-4CDD-4D04-8E9E-A50D9A319672@microsoft.com... > Can someone please tell me how to remove the grid lines from a single row or column. > > Cheers Stuart. Stuart, You can't remove the grid lines from a single row or column. Grid lin...

How do I subtract 20% from one column in Excel and place totals i.
I would like to know if someone knows the formula I use to subtract 20% from totals in one column on my spreadsheet to show totals in another column on same spreadsheet. Can anyone help? Spent way to long trying to figure out correct formula. =A30*80% assuming totals in A30 -- HTH RP (remove nothere from the email address if mailing direct) "Full Effect Landscaping" <Full Effect Landscaping@discussions.microsoft.com> wrote in message news:7ACF893B-54DF-4EE4-A931-3BB5FA365821@microsoft.com... > I would like to know if someone knows the formula I use to subtract 2...

Year + week => first date of week
Hi, I would like Excel to calculate the date for the first day (Monday) in a week.I have my sheet setup like this: Cell = A1 = Year = 2009 Cell = A2 = Week = 32 Cell = A3 =DATE(A3,1,1)+7*B3 this gives me the Excel date = 40038 (2009-08-13 Thursday) this is not correct. Correct Excel date for 2009 week 32 should be 40028 (2008-08-03 Monday). I live in Sweden and we use the ISO week that starts on Mondays (first week of the new year is the first week that contains Thursday). I don't know if it has got something to do with that the formula above does not work. Above formula is the one I ...

Date problem #6
I have a column containing 4/21/2004 10/7/2003 5/5/2004 Which I need to convert to UK dates. because of the variables(4/, 10/) I am unable to use mid etc in seperate columns. also =TEXT(f2,"DD/MM/YY")fails to work. Hi are these values 'real' date values. If yes a simple format should do -- Regards Frank Kabel Frankfurt, Germany "Gerry" <Gerry.Briant@goodrich.com> schrieb im Newsbeitrag news:9a4601c486b0$01ac1f40$a501280a@phx.gbl... > I have a column containing > 4/21/2004 > 10/7/2003 > 5/5/2004 > Which I need to convert to UK dates. > bec...

Stop Chart Style (Format) Update
Hi TWIMC, I'm using Excel 2000 and I have a pivot table that aggregates data based on REGION, COUNTRY, ZONE etc etc. Once I have set up the chart they way that I would like it to look, if I then make a change to the selection using the pivot chart buttons, e.g. I select a country and the chart re-formats itself back to what appears to be the default style. I've set my chart style to the default but it still doesn't keep my chart style. What I want is that the chart style remains the same but the new values are just plotted onto the chart. I'm sure there is an option to turn ...

center gutter line in Publisher booklet
Hi. I have designed a newsletter that has 12 pages, 8.5 X 11, to be printed out in booklet form on 11 X 17 paper. The format and the pagination all work fine. This is my problem: the two pages at the center of my document (pages 6 - 7) are actually a larger sized calender...so my calender actually takes up both pages in the center. When I print, there is a vertical space down the center of my calender where the two pages divide. How can I get my calender to print out cleanly with gutter space down the center of my page? Is it possible? by the way, I have a networked Konica Minolta print...

Change Y axes (Categories) on line chart
I have a line chart. I am happy with my X axis categories, which say something like "Term 1", "Term 2", "Term 3" etc However my Y axis reflects the actual values (eg Points scored) that I wish to chart, whereas I want it to contain descriptive terms such as "Grade C", "Grade B" etc I have read Jon Peltier on how to change axis by creating a dummy series in a scatter graph. Problem is that if I change to a scatter chart, I lose my X axis.values that I am happy with. I suppose that I could then fix this by using Jon's solution twice: o...

How do print a chart with smooth lines?
I am trying to paste a pie chart in PowerPoint and print a pie chart with smooth edges. Does anyone know how this is done? Thanks I've got the same question. Is there any way to smooth the lines? "AmyC79" wrote: > I am trying to paste a pie chart in PowerPoint and print a pie chart with > smooth edges. Does anyone know how this is done? > > Thanks The only workaround I could come up with was to draw an elipse over the pie chart. I set the color in the middle of the elipe to no fill and made the line thickness "2" to cover the jagged pie circle....

Sparkline Charts
Hi, How can i create sparkline charts in excel 2003 Hi, To do it easily, you'll need to dowload one of the many add-ins available. With a bit of fiddlinh, you can generate some chart types which can be made small enough to fit in a single, slightly enlarged cell. Another way is to use a panel chart. Dave "Raiju" wrote: > Hi, > > How can i create sparkline charts in excel 2003 You can use the free Excel add-in I created. Download and examples files are available at http://sparklines-excel.blogspot.com/. Sparklines for excel is an open source project hosted by S...

Sort numeric data in more than 3 columns in excel
Hi All, I have to sort 12 columns of numeric data in descending order on a excel worksheet. I currently sort 3 at a time and then move forward. For example, I have sales data for months Jan to Dec. I want each of these columns to show zeros at the bottom when the macro is run. The rows would increase in future but the columns would be fixed. The worksheet would also remains unchanged each month. None of these columns are of least significance. Is there a way I could change a recorded macro to achieve this?? I know very little of VB. This would be of great help! Thanks! All of the columns ...

Printing half lines horizontally
My printer is suddenly printing only half of the line horizontally. I have only been using Word 2007 since December. I may have made a change in line spacing but have no idea what to change back to. I'm sure it's not the printer because when I print in Excel the lines are complete and I can print great photos. What do you mean by half lines? Is the text in the line complete, but you have an enlarged right hand margin? If that is the case, it is likely that you are printing the document in Final showing Changes mode so go to the Review tab and change that setting to ...

Line Item Sequence Number #2
I have a question pertaining to the Line Items Sequence Number of an Order apposed to an Invoice. If an Order is Transferred to an Invoice in the GP SOP Trx Entry, does the LNITMSEQ Number stay the same from the Order to the Invoice? If a Item is LNITMSEQ value of 49152 on the Order will it be 49152 on the Invoice? or will it depend of the positioning on that Line on the Invoice? I am trying to report on Costs on Open Invoices from the SOP POP Link and have to use the ORIGNUMB/ORIGTYPE to relate to the SOP60100 Table but I want to be sure the LNITMSEQ is going to be right. Any help w...

how do i delete a * at the end of each word in a column
In a column I have all last names followed by * e.g. Jones* How do I delete the * Thanks in advance for your help Use Replace, and put ~* in the Find What box. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "celler6" <celler6@discussions.microsoft.com> wrote in message news:3C6160E7-7217-4D14-8671-0EA2513D501A@microsoft.com... > In a column I have all last names followed by * e.g. Jones* > How do I delete the * > Thanks in advance for your help =SUBSTITUTE(A1,"*",...

Trying to total the column values in my chart?
Hi !! I am using the Stacked Column with a 3-d visual effect chart. I am charting 6 months worth of numbers with 5 values in each column. I would like to add up the totals in each column of my chart to make a heading (or something). Can this be done? Thanks ! K8T Hi, The last section of this page shows you how to add a total to a 3-d stack. http://www.andypope.info/charts/StackColTotal.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "K8T" <K8T@discussions.microsoft.com> wrote in message news:35E21028-5057-4B86-9725-2324FF5DA718@micr...

Can't set alignment in Pivot Chart
I have a pivot chart with 2 level category labels on the x axis. It looks a bit like this: 1/4 1/6 | 1/4 1/6 | 1/4 1/6 | 1/4 1/6 | Company A | Company B | Company C | Company D | I can change the dates (1/4, 1/6) to display vertically, but not the company names(Company A, Company B). Is there a trick to this? I added a third level, the year. But I still could only change the top level. Thanks for any help! ...