Intercept of a line and curve in Excel chart with a log scale on the x-axis

Hi everyone,

I have got a set of data points (I don't have an equation unfortunately) from an experiment which I have plotted on a scatter graph. The x-axis has a log scale to the base of 2. I need to find the median for the data points plotted (the curve), therefore the intercept between the curve and line (drawn at y=50 to find the median) needs to be calculated to give a value for the x co-ordinate (which is on a log scale to the base of 2). Is there any way that the x value for the intercept can be found either graphically or using formulae in excel, as I need to repeat this process to find the intercept for 40 different graphs?

The Excel file is attached to hopefully demonstrate the problem a bit clearer. I hope I have explained everything clearly. If not, please let me know and I will have another go!

Thanks very much for any help in advance.

Intercept Problem.xlsx

If I understand correctly, given the "sigmoidal" curve described by the data, you need to find the x value that would correspond to 50%, is that correct?

2 basic approaches that I see used for this:

1) a) Regress parameters for a suitable equation (logistics or other sigmoidal curve in this case). Since these kind of equations are rarely linear, you would probably need a non-linear regression using Solver.
b) From the regressed equation, solve for x at your specified y. If this cannot be done algebraically, numerical methods (Solver again) may be required to solve the equation.

2) linear interpolation
a) "regress" m and b for the linear equation connecting each pair of points (I would probably use log(x,2) instead of x in these regressions)
b) use a lookup function to return the desired m and b for the interval that contains the unkown point (y=0.5 in this case).
c) Solve that equation for x.
This example file shows a similar interpolation problem that I proposed to someone else. It should give you an idea of how to set this up in your spreadsheet. http://www.excelforum.com/excel-char...ml#post3904113

If you have further questions, let us know.

For piecewise linear interpolation,

Row\Col	D	E	F
8	0.868636	50	D8: =PERCENTILE($A$5:$A$13, PERCENTRANK($B$5:$B$13, 50, 6))
9			E8: Input

Please take a few minutes to read the forum rules about cross-posting.

The two methods I suggested are generic enough to work with whatever "transformation" you apply to the x data.

Note that shg's PERCENTILE() approach will also work, though, here again, I would probably use =log(A5,2) (in a helper column) as the "x values" in the interpolation problem (yields 0.833 instead of 0.869).

Mr Shorty is probably correct.

Originally Posted by shg

Mr Shorty is probably correct.

Maybe, maybe not. My rationale is because the OP seems to like having the x axis on a logarithmic scale, so treating x as logarithms more closely approximates the visual/chart. The difference in the result is 4 out of 85 or about 5%. Only the OP would really know if that 5% is significant or not. Considering the empirical nature of an interpolation approach, I don't know if it is.