Scaling XY charts with suppressed starting value

The autoscaling feature of the Excel graphing function seems to have 
(limited) mind of its own. I'm trying to graph Y-values in the -1.5 t
1.5 range and X-values from 190 to 410 with the minimum from 190 to 29
and the max value from 290 to 410.

The autoscaling of Y works fine, but the X-axis always start at 0
while the high value properly sets the maximum. Of course, I could fi
the minimum at 190, but that is not what I had in mind.

Does anyone know how to overcome this limitation without using a macro

--
Message posted from http://www.ExcelForum.com

0
5/20/2004 3:55:22 AM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
617 Views

Similar Articles

[PageSpeed] 7

Per -

You need to make an XY Scatter chart, not a Line chart. The series can 
be formatted the same, with markers and connecting lines. But in a 
Scatter chart, the X axis is as easily rescaled as the Y axis.

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

PerNielsen < wrote:

> The autoscaling feature of the Excel graphing function seems to have a
> (limited) mind of its own. I'm trying to graph Y-values in the -1.5 to
> 1.5 range and X-values from 190 to 410 with the minimum from 190 to 290
> and the max value from 290 to 410.
> 
> The autoscaling of Y works fine, but the X-axis always start at 0,
> while the high value properly sets the maximum. Of course, I could fix
> the minimum at 190, but that is not what I had in mind.
> 
> Does anyone know how to overcome this limitation without using a macro?
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 

0
DOjonNOT (619)
5/21/2004 1:26:11 AM
Jon:

Oh, if it were only that simple. As the title of my thread indicates, 
AM using the XY scatter chart (almost always do because of the natur
of my data).

If you want to see the problem for yourself, make an XY chart with 36
x-values (A2..A362) and allow for entry of a starting value and a
ending value. Use NA() as an x-result if x exceedes the maximum. Ente
in A3:

=IF(A2<$D$2,A2+1,NA())

where $D$2 contains the max-x-value and A2 contains the min-x-value.

In B2, enter 

=sin(A2*pi()/180)

for the y-values. Copy the formulas to row 362 and create the X
chart.

With 0 in A2  and 360 in D2, the plot is a nice sine wave.

Enter 300 in A2 and see the autoscaling work as one would expect. The
change A2 to 299 and see the autoscaling fail.

I still believe this is a bug. The wrong algorithm is use

--
Message posted from http://www.ExcelForum.com

0
5/21/2004 2:11:03 PM
Per -

Sorry. Your description is just like the description of someone using 
the wrong type of chart for their data. 99 times out of 100, my 
assumption would have been correct.

You've just discovered a known and documented behavior of Excel. 
According to Knowledge Base article 101939, How Chart Axis Limits Are 
Determined:

<quote>
If the difference between yMax and yMin is greater than 16.667 percent 
of the value of yMax, the automatic minimum for the y-axis is zero.

If the difference between yMax and yMin is less than 16.667 percent of 
the value of yMax, the automatic minimum for the y-axis is the first 
major unit less than or equal to the value returned by the following 
equation:

