• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding horizontal lines to a dynamic chart

C

comp764

Member
Hello everyone,

I would like to know if there is a way to add horizontal lines the shows the acceptable limits on a chart. Everything I have applied to these charts I have learned from reading and doing some research on the Internet.


Here’s what I have so far. I have a spreadsheet where the chart is getting the info. Which consist of: Temperature, Ph, Viscosity, and Solids. I am using a line chart.

The chart has a moving average, a moving range for min and max. So when data is entered, the lines on the chart moves up or down depending on the values entered.


I entered the following formulas in chart series:

For average - ISNUMBER(‘SHEET1’!PH)*AVERAGE(‘SHEET1’!PH)

For Min - ISNUMBER(‘SHEET1’!PH)*MIN(‘SHEET1’!PH)

For Max - ISNUMBER(‘SHEET1’!PH)*MAX(‘SHEET1’!PH)


These formulas are working fine for my moving ranges. Now, I would like to add two horizontal lines that don’t move. One for the low-end specs and one for the high-end specs. This way, you can see when you’re over the high and low acceptable spec limits. This is a dynamic chart so it updates as Info is added. I can add another column with the high and low spec limits, but need it to update it self rather than adding this when the data is entered because I will be using four charts one each category. Right now I am using excel 2000. I purchased excel 2007 but haven’t received it yet.


Any suggestions or ideas would be greatly appreciated.

Thanks Dan
 
Hi comp764,


Welcome to the forum first of all..


Off course you can add a horizontal line(s). What you need to do is to add a new series having two point, one having configuration (0,y) and other having (x,y). This will give you a straight line having one end on y axis and slope of zero. Plot this series of secondary axis (both x and y), select the series and change chart type to scatter plot. select the secondary x axis for this scatter chart and change the scale from 0 to 1. you will a horizontal line that spans across the chart. hide the secondary axis to give it feel of a limit for your primary chart. Method is explained in detail here:


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


If it is difficult to follow then upload the file and it will be configured for the same.


Regards,

Faseeh
 
Hello Faseeh,

I don't know if what I'm doing wrong but I can't seem to get the horizontal line to span across the chart. I'm able to get on the left side of the chart after I follow the directions of the link. I don't know if it works on excel 2000 because that what I'm using till I get my copy of excel 2007.


Thanks, Dan
 
Hello Faseeh,

I finally got it to work for me. I had to click on Custom in the Format Error Bars dialog box and add the appropriate number in my case I added ={12.2} to get to span all the way across the chart.


Thank you so much for your help. You've been such a great help, I have been searching for the solution to this problem for days now and finally found on this forum.


Thanks again,

Dan
 
Hi Faseeh,

I took a break for the computer for a while. Okay, so I have the charts on another worksheet and I have it linked to a cell where my charts are. So when I enter a number in the cell, it shows me the amount of data on charts that I entered in the cell. So if I enter 20 in the cell it shows me the last 20 data entries on the chart.


The problem I am having now is that the more data the chart show the horizontal lines start to disappear.


Dan
 
Hi faseeh,

Never mind, I just enter a lager value in the Format Error Bar Dialog Box and it seem to work. Ended up entering a value of 1000.


Thanks, Dan
 
Hi Comp764,


Glad to listen that things worked for you. I think there should have been no problem at all if you set you secondary x-axis's min to 0 and max to 1! HTH Keep visiting.


Faseeh
 
You must log in or register to reply here.
Back
Top