Value (Y) axis crosses between categories and Secondary Value (Y) Axis

Hi all -

I have 7 series on my chart
Series 1 is column chart - may be area chart eventually (2007 data)
Series 2 is column chart 2008 data
Series 3 is column chart 2007 avg
Series 4 is a column chart 2008 avg
Series 5 is a line chart (32,0 benchmark)
Series 6 is a line chart (points to column chart for 2007 avg, I then
add text to right to explain the column)
Series 7 is a line chart (points to column chart for 2008 avg, I then
add text to right to explain the column)

I would like the column charts for 2007 and 2008 data to be flush to
the origin (12 data points each year)
I would like the benchmark line chart of 32 to span the chart area
from left to right (16 data points)

>From what I've read, the way to accomplish this, is to clear the tick
mark from Value (Y) axis crosses between categories when formatting
the x axis.

However, when I do that, the data point for 2007 closest to the origin
appears cut-off on left side of column

I more or less posted this information on Ozgrid a few days ago
(Ozgrid is now down until Nov. 13 according to the site). Andy Pope
replied that if I move the line charts to the Secondary Value (Y)
Axis, that would resolve the problem.

I must be doing something wrong. I did assign the line chart to
Secondary Value (Y) Axis and then cleared the tick mark on the x axis:
Value (Y) axis crosses between categories. However whatever datapoint
is closest to the Primary Y Axis disappears entirely

What am I doing wrong here?
Send a confidential e-mail for a sample copy of the workbook
Best regards,
-markc

0
10/26/2007 10:30:37 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
929 Views

Similar Articles

[PageSpeed] 15

To draw a line on a chart, use a technique like this:

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

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


"-markc" <winston.snyder@gmail.com> wrote in message 
news:1193437837.523563.229200@z24g2000prh.googlegroups.com...
> Hi all -
>
> I have 7 series on my chart
> Series 1 is column chart - may be area chart eventually (2007 data)
> Series 2 is column chart 2008 data
> Series 3 is column chart 2007 avg
> Series 4 is a column chart 2008 avg
> Series 5 is a line chart (32,0 benchmark)
> Series 6 is a line chart (points to column chart for 2007 avg, I then
> add text to right to explain the column)
> Series 7 is a line chart (points to column chart for 2008 avg, I then
> add text to right to explain the column)
>
> I would like the column charts for 2007 and 2008 data to be flush to
> the origin (12 data points each year)
> I would like the benchmark line chart of 32 to span the chart area
> from left to right (16 data points)
>
>>From what I've read, the way to accomplish this, is to clear the tick
> mark from Value (Y) axis crosses between categories when formatting
> the x axis.
>
> However, when I do that, the data point for 2007 closest to the origin
> appears cut-off on left side of column
>
> I more or less posted this information on Ozgrid a few days ago
> (Ozgrid is now down until Nov. 13 according to the site). Andy Pope
> replied that if I move the line charts to the Secondary Value (Y)
> Axis, that would resolve the problem.
>
> I must be doing something wrong. I did assign the line chart to
> Secondary Value (Y) Axis and then cleared the tick mark on the x axis:
> Value (Y) axis crosses between categories. However whatever datapoint
> is closest to the Primary Y Axis disappears entirely
>
> What am I doing wrong here?
> Send a confidential e-mail for a sample copy of the workbook
> Best regards,
> -markc
> 


0
jonxlmvpNO (4558)
10/28/2007 12:33:23 AM
On Oct 27, 5:33 pm, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> To draw a line on a chart, use a technique like this:
>
> http://peltiertech.com/Excel/Charts/AddLine.html
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. -http://PeltierTech.com
> _______
>
> "-markc" <winston.sny...@gmail.com> wrote in message
>
> news:1193437837.523563.229200@z24g2000prh.googlegroups.com...
>
>
>
> > Hi all -
>
> > I have 7 series on my chart
> > Series 1 is column chart - may be area chart eventually (2007 data)
> > Series 2 is column chart 2008 data
> > Series 3 is column chart 2007 avg
> > Series 4 is a column chart 2008 avg
> > Series 5 is a line chart (32,0 benchmark)
> > Series 6 is a line chart (points to column chart for 2007 avg, I then
> > add text to right to explain the column)
> > Series 7 is a line chart (points to column chart for 2008 avg, I then
> > add text to right to explain the column)
>
> > I would like the column charts for 2007 and 2008 data to be flush to
> > the origin (12 data points each year)
> > I would like the benchmark line chart of 32 to span the chart area
> > from left to right (16 data points)
>
> >>From what I've read, the way to accomplish this, is to clear the tick
> > mark from Value (Y) axis crosses between categories when formatting
> > the x axis.
>
> > However, when I do that, the data point for 2007 closest to the origin
> > appears cut-off on left side of column
>
> > I more or less posted this information on Ozgrid a few days ago
> > (Ozgrid is now down until Nov. 13 according to the site). Andy Pope
> > replied that if I move the line charts to the Secondary Value (Y)
> > Axis, that would resolve the problem.
>
> > I must be doing something wrong. I did assign the line chart to
> > Secondary Value (Y) Axis and then cleared the tick mark on the x axis:
> > Value (Y) axis crosses between categories. However whatever datapoint
> > is closest to the Primary Y Axis disappears entirely
>
> > What am I doing wrong here?
> > Send a confidential e-mail for a sample copy of the workbook
> > Best regards,
> > -markc- Hide quoted text -
>
> - Show quoted text -