yMin - ((yMax - yMin)/2)
</quote>
(http://support.microsoft.com/default.aspx?scid=kb;[LN];101939)

Your threshold is between 300/360 (a difference of 16.667%) and 299/360 
(16.944%). Whether you believe this is the wrong algorithm (and I agree: 
who wants all that white space, which per the formula can be nearly 
83.3% of the chart), Excel is doing what it was designed to do. 
Ironically, prior to Excel 97, the lower limit was always computed the 
"correct" way.

There's no way to turn off this behavior, but you can work around it. 
Tushar Mehta (http://tushar-mehta.com) has an AutoChart add-in that 
takes care of this, by linking the axis parameters to cells that you 
specify. If you want to see the code to do this, check out this page on 
my web site:

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

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

PerNielsen < wrote:

> Jon:
> 
> Oh, if it were only that simple. As the title of my thread indicates, I
> AM using the XY scatter chart (almost always do because of the nature
> of my data).
> 
> If you want to see the problem for yourself, make an XY chart with 361
> x-values (A2..A362) and allow for entry of a starting value and an
> ending value. Use NA() as an x-result if x exceedes the maximum. Enter
> in A3:
> 
> =IF(A2<$D$2,A2+1,NA())
> 
> where $D$2 contains the max-x-value and A2 contains the min-x-value.
> 
> In B2, enter 
> 
> =sin(A2*pi()/180)
> 
> for the y-values. Copy the formulas to row 362 and create the XY
> chart.
> 
> With 0 in A2  and 360 in D2, the plot is a nice sine wave.
> 
> Enter 300 in A2 and see the autoscaling work as one would expect. Then
> change A2 to 299 and see the autoscaling fail.
> 
> I still believe this is a bug. The wrong algorithm is used
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 

0
DOjonNOT (619)
5/22/2004 1:36:23 AM
Jon:

Thanks a lot for the references. I tried to find help on the Microsoft
support site, but my imagination was not good enough to look for the
"right" stuff (I rarely ask for help).

My spreadsheet is used worldwide in a scientific instrument
application, so I am trying to stay away from macros and add-ins. A
customer requested modification brought this up, now I'll just set the
minimum to a fixed value and live with the limitation.

Again, thank you for your verification.

Best regards
Per Nielsen
www.pion-inc.com


---
Message posted from http://www.ExcelForum.com/

0
5/22/2004 3:33:14 AM
Reply:

Similar Artilces:

refresh pivio chart
i have excel work book with multiple sheets, I want to put a button on every sheet so I could click the button and refresh the data, I am suing PIVIO Chart. Any one know how to put a button and what code I will write behind it. Thanks in advance. ...

Chart: range info from seriescollection?
I would like to fill a combobox (Rowsource) with data that are used in for instance the XValues or Values of a Seriescollection of a graph. "ActiveChart.SeriesCollection(1).Values" does not give the right type (I was hoping for a string type of info like "Sheet1.Range(a1:a10)"). How to get a Range to be used as Rowsource for my combobox??? Thanks for your suggestions, JB The Values and XValues will return an array of values. To get the range address like this, you will need to parse the series formula. Or you could use John Walkenbach's class module for extractin...

PDF scaling
Hi, my publication is on A7 format and I need to publish it as A5 format. With normal printers there is no problem, just set the scaling to 200%, but how could one achieve that with Publisher's PDF export feature? Thanks, Jan Jan, I use Pub 2000, sans built-in PDF but if you select File, Printer setup, is PDF listed? If so, can you then select Printer Preferences and there may be "Scaling". -- Don Vancouver, USA "Jan Kucera" <miloush@community.nospam> wrote in message news:E122321E-911E-4580-A8FF-2D522524FE7E@microso...

'Counter' value of the 'Perflib' subkey
I have a program which queries the 'Counter' value of the 'Perflib' subkey in the Windows registry using RegQueryValueEx. It used to work fine but now the debug build of my program is broken on Windows 2008 Server R2. The 'Counter' value is a REG_MULTI_SZ type data and is terminated with two NULL characters. However, I found out for Windows 2008 Server R2, the value returned from RegQueryValueEx has an extra character(0xcdcd) after the two NULL characters, which ends the 'Counter' string value. And that extra character causes my program to fail ...

Start with a specific worksheet when a Workbook is opened
i have a work book Call *i* and when open i want it to always start at *Data* sheet i found th following code but i dont know what else i need to modify. Private Sub Workbook_Open() On Error Resume Next Application.Goto Reference:=Worksheets("Data").Range("A1"), _ scroll:=True If err = 9 Then 'actually is subscript out of range MsgBox "Requested worksheet ""Data"" was not found " _ & "by Workbook_Open in ThisWorkbook." End If Worksheets("Menu Sheet").Activate Sheets("Data").Move Before:=Sheets(1) '...

Interactive Pie chart in PowerPoint
I have created an interactive Excel pie chart that enables the user to change the year to display with a set of radio dials within a group box. My colleague likes it so much that he was asking whether he can insert it in a PowerPoint presentation. It sounds like a non starter to me as I don't know how to make PowerPoint mimic Excel in this fashion. I was going to offer inserting a hyperlink in PowerPoint that takes him to the Excel worksheet where this pie chart resides. Alternatively, I was going to suggest inserting in his PowerPoint presentation static pictures of the chart. A...

Dynamically Scale Gantt Chart Time Scale
Excel 2007 (or 2010 Beta, if that's the only way to do it) I've developed a Gantt Chart using a stacked bar chart. It works great. Is there any way to have the horizontal axis (or vertical axis, I get confused with the terms in a stacked bar chart) expand in and out based on the MIN and MAX of the dates in the plotted range? Excel 2007 Gantt Chart Scale X axis with macro. http://www.mediafire.com/file/zdzj4tz2mmo/01_22_10.xlsm Thanks! Will try that out. "Herbert Seidenberg" wrote: > Excel 2007 Gantt Chart > Scale X axis with macro. > http:...

How to import flow charts made in excel to visio as editable text
I have created flow charts using drawing objects (rectangles, circles etc) in excel. Now I want to import these diagrams in visio, such that I am able to edit the text entered in the rectangles etc while preparing the charts in excel. I have tried it by copying the individual rectangles / circles in excel and pasting them in visio. But they appear in visio as uneditable objects (similar to jpg image of text which cannot be edited as text). Kindly advise on how to import the excel diagrams as editable visio diagrams. ...

default values #2
Hi, Upon openeing a new worksheet I would like to have the celformat of the entire worksheet to have following default value: Number negative values in red 2 decimals after comma points after "thousands".... I can do it manually but in most cases I use the same settings.. Can this be done?? Thanks JP Check out the "Create a template for workbooks or worksheets" topic in XL Help, and follow the instructions for creating a default template. In article <BjpAh.228965$6S2.77082@blueberry.telenet-ops.be>, Jean-Paul De Winter <jp.dewinter@telenet.be> wrote: >...

Chart not visible -suspect scale setting?
All of a suddend when I create a chart the chart area is empty. If I change the type to 3d , I will get a small 3d chart on the middle left of the chart area. I suspect the default scale has changed somehow. can anyone help please? -- ...

Linking Multiple Charts
I'm trying to link a chart, or a page, from one excel spreadsheet to chart or page on another excel spreadsheet. I have multiple excel spreadsheets with many charts inside. Many o the same charts are in more than one spreadsheet. For example, if want to update the CPI chart I would like to be able to update it i one of the spread sheets and have all the CPI charts automaticall updated in the other spreadsheets, since they are identical charts -- Message posted from http://www.ExcelForum.com Does anyone know if this is even possible -- Message posted from http://www.ExcelForum.com ...

Horizontal Line Chart
I have some data in three cells. A1 is the minimum value, A2 is the current value, and A3 is the maximum value. How can I create a simple graphical representation of the location of the current value relative to the min and max similiar to the below | X | min value cur value max value It is possible for the curr to exceed or equal either the min or the max as it is dynamic based on entries from a lot of other cells. Thanks in advance for any assistance. ...

start w/a BLANK business card Avery 8869
I do NOT want a template that is already designed. I want a BLANK business card template that will work with Avery 8869 or Avery 8373. HELP! PLEASE! Publisher 2007? Page setup, Advanced on the right, Layout Type, Multiple pages per sheet, click OK. -- Mary Sauer http://msauer.mvps.org/ "Janez" <Janez@discussions.microsoft.com> wrote in message news:7EE9434A-F735-464A-A1AF-E2658A71C823@microsoft.com... >I do NOT want a template that is already designed. I want a BLANK business > card template that will work with Avery 8869 or Avery 8373. HELP! PLEASE! ...

Trouble. Visio 2003 SP1 ORGANIZATION CHART WIZARD #2
I got a trouble using Visio 2003 SP1 ORGANIZATION CHART WIZARD. I want to import org data from Exchange using ORGANIZATION CHART WIZARD and customize the fields to load and draw in the shapes. So, I found HOWTO articles in MSDN and also in Office Online Assistance. The article name - "Create an organization chart from a data file using the command line or Run method" http://office.microsoft.com/en-us/assistance/HP010384221033.aspx. Also I used examples from Microsoft Office Visio Code Librarian to write a C# solution. This solution will help me to automate the process of generation...

Make Bit field control value of float field
Hi. I've made bit field with the default options of yes and no: new_Q1 Then i've made a field to collect a amount of points, incase the bit is set to yes: new_Q1p. If i select the format of the bit value to "list" my script works fine. If I select "check box", it does not work. Can any one help me modyfi the script to work with the check box? Thanks Regards, Jacob Mondrup This is the script I use: ______________________________________________________________________________ var point = 0; var get_status = parseInt(crmForm.all.new_q1.DataValue); switch (get...

Conditional Charts
Hi all, I have a chart that has many different series, 2 y-axis, and othe jumbled things. Its a chart of a 12 month period, having volume o parts, types of problems, etc... What I want to be able to do, i depending on which months have volume, display only those months. So basically, with a chart that has many series, how can I limit wha is displayed from those series based on just one series (the volume)? Is it possible? Please let me know if I am not being clear. Thank you, Ale -- maluguitaris ----------------------------------------------------------------------- maluguitarist&...

Getting the min value that matches multiple conditions?
My data table looks like this: A B C --------------------------------------------- 1 Region Gas Sales Price 2 US Air $35,000 3 Asia Nitrogen $50,000 4 US Air $38,000 5 US Nitrogen $39,000 6 US Air $40,000 7 US Air $55,000 8 Asia Air $51,000 9 Asia Nitrogen $52,000 etc... There are 1300 rows, not sorted. What formula can I use to get the minimum sales price for a particular combination of region and gas? Thanks jg7...

How to draw chart: log scale on X axis, natural scale on y axis?
How to draw chart: log scale on X axis, natural scale on y axis? sample data is given below. This chart is used for Civil Engineering? X axis Y axis Sieve size % passing 26 mm 100 22 mm 95 16 mm 81 12 mm 75 10 mm 66 4.8 mm 52 2.4 mm 42 1.2 mm 32 0.6 mm 18 0.3 mm 16 0.15 mm 9 0.09 mm 3 Right-click on the X-axis, select Format Axis, choose the "Scale" tab, and tick the "logarithmic scale" box. You will, of course, need to be using the XY chart (confusingly named scatter), rather than a line chart. -- David Biddulph "Pratap D. chavan" <Pratap D. chavan...

Chart Question
I have a chart that tracks different nutritional information such as carbs, sugars, fat, calories, etc. Each chart only tracks one of the items for one individual. Each item has a maximum value based on a 2000 calorie-a-day diet. Is there a way in a 3D bar chart to indicate that an item is above it's recommended daily value. For example, on a 2000 calorie-a-day diet, a person should keep their total grams of fat for each day to 65 or less. I would like to have the chart change the color of the day that anyone goes above the recommended maximum. If someone eats 82g of ...

Excel 2003- Charts- date and time format on x-axis of XY chart
Hello, I am trying to plot an XY graph with x-axis data in date and time format but Excel keeps counting the x-axis data as "data labels". It is in the format mm/dd/yy HH:mm ex) 10/28/06 17:00 Instead, Excel appears to be turning my XY plot into a line plot (for example, the first date is assigned an x-value of 1, the second value assigned an x-value of 2 and so on so that each date/time x-value is evenly spaced). I would like to find a way to plot this data so that it is spaced relative to date and time. Are your data values actually date and time, or are they text? Does ...

Excel Finding instances of a value in defined range
Can some one help me with a small excel problem.. I have a excel sheet in which I have.... wait... why dont you check th attached file and then tell me how do I find out the repeated instance of a check.. For example , I want column H to say - "Instance Correct" if a chec no. X has been found 2 times in the defined range A1-F7 , and if th amount mentioned under deposit column of Bank is same as amount mentio in Deposit column of company.. If the check number is found 2 times bu the amount is different then column H should say - "Instance Wrong", i the check number is foun...

How move a chart with arrow keys
I can move a chart around the spread sheet fine w/ the mouse, but it is difficult to line this chart up w/ other things in the spread sheet. How do I move the chart w/ the arrow keys to fine tune its position? I appreciate your help, -John Unfortunately the cursor keys will only allow you to move between elements rather than re-position the chart. However, you can use the ALT key to snap to the worksheet grid when dragging the chart with the mouse. ------- John Mansfield http://www.cellmatrix.net "John" wrote: > I can move a chart around the spread sheet fine w/ the mous...

Can I change text in Pop-Up Boxes for chart items?
I know that I can change the data labels, but I have hundreds of items in the xy chart and want to be able to scroll over a xy data point and have text show instead of the xy coordinates. Thanks! Hi mike, Check out Tushar Metha's Hover Chart Label addin. http://www.tushar-mehta.com/ Cheers Andy mike wrote: > I know that I can change the data labels, but I have > hundreds of items in the xy chart and want to be able to > scroll over a xy data point and have text show instead of > the xy coordinates. Thanks! -- Andy Pope, Microsoft MVP - Excel http://www.andypope....

XY Scatter Chart Not Showing X Axis
I'm trying to create a quadrant analysis. I have the four quadrants as stacked columns and the real data as a XY Scatter. The x axis for the XY Scatter is not showing. The real data series are marked as the secondary axis. Does anyone know how to make the X axis appear? Usually Excel gives you both secondary axes when you add an XY series to a column chart. Go to Chart menu > Options > Axes, and check the box for the secondary X axis. If you're using Excel 2007, it's found on the middle of the three charting tabs, under Axis. - Jon ------- Jon Peltier, Microsoft ...

Excel Chart to GIF Format
What is the best way to convert an Excel chart to a format I can display on a web page. I will need to make the chart smaller too. I assume *.gif is the format I want the converted to. I tried this, but quality not very good. Thank you Select the Chart, Copy the chart, Open Microsoft Office Picture Manager, select Edit then Paste, the Chart should appear as "new Picture", select it then pick File, Export, and then Pick the type of file format. JPG would probably be the one desired. pestocat wrote: > What is the best way to convert an Excel chart to a format I can displa...