Can I detect the exact point at which a graph crosses a set threshold value?

[creationracing]

Afternoon all, from an oil platform roughly halfway between UK and Norway.

Just a quick question, that I've been Googling all day and can find no meaningful solution for...

I have a chart plotting the voltage of a battery while it is being discharged. This is a non-linear graph with no defined function, so my chart could be called a linear interpolation, as opposed to a proper curve. The specification of these batteries dictates they should be above a certain voltage after a certain period on discharge.

So, my X axis markers are time (minutes) and my Y markers are voltage. And there are two series. One is a straight line, the end of discharge voltage threshold obtained from the battery datasheet, and the other is the plot of the discharge voltage readings, taken at periodic intervals.

Now, it would be great to have a way to know for sure (as opposed to eyeballing the chart) at what point the plot of the discharge readings crosses the threshold line. That is to say, I'd like the cell to report that the battery has lasted for a duration of ??? minutes before it crossed the threshold.

I've played with INTERCEPT but it only deals with Y axis crossing and can't be tailored to look at a threshold value, and as my graph can have a rapid rate-of-change, it's best fit line would possibly be inaccurate anyway. It would appear that there's no way to get the chart to report this value, but I have a gut feeling that there's probably a formula that can do it at data range level anyway.

Anyway, workbook attached for your reference with some test data loaded.

Thanks very, very much in advance!

Roy K.

[CK76]

Since Excel charts are not smooth curve but series of connected data points. You can determine data point before and after threshold and calculate slope to determine intercept.

In your case 2 points before and after are...
y = 23.00 & 19.37
x = 165 & 195
Slope = SLOPE(known_y's, known_x's)
Therefore slope = -0.12

So (20-23)/-0.12 = 25
165+25 = 190

Threshold intercept at x = 190 min

Edit: Another method is to find best fit trend line and solve for x.

[creationracing]

Just had a little bit of fun setting formulae to pick the X and Y axis values from the ranges, but it's all working beautifully now with your solution.

Thanks very much, you're a superstar!