Thanks Jon
Works great
Appreciate the help

Best regards
-markc

0
10/31/2007 7:03:23 PM
Reply:

Similar Artilces:

Find value in a column and insert rows above
The set up looks like this: ColU ColV ColW ColX Y N N N Y N N N N Y N N N N Y N N N Y N N N Y N N N Y N N N Y Y Columns will always be U through X and will always be sorted in this order. I need to find the first Y in each column and insert 2 rows above that row. On the blank row above the first Y, I need to highlight in yellow and put title in the first cell, such as New, Old, Existing, Deleted. Any help would be greatly appreciated. Thanks for your time, Dee If desired, send your file to my address below. I will only look if: 1. You send a copy of this ...

Column chart with 2 Y axis's
I have two Y axis, the secondary is 10x the primary in values. The 1 data series plotted against the secondary axis shows up right on top of the 4 data series plotted against the primary axis. Is there some way to avoid this, so all 5 data series appear side by side with no overlap? Thanks!!! Norm PS Excel 2002 & WIndows 2000 Pro Father Guido ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯ I plan on living forever... so far, so good Plot the data series you want on the secondary axis not once but 5=20 times. Ensure all of them are on ...

How to delete rows automatically with values zero out?
Hello, I have a spreadsheet with hundreds of rows. Many of them were entered at the beginning and then were reversed out. I got this spreadsheet from our ERP system. It is just like this: Part# Quantity Value 013-001 1 $1.00 013-001 -1 $1.00- 013-002 5 $25.00 013-007 3 $120.00 013-007 -3 $120.00- 022-001 12 $17.00 041-009 7 $251.00 041-009 -7 $251.00- 052-061 10 $30.00 How can I delete those rows autometically with the quantity were zero out. I don't want them show up on my spr...

Ignoring 0 values in the series data
How can I get my chart to ignore values which are zero in the series data and not show these? replace zeros with =NA() Manges -- mangesh_yada ----------------------------------------------------------------------- mangesh_yadav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1047 View this thread: http://www.excelforum.com/showthread.php?threadid=38003 You can delete the formulas that are producing zero, or replace them with =IF(formula=0,#N/A,formula) Jerry Alison wrote: > How can I get my chart to ignore values which are zero in the series data a...

I try to assign category based on subject in appointment (code included)
Below i wrote code to assign a categorie to a calendar item based on the word "vrij" in the subject. I think this code should work.. but somehow it doesnt I use Outlook 2003 with macro security set to lowest level. The code is placed in "this outlook session" Any ideas why it doesnt work ?? ==================================================== Dim WithEvents colRDVItems As Items Private Sub Application_Startup() Dim NS As Outlook.NameSpace Set NS = Application.GetNamespace("MAPI") Set colRDVItems = NS.GetDefaultFolder(olFolderCalendar).Items Set NS = Nothing E...

Using contact categories as distribution lists
Using Microsoft Office Outlook 2007 I select a category name "Robinson Family". Actions->Create->New Message to Contact. (get msg "this action apply to all items in the selected groups". Click OK). A new email message window appears but only email1 shows up for each person, not email2 and email3. I found this tip by searching microsoft.com How to Use Contact Categories as Distribution Lists in Outlook http://email.about.com/od/outlooktips/qt/et_category_dis.htm This tip says that all the email addresses for each person will automatically be plac...

Counting Unique Values within a Group
I have a report that lists students by grade and the amount of money donated by that student's family. If the parents are divorced, the student's name is listed twice, once for each parent. I want to count only the unique student record in each grade. DCount is giving me the count for the whole school regardless of what group detail i put it in. How do I count only the unique records within each grouping? Thanks in advance. I would attempt to create a totals query that groups by whatever and counts something. Add this totals query to your report's record source...

