April 14, 2014 9:02 AM Subscribe

I'm using Excel 2010 and I'm making several x/y scatter graphs to plot QA results. I want to show on the graphs an upper and lower allowed limit for the data.

Each data plot will have it's own graph with less than ten data points which use a numeric value for the "Y" data point, and a date value for the "X" axis. The graphs I have made in Excel 2010 are easy to make and look good. Now how do I add those limit lines? The value for each line must be a function of some data points in the spreadsheet.

I would have thought that by now Excel would have made this easy!

Each data plot will have it's own graph with less than ten data points which use a numeric value for the "Y" data point, and a date value for the "X" axis. The graphs I have made in Excel 2010 are easy to make and look good. Now how do I add those limit lines? The value for each line must be a function of some data points in the spreadsheet.

I would have thought that by now Excel would have made this easy!

I'm not sure if I understand this properly, but what I would do is this:

In the source data, add another data series using the formula =IF(source_data [greater than] upper_limit,upper_limit,IF(source_data [less than] lower_limit,lower_limit,source_data)) and use that in the graph. That should create your floor and ceiling for you.

If you want to preserve the source data and just have visible lines, then create two new data series, one for the upper limit, one for the lower. Put them right along side your source data and populate all the entries with the same number, depending on what that limit is.

Sorry about the formatting, it wouldn't let me write the exact formula in here.

posted by JimBJ9 at 9:13 AM on April 14

In the source data, add another data series using the formula =IF(source_data [greater than] upper_limit,upper_limit,IF(source_data [less than] lower_limit,lower_limit,source_data)) and use that in the graph. That should create your floor and ceiling for you.

If you want to preserve the source data and just have visible lines, then create two new data series, one for the upper limit, one for the lower. Put them right along side your source data and populate all the entries with the same number, depending on what that limit is.

Sorry about the formatting, it wouldn't let me write the exact formula in here.

posted by JimBJ9 at 9:13 AM on April 14

Hey, I had to do this recently, the way I ended up doing it wasn't very neat, but I did the same thing Dashy suggests to get a bunch of points in a straight line. Then I fitted a line of best fit to them and hid the points in that series (by double clicking one and pressing delete) to leave just the line.

posted by Ned G at 10:52 AM on April 14

posted by Ned G at 10:52 AM on April 14

I've done this previously in Excel 2010, but I only have Excel 2013 to hand, and the formatting menus are completely different, but formatting the series with no markers and with lines will work fine. Because I can't describe how, I've linked an example file where I've done it.

posted by ambrosen at 11:01 AM on April 14

posted by ambrosen at 11:01 AM on April 14

This can also be done (less neatly i think) using error bars. The always thorough Jon Peltier gives examples of this (and Dashy's) at his blog here.

posted by kev23f at 5:35 AM on April 15

posted by kev23f at 5:35 AM on April 15

I came in to say I'd do it with error bars, I think it's neater as you can just enter the data points in directly into the Chose Data dialog, then chose the length in the Error bars dialog. This way you don't have cells hanging around just to provide the line.

posted by chrispy108 at 8:23 AM on April 15

posted by chrispy108 at 8:23 AM on April 15

You are not logged in, either login or create an account to post comments

posted by Dashy at 9:10 AM on April 14