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
Bookmarks