Unable to get a single point to identify whether its in spec or otherwise.

[bralew]

Hi,

I have a graph with scatter X,Y arrangements and I have designed it that I am able to type in X,Y and I can quickly determine whether a reading is in specification or not. This works fine, but Id like to to put a Pass/Fail reading in the chart.
My problem is that this number is dynamic and not static i.e. everything above 250mm is failed. I am not really sure how to go about this, but I imagine i would have to get excel to identify whether it is above or below the threshold line.
Ill include a snippet of the graph to facilitate any assistance. beam bending.PNG

[Kaper]

Of course if the case is that simple as described (everything below 250 is a failure) just compare with 250 :-)
But probably you want to know whether your red dot is for instance above or below for instance green line.
Then you basically cannot do this directly on the chart.
You shall check it directly in cells (some simple math - may be some interpolation of your lines would be needed).

[bralew]

Thanks,

Yes, i thought this may be the case. Due to the non linear nature of the data its difficult to write in any tolerance. The 250 rule wont work due to the fact that it can move depending on composition.
The data in the cells is not detailed enough as they work on coordinates which occur every intervalic division(300, 320,340 etc). Therefore, the data in between these is assumed due to the nature of the graph and what were trying to measure.
Any ides how to progress?

[Kaper]

If I'm right, you used scatterplot with smooth lines.
Here: https://newtonexcelbach.wordpress.co...cubic-splines/ you can find a function which mimics excel smooth lines. you can use it to calculate Y value for the smooth line at given X. Then you just have to compare your red dot Y with calculated Y to see if red dot is below or above the line.
But as you claim that data for lines is collected every 20 X axis units then probably linear interpolation of each segment. In this case math will be much easier than in link cited above.

If your dot is at some X0 point you look for Xi (line point which is first smaller than X0) and Xj (first larger than Xi, or in other words point i+1)
then Yc (calculated) will be given by:
Yc=Yi+(Yj-Yi)*(X0-Xi)/(Xj-Xi)
now you can compare Yc with Y0 and you are done.

To find i you can use excel MATCH function (with third parameter set to 1).