Handling blank cells in a graph

[johnnymac]

I have a large table of data in which many cells are blank. A typical lookup table is shown below:

x = 8.96 11.98 30.52
y = 1 2 3 4 5 6 7 8

Each cell in the x row is a lookup: e.g =IFNA(INDEX($H$366:$AU$366,MATCH(BB$7,$H8:$AU8,0)),"")

My problem: If I use the IFNA, then Excel will calculate slopes and intercepts of the line and find the inflection point I am looking for. If I leave the data as #N/A, all the parameters become #N/A as there is no data that has a value for each point. However, Excel will only graph the data if I do not include the IFNA. When using the IFNA and generating blank cells, the Scatter plot in Excel will show the x-axis as the y-axis. [pulls out my hair]

Is there something I can use other than "" to create a blank space that the chart will ignore as it does with #N/A?

Thanks for any insights. I wanted to show more graph images here but could not figure out how to paste an image in this forum.

John

[MrShorty]

#N/A is the built in way in Excel to handle gaps in Excel. You might review this discussion and see if he has other insights, but I'm not aware of any alternatives to #N/A in charting gaps (other than making those cells truly blank with the Clear/Delete command): http://peltiertech.com/mind-the-gap-...g-empty-cells/

You don't say how you are calculating slope and intercept of your regression line. If you have not tried it, yet, then you might try using the =SLOPE() and =INTERCEPT() worksheet functions: list of statistical functions https://support.office.com/en-us/art...__toc309306716 According to the help files, these two functions will ignore text values within their arguments, unlike the chart trendline feature (that treats text as 0) or the =LINEST() function that generates an error for any non-numeric data in its arguments.

[johnnymac]

Thanks for the quick response. I do use the SLOPE() and INTERCEPT() functions after conversion to log form for the sigmoidal curve fit. Those function fail (i.e. return #N/A) when there are empty cells, which there always are. I see my original post left the blank spaces out of the data . The top row (x) has no observations at 1,3,4,7, and 8. I had found that exact article in a search, but it only seems to apply to gaps in the y-data. My gaps are in the x-axis and Excel seems to want to treat them differently.

I could set up a second page - one with #N/A (for graphing) and the original with blanks (for calculating), but that doesn't seem very elegant

[johnnymac]

not a great solution, but this works:

since I am only graphing a selected value - not the entire matrix - I used an IF to copy the selected position to a small table by the graph.

=IF(VLOOKUP($BS$14,'gt15 data'!$AV$8:$BO$1089,7,1)="",#N/A,VLOOKUP($BS$14,'gt15 data'!$AV$8:$BO$1089,7,1)) the column 7 shown here being incremented for all the columns to be shown. $BS$14 generates the plate ID from the selected experimental parameters.

This inserts #N/A values and allows the XY scatter plot to show the data, the user selects. I wanted this graph, so someone could get a view of how the curve fit was working. Since there are over 1000 experiments in all, I couldn't show all of them anyway.

John

[johnnymac]

By the way,

the SLOPE() and INTERCEPT() functions may ignore text values, but they do not ignore #DIV/0! cells. That was the whole reason behind my post - that Excel charts and functions treat these cells differently.

[MrShorty]

I have thought the same thing before. When I trap errors in a spreadsheet, it would be nice if I could use the same error trapping strategy for worksheet functions (where it is useful to return empty string since they can ignore text values) and charts (where it is useful to return N/A, since charts mostly ignore N/A errors). Unfortunately, we have to work with the Excel application as it is (unless another spreadsheet application is better at this, in which case we could defect to another spreadsheet application). As Jon Peltier mentions in the Mind the Gap article I linked to earlier, Excel MVP's have been asking for a "blank" function that would simulate blank cells for a long time, and MS has not felt a need to add that to Excel. Until MS makes a change to the application, we have to learn how to trap errors in a way that works for the end goal of the function (whether the output is intended for a chart or for a worksheet function).