Y Axis : Dynamically Hardcode Min/Max Values?

I have a number of data tables in a summary topsheet. I have added a dynamic 
table that uses the indirect function to populate this grid and generate a 
graph for only the data our execs want to see.

If I choose the "Auto" feature to assign the Min/Max Value of the Y Axis, 
some of the lines in the chart appear too flat... I need to be able to assign 
Min/Max Values for the Y-Axis based on my criteria, not Excel's default 
selcetions (i.e. the min value should almost never be 0 on these graphs, but 
from chart to chart, some are 10 some would be 10,000).

I am using Excel 2003 SP3 in XP.

Thanks for your help!

Regards,
Ray

-- 
"Trying to make reports so easy... even a monkey could run 'em!"
0
2/21/2008 6:00:05 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
549 Views

Similar Articles

[PageSpeed] 10

The technique is outlined here (it requires VBA):

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

Tushar Mehta has a utility on his web site called Auto Chart Manager which 
handles this for you (http://tushar-mehta.com).

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


"RayportingMonkey" <RayportingMonkey@discussions.microsoft.com> wrote in 
message news:ECB6E688-75D2-4570-88B8-064196F163CA@microsoft.com...
>I have a number of data tables in a summary topsheet. I have added a 
>dynamic
> table that uses the indirect function to populate this grid and generate a
> graph for only the data our execs want to see.
>
> If I choose the "Auto" feature to assign the Min/Max Value of the Y Axis,
> some of the lines in the chart appear too flat... I need to be able to 
> assign
> Min/Max Values for the Y-Axis based on my criteria, not Excel's default
> selcetions (i.e. the min value should almost never be 0 on these graphs, 
> but
> from chart to chart, some are 10 some would be 10,000).
>
> I am using Excel 2003 SP3 in XP.
>
> Thanks for your help!
>
> Regards,
> Ray
>
> -- 
> "Trying to make reports so easy... even a monkey could run 'em!" 


0
jonxlmvpNO (4558)
2/21/2008 6:06:10 PM
Reply:

Similar Artilces:

Can I abbreviate one value in a data series?
I've got a chart where one value (8,300) greatly exceeds all the others. Is there a way to abbreviate this value so the other data points show better in the graph? Hi, One way is to break the Y axis, have a look at these examples of how to http://peltiertech.com/Excel/Charts/BrokenYAxis.html http://tushar-mehta.com/excel/newsgroups/broken_y_axis/tutorial/index.html http://www.andypope.info/charts/brokencolumn.htm Cheers Andy CMEknit wrote: > I've got a chart where one value (8,300) greatly exceeds all the others. Is > there a way to abbreviate this value so the other da...

Return values that sum to a known value
I have a list of data and would like to know if there is a formula that would return any items from that list that sum to a known value. Have a look at this thread for something similar: http://www.microsoft.com/office/community/en-us/default.mspx?pg=7&cat=&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&fltr= Regards, Tom "lmattern" wrote: > I have a list of data and would like to know if there is a formula that would > return any items from that list that sum to a known value. ...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

Code about setting Y Axes scale.
I couldn't understand the lines between 35-42, why he thought the dbMinScale after the change is the one he want. What the logic of this code section. Any one who could understand please help me. thanks in advance!:) Private Sub USP_SetYAxesScal(ByVal i_strWSName As String, ByVal i_strChartName As String, Optional ByVal i_intDecimal As Integer = 2) Dim dbMaxScale As Double, dbMinScale As Double, i As Integer, j As Integer Dim xTargetChart As Chart, vTemp() As Variant, blnInit As Boolean, dbMax As Double, dbMin As Double 1 On Error Resume Next 'get max/m...

How do I make X-values of a chart dependent on values in cells?
Greetings. I have a chart which can go from x-value 0 to x-value 200. However I'd like to be able to input min X-value into a cell, and a max X-valu into a cell, and the x-value in the chart changes to reflect that. Is it possible to do that? Thanks for any replies. K -- Message posted from http://www.ExcelForum.com Hi, There is no automatic way to do this but take a look a Tushar's AutoChart Manager for a possible solution. (http://www.tushar-mehta.com/) Cheers Andy Kashgarinn < wrote: > Greetings. > > I have a chart which can go from x-value 0 to x-value 200...

set value of a group of activex control points
Have a spreadsheet that has some 20+ activex control points (option buttons). Is there a way to group all these controls together & set their initial values the same? Trying to setup a "reset" type of operation that would clear all control points. I can do them individually via properties, but it's too time consuming. Any suggestions? ...

primary & secondary axis
How do you space the primary and secondary axis equally. I've been fooling around with "Format Data Series>Options", but I can't get them equally spaced and with the same size. Is there something I'm missing or is Excel 2000 just trying to intimidate me???? Connie What to you mean by 'equally spaced' Same range (Max1=max2, Min1=Min2) ? Same interval between gridline? Tell us more -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Connie Martin" <ConnieMartin@discussions.microsoft.com> wrote in message news:...

Storing distinct values in an array
Hello .. in the speadsheet, i have a column containing a series of numbers maybe of them repeated multiple times... i would like to store all distinct values in an array any ideas on how i could do that ... eg , if these were the numbers going down column then 1 3 5 7 5 3 5 7 5 4 3 4 5 7 8 5 3 .. store 1,3,5,7,9,4,8 in an array, in no specifi order thank you shimee -- shimee ----------------------------------------------------------------------- shimeel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1583 View this thread: http://www.excelforum.com/showt...

Populate cell with a value if another cell has a certain value
All, good morning. i have a issue, i need to populate cell E4 with a value CX/025966 when cell F4 has a text Bond Street. Is there a simple way of doin this? EXCEL 2007 Try:- =IF((F4="Bond Street"),"CX/025966","") If my comments have helped please hit Yes. Thanks. "B2ORL" wrote: > All, good morning. i have a issue, i need to populate cell E4 with a value > CX/025966 when cell F4 has a text Bond Street. > > Is there a simple way of doin this? Use IF() =IF(F4="Bond Street","CX/02...

X axis location
I created a chart with x-axis at the bottom. How do I make the chart have the x-axis on top instead? (Y axis can stay on the left side as it is now). thanks T. Hi, I think if you click on the Y axis, go to format axis, and check the "Value (x)axis crosses at maximum value" at the bottom, you'll be set. jeff >-----Original Message----- >I created a chart with x-axis at the bottom. >How do I make the chart have the x-axis on top instead? > >(Y axis can stay on the left side as it is now). > >thanks >T. >. > ...

Show zero values
When a formula returns a zero value, the zero value is not being displayed. I have reviewed the following: - conditional formatting - zero values are checked (Tools/Options/View) - stepped through my VB code (it inserts formulae based on a Worksheet Change event) - locked and hidden values unchecked with and without protection (this should have no effect when the sheet is unprotected) Are there any other circumstances that result in a zero value not being displayed? Are there any other reasons for this occurrence? Cheers John Check also normal cell formatting! there are formats that hide ...

CButton::GetButtonStyle()
hi! is it possible that the function GetButtonStyle() returns one single style value? where can i get information when to use a mask and when to use the plain value? how about other controls/windows? actually i am a little bit confused about this... thanks, philipp "Philipp Huber" <philipp.huber@innval.com> wrote in message news:bonpkq$7hi$04$1@news.t-online.com... > hi! > > is it possible that the function GetButtonStyle() returns one single style > value? I'm not sure what you're asking, but GetButtonStyle returns the LOBYTE of the GetWindowLong/GW...

Comparing values #2
I have two sheets in a workbook. The first column in each sheet has part numbers. I would like to compare worksheet1 column a to worksheet 2 column a and identify duplicate values. I have not been able to make this work. -- Dirk Chip Pearson has lots of ways to work with duplicates/uniques at: http://www.cpearson.com/excel/duplicat.htm Dirk_Bob wrote: > > I have two sheets in a workbook. The first column in each sheet has part > numbers. > I would like to compare worksheet1 column a to worksheet 2 column a and > identify duplicate values. I have not been able to make...

How do I put a break in an axis in a chart?
Is there any way to put a break in an Excel chart axis? I'm using Excel 2003 and Excel XP. I have some graphs where one or more data points is substantially higher than other data points; it's difficult to see the differences between the lower data points. What I would like to do is have the y-axis go from about 0-20, have a break in the graph, and resume the graph at about 40 (my highest data point). Here's an example and some links to others: http://peltiertech.com/Excel/Charts/BrokenYAxis.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Service...

Chart templates treates value axis as a category axis
I saved a Chart Template [Line Plot Type] that uses a horizontal value axis, but when I try to use it in a new workbook, the data is plotted with a horizontal category axis? In a line chart, the horizontal (X) axis IS a category axis. Only an XY chart has a value horizontal axis. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "FHW at EAD" <FHW at EAD@discussions.microsoft.com> wrote in message news:E52B4E4C-9DC6-41F5-84B2-3BC637C56591@microsoft.com... >I saved a Chart Template [Line Plot Type]...

Assign a value to a cell
I'll apreciate your help with this in A1 i have a value in days that is used to make several calculations but I need it to change if a specific condition is given something lik this: A1=300 If b1<a1 then a1=b1 where b1 is a value calculated somewhere else. Thank you for your help. Rodolf -- Message posted from http://www.ExcelForum.com Hi try the following formula in A1: =MIN(B1,300) Frank > I'll apreciate your help with this > > in A1 i have a value in days that is used to make several > calculations, but I need it to change if a specific condition is > giv...

Cntl Key move/select to change in value
Just another convenience key sequence to make my life easier. Sure, maybe I could do it with a macro. But, it seems like a nice little obvious addition to regular functionality. Cntl-Alt down arrow moves you down to where the value changes. Cntl-alt-shift selects to change. Up, down, right, left of course. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the s...

IF statement with FALSE value being text AND cell reference? #3
Once again, figured it out on my own. Oh, and thanks for the repl Frank. Needed to use the ROUND function: =IF(D9>1,D9*D8,"<" & ROUND((D9*D10),2) -- jcob ----------------------------------------------------------------------- jcob2's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1560 View this thread: http://www.excelforum.com/showthread.php?threadid=27157 ...

Check for Values
Hi I have 2 tables, requirements and stock on hand. In the requirement table, I have a part number, qty and due date and a related record in the stock on hand table using part number showing current stock. My question is can I use a query (and how!) to tell me a) I have enough stock to fulfil the requirements and b) If not at what date will I not be able to fulfil the requirements and by how many? Have been working on this for a while, and cant work it out Regards Alec Alec You are there, we are not ... the following may not fit your situation ... If you have a [Par...

Dynamic Image references in a cell
I am exporting a report from Access 2007 to Excel 2007 containing product information. I have an image for each product. Access does not export Images to Word or Excel. I have the URL or local path for the image standardized so the path is C;/temp/image[ID Num].jpg or http://www.temp.com/images/image[ID num].jpg. I have [ID num] in Column B for each row. I want to reference the image column A of each row. How do I get the image to display in the cell? -- Jim Fidler ...

Subclassing dynamically created controls
I have a created an edit box dynamically using the Create function in a class derived from CTreeView ( Explorer Style - CLeftView ). Now I want to change the color of the edit box . I guess I need to subclass this window using SubClassWindow( ) . I have derived a class from CEdit and using the derived class to create the control . Can anyone please help me how to Subclass the edit control so I can change its background colour . Thanks in advance . Sujay -- Sujay Ghosh Bangalore, INDIA Sujay Ghosh wrote: > I have a created an edit box dynamically using the Create function in a ...

Save only values of one sheet
Hi, If I save a workbook as .CSV, Excel only saves the values of the current sheet in a *.CSV-file. Can I do EXACTLY the same - saving only values of the current sheet - in an *.XLS file (and of course in an XLS-format!). Eventually, formatting elements (like background and font colors) may also be saved. Thanks for any help. Anja. Just copy the sheet to a new workbook (right-click on the sheet tab, select Move or Copy from the menu, select (new book) from the To book dropdown, and check the Create a copy option, then OK), and save that new workbook. -- HTH RP (remove nothere from the...

Trouble when creating secondary axis
I am trying to create a column chart with two columns (with differen colors) for every date (dates are on the x-axis). Both series show u as desired (i.e two different color bars right next to each other fo each date) until I make one series set to the secondary axis. When I d this, the secondary axis values appear on the right, but the bars no merge into a single stacked-looking column. How can I make sure the columns for each data series stay independen when adding the secondary axis. Also, I noticed that when using any of the 3D columns chart types th axis tab is not available, does tha...

links+hold old value
i want to link 2 cells A + B cell A is a calculation (D+E) copy + paste works for that. But i want to change values D+E reguarly,and at the same time create new cells for the new answers(i sill need the old answers) Alan Hi not really sure what you're trying to achieve. Could you give a specific example -- Regards Frank Kabel Frankfurt, Germany mavman wrote: > i want to link 2 cells A + B cell A is a calculation (D+E) copy + > paste works for that. > But i want to change values D+E reguarly,and at the same time create > new cells for the new answers(i sill need the old...

pasting values #2
I am working on a template which evaluates all the markets in which we do business in. I have information on the some of the markets but not all of them so i have filtered by NonBlanks so display all of the valid information. I now have found new information on the countries that have NonBlank and would like to paste them to my sheet as they are in the same order of my filtered template. However, when I paste the information it disregards the filter and pastes it on the sheet containing all the data. Naturally, the information is not pasted in the right cells. I have gotten arou...