Can you expand/collapse columns based on a reportitem/field values
I have a report that is driven by accounting periods. The columns are 1, 2, 3, Q1, 4, 5, 6, Q2...YTD. Currently when the report opens all that is shown is Q1, Q2, Q3, Q4, and YTD and the user can expand from there. What I want to happen is, if we are in Q2, I want columns 4,5,6, Q2 to be expanded. So the report should read Q1, 4,5,6, Q2, Q3, Q4, YTD. Is this possible? Thanks in advance Original Stealth, Certainly can. I created the following dataset: SELECT 'Q1' AS Expr1, 1 AS Expr2, 500 AS Expr3 UNION ALL SELECT 'Q1' AS Expr1, 2 AS Expr...

Why did payment turn into Paycheck category?
I entered my recent paycheck under Wages and Salary category then Gross Pay subcategory. Money asked if I wanted to enter all of the tax items and track them, which I did. Now the payment shows under the Special Paycheck category. I can't click it to see the tax withdrawals either. What exactly happened? Can I have Money set up each payment to allow viewing of the tax withdrawal items and also tracking them? Thanks, Brett Money 2003 Deluxe and Business ...

How to have one year value added to another cell
I have a cell where I date foramtted fill in the date. How to have on another cell the date + one year automatically filled? So, A1 02-04-06 auto: B1 02-04-07 Bart Excel 2003 =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) Bob Umlas Excel MVP "AA Arens" <bartvandongen@gmail.com> wrote in message news:1164983094.802613.212520@l12g2000cwl.googlegroups.com... > I have a cell where I date foramtted fill in the date. > How to have on another cell the date + one year automatically filled? > > So, > > A1 02-04-06 > > auto: > B1 02-04-07 > > > Bart >...

Autoadjusting Y axis minimum
I want the axis to adjust to 2/3 of the lowest value in my chart. How do I get my minimum to change? When I set it to auto it just starts at 0. You have to do it programmatically. Jon Peltier has shared code on the subject. Search google.com's archives of the XL newsgroups. For a readymade solution check the AutoChart Manager add-in available from my web site. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <088801c3c33e$a17899d0$a001280a@phx.gbl>, adam.skin...

SQL to get the lowest unique value from table
Hi I'm looking for some SQL to get the lowest unique value in a table: eg, <table> ID BID 1 1 2 1 3 1 4 2 5 2 6 2 7 3 8 4 3 is the lowest unique value. I would need to return the ID. Any suggestions? thanks Lee "Lee" <lee@digital-interactive.com> wrote in message news:a981d263-7f22-4b14-8493-014559da8590@z41g2000yqz.googlegroups.com... > Hi > I'm looking for some SQL to get the lowest unique value in a table: > eg, > > <table> > > ID BID > 1 1 > 2 1 > 3 1 > 4 2 > 5 2 > 6 2 ...

valid FirstName value
Hello everyone, Anyone know how to search for a valid FirstName in a field? eg.: Himansu Rich Anna Tom .... I don't want junk like this: AA, 0, "Himansu" 'Rich' Any assisatance will be greatly appreciated. Have a nice evening. Thanks, Himansu How about "Gert-Jan"? //Peter "Himansu" <himansu114@hotmail.com> wrote in message news:u3#lXzwZKHA.6028@TK2MSFTNGP04.phx.gbl... > Hello everyone, > > Anyone know how to search for a valid FirstName in a field? > > eg.: > Himansu > Rich ...

Macro to insert to move values of a cell to next cell in the same row
Hello, I would like to know the Macro code for Moving the Values of a cell to next cell based upon values in a particular cell. Ex:- Col A(DATE) Col B(0-30 Days) Col C(30-60days) ColD(60-90day) Jan-05 10 Feb-05 10 Based upon date in Col A i want move the value in col b and c to the next cell.Basically this for aging the items in the work sheet Hi There, Can anyone pls give the code for this query. Pls help ...

adding values of cells
I am trying to use a formula or function that will look at a cells say column A check for a condition, if true, then add the value of another cell that is on the same row to another cell. Yes I know that may be difficult to understand so let me give you an example. A B C D 1 2 dvd 25.23 3 cd 12.25 4 dvd 25.23 5 cd 12.25 6 bat 19.25 ok so what i want to do is look at column A, and look for any cells that have dvd and add the corresponding value from column D to cell B1. So in this case we can see the cell A2 and cell A4 is dvd so I want to add cells D2 and D4 and that...

converting hand drawn sketches into Visio Cross Functional Horizon
Hi , I am new to Visio 2003 . Can anybody tell me how can one convert hand drawn sketches into Visio Cross Functional Horizontal diagrams? Thanks Harsha Are you asking about a way to scan images into Visio and transform them into drawings? Or are you asking how to assemble a Cross Functional diagram? To make a Cross Functional diagram, you first choose how many bands to create. Then you click on the headings for the bands to change the text to the names you want. Switch to the Basic Flowchart shapes in the Shapes Window and drag out the Process shapes to create the steps in you...

Originating Values
I have been workign with GP for a while, but am, not really clear on the difference between Originating Price and Price, Originating Cost and Cost, etc. Can anyone clue me in, I haven't been able to find a good explanation. Thanks Chris I believe this has to do with Multicurrency. 'Originating' is the originating currency. If you are not using Multicurrency the Originating field will be the same as the other field. If your making any changes to the windows using Modifier be careful because these two fields are 'stacked' on top of one another. "Chris Hornun...

Copying Specific Values From Sheet
Hi. Is there a way to copy specific rows from 1 sheet to another? For instance I have a workbook containing 4 tabs. These are labeled "raw data", "Sheet A"," Sheet B", and "Sheet C". On the "raw data" sheet there are 3 columns. One of the columns is titled "Project". In there the values are A, B, C or D (and maybe E someday). I would like to say "if the "Project" column contains and "A" copy everything on that row to "sheet A". Basically I want to take all t he information that has a proje...

Sorting Alpha-Numeric Values
I would like to sort data that have aplha numeric values. When I use the sort command, the numbers wind up getting sorted by place values instead of the actual value of the number, kind of like alphabeticaly except with numbers instead of letters. For instance, I would like to these numbers sorted like this: 17A 36B 265F 1492C 1609A Instead, Excel sorts them like this: 1492C 1609A 17A 265F 36B It appears that the suffixed alpha characters alter the way the numbers would normally be sorted. Any ideas how I can sort them more logically/numericaly? Thanks! Larry ...

Change "Y" axis from integer to text
Excel 2k Is it possible to change the range on the Y axis from numbers (0-50-100) to text (low-medium-high) without just placing text boxes on the chart? Please let me know how!! You can simulate the effect. See http://www.peltiertech.com/Excel/Charts/DummySeries.html#VertCatAxis -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <077501c48c13$dde7a6b0$a401280a@phx.gbl>, anonymous@discussions.microsoft.com says... > Excel 2k > > Is it possible to change the range on the Y axis ...

Repeat values in column A a certain number of times depending on the value in column B
Imagine a set of data as set out below: Column A Column B Apples 24 Pears 36 Oranges 8 I want to poplulate a column (for exampel column A on a new sheet), where the values in column A will be repeated as many times as the value in column B Thus the first 24 rows will say Apples, the next 36 rows will Pears, Oranges. I need a formula that recogonizes that when it gets to row 25 that it should no longer need to copy Apples, but then switch to pears. This might sound like an unusual request but if I can grasp a way to do this I can create the table I need and populate the rest...

different colors for x-Value axis
I want to use different colors for X-Values in X-axis of a chart. Is it possible? Example I-1, I-2 black II-1, II-2, II-3 grey III-1, III-2 red IV pink V-1, V-2, V-3 green -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan The best solution is that, Excel brings the formatting of x-axis value from source cell. -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan "Khoshravan" wrote: > I want to use different colors for X-Values in X-axis of a chart. Is it > possible? > Example > I-1, I-2 black > II-1, II-2, II-3 grey > III-1, III-2 red > IV pink >...

Adding 2nd Y-axis to Excel2003 chart
I am making a simple line chart with two types of column data. I want to represent them with separate Y-axes. However, when I select the two columns of data and go through the Chart wizard the secondary Y-axis option is inaccessible. Excel 2003 help is no help as it tells me to go to the Format Menu, click Selected Data Series then click the Axis tab, but there is no such option under my format menu. How do I select the secondary Y-axis option? Hi, The chart wizard does not give you the option to place the selected data on 2 axes. You will have to do it once the chart is cre...

Categories and Payees
Hi, I hope someone can help me. I download my credit card transactions to Money and I have many charges for FedEx as they are the shipping company I use. For some reason, Money does not want to recognize the "repeat" PAYEE and automatically assign my CATEGORY (which is shipping expenses) to each transaction. It does when it is Exxon and it puts the gasoline category but not the FedEx ones. Can I automatically update all the transactions that have the same PAYEE to the same CATEGORY? Thanks In microsoft.public.money, Miriam wrote: > >I hope someone can help me. I do...

Conditional Max value
Hi, i have 2 columns and i'm trying to calculate the conditonal maximum from column one. These are the columns : 47 7 44 7 71 7 58 7 214 4 22 4 54 7 1 7 45 7 21 7 and i try to find a formula that gives the maximum in column one, where column 2 has value 7 in this case this would be 58 Marc Marc, Use the following array formula: =MAX(IF(B1:B10=7,A1:A10,FALSE)) Change the range references to match your data. This is an array formula, so you must press CTRL+SHIFT+ENTER rather than just ENTER when you first enter the for...