How to "trace" (predict) the X value when given the Y value

**redsteven** · 03-07-2009, 10:40 AM

I have a scatter chart based on two columns of data (X data in left column, Y data in right column):

Final Concentration Absorbance
0.00E+00 0
1.06E-05 0.147
2.12E-05 0.306
3.19E-05 0.464
4.25E-05 0.626
5.31E-05 0.752

I want to predict the Concentration (X-value) at an Absorbance (Y-value) of .582

How would I go about doing this using Excel? I've read about the VLOOKUP function, but I don't think that's what I'm looking for here.

Thanks,
Steven

**Andy Pope** · 03-07-2009, 10:50 AM

Maybe look at the TREND function.

Are you sure about your values? The know X value is way out of range of what you said are your X values. Looks more like the Y value range.

**redsteven** · 03-07-2009, 10:55 AM

Originally Posted by Andy Pope

Maybe look at the TREND function.

Are you sure about your values? The know X value is way out of range of what you said are your X values. Looks more like the Y value range.

Whoops, you're right – I fixed that.

I'm gonna take another look at the trend function. i didn't think it could be used to find a specific value but perhaps it can.

EDIT: It looks like the trend formula will give you Y-values for a specific X-value... so in this case it just meant switching what was designated as X and Y when plugging the cell references into the formula.

**Andy Pope** · 03-07-2009, 11:03 AM

For me it returns a Y value of 0.000040391

You can also look at LINEST . This will return the components for a 3rd order polynomial trend line.

=LINEST(A2:A7,B2:B7^{1,2,3},TRUE,TRUE)