Interpolated Data-Connecting Data Points

One way I can think of is to use the Reorganization tools to filter your table so only rows with data are displayed. The default for charts is to not show hidden data so all those empty points will be ignored and the other points can be connected with a line.

Depending on your needs, you might want to create an auxiliary table that is a a direct copy of your data table. Each cell would have a formula such as cell A1=Table 1::A1. Then you could filter and chart this table instead of your data table.

Whether you use the existing data table or an auxiliary table, to do the filter:

1) Plot your data while all rows are displayed
2) Click on Reorganization on the toolbar.
3) In the middle section choose a filter for "show only rows where column __ is not blank".

Your chart should be able to connect the data points. If not, go to the Chart Inspector, click the chart button and deselect "show hidden data".

The first method I posted works well if you don't need the "blank" categories/rows taking up space on your chart. But if you need to see the "blank" categories/rows, I have a table that I think will do a straight-line interpolation for you. I'm not sure it is error free but, if not, it'll be obvious when you chart it. I'm also not sure it was the simplest way to do it but a lot of the complexity was a result of my making it easy to add new rows to the bottom. I tend to brute force things so if someone has a simpler solution, I hope they post it.

Copy the table to your spreadsheet. Copy your data to the X and Y columns and your category labels to Column A. If you only have Y data then the X's must be a numeric series (like what's in the table now). Plot your X-Y (or Y-only) data as one series and the interpolated column as another series. Change the interpolated data points to "none" and change the line color if you want to (which I did not do below).

http://files.me.com/pwb3/zk3o46.numbers.zip

I got an easier way to do it. Ideas bounce around in my head for a while then another thought will pop out.

Put your data into separate tables. I'd create four new 2-column tables (no header column) that pull the data off of your original table. All the X data will be the same (Column A) and each table will have one set of Y data (column B). Chart one table. With the chart selected, hold down Command and select another table's X-Y data. Repeat for the other two tables. Filter reach of these tables to show only rows where column B is not blank. Then, back on the chart, choose to connect the lines for each series.

If you had just one data set, you could do nearly the same thing. The second table, which would not require being filtered, would be a column of all X values and the Y values could be anything (zero, for instance). For this "fake" data series you would select "none" for the data point and the series would be hidden on the chart.

I got interrupted in the middle of an edit and didn't get it done in time. The method in my last post works with scatter charts, not other line charts. It sounded like you were doing a scatter chart.

Unfortunately, I have found a bug in my interpolation algorithm. It bugs out if two Y's with the same value are next to each other, whether in two consecutive rows or if separated by blanks. I'll work on it some more.

To answer your question on how to use it for multiple data sets (once I fix it), I would make a copy of all columns from X Data through X2 for each data set then put the correct Y data in.

By the way, where is the interpolate setting in Excel? I looked but didn't find it. Excel 2008.

Here is the fixed version. It is for non-scatter charts. I fixed the formulas and also ensured you could drag down on the tab on the lower left corner of the table to add rows and all formulas will copy down properly. You cannot add or delete rows in the middle. The only columns that should be messed with are the labels in column A and the Y data in Column C. Everything else is formulas (including the X data).

http://files.me.com/pwb3/jrw6qe.numbers.zip

I have to admit this still seems like a hard way to do it, certainly harder than clicking on a button in Excel to have it do the interpolation for you. If there is an easier way I hope someone posts it.

Thanks for showing me where that setting was in Excel. As usual, Microsoft hides things in strange places. I was expecting it to find it in the floating toolbar with the other chart settings or in one of the various chart-related right-click menus. Nonetheless, it is definitely a lot easier than the two ways I've found to do it in Numbers.

By the way, if you want to see an inconsistency in Numbers, plot a 3-D line chart. Empty cells are plotted as zeros and all points get connected.

Message was edited by: Badunit