Specifying a point on each series in an X-Y scatter graph.

[rmpadilla]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have an X-Y scatter graph with multiple series showing wavelength on the X-axis and absorbance on the Y-axis. What I want to do is specify a value on the x-axis and find its corresponding value on the y-axis. In other words, I want to plot a point on each of the data series and find out what the absorbance is for that wavelength value. This point is not included in each of the data series. And I don't want to plot all of the data points. For example, if the wavelength is 500, I want a point on all the series where the plots cross 500. Also, what is the value of absorbance on each of those points where the wavelength equals 500. I hope I gave enough information. Let me know if there are multiple ways to do this. Thank you.

 

[Carl Witthoft]

You don't need to plot (nor should you plot).

If you are willing to settle for linear fits, look at the help file for
FORECAST -- and just execute FORECAST on the two pairs of x-y values
which bound the x-value you want to calculate absorptance for.
Or you could just calculate mx+b from those two pairs directly (LINEST
or with the fit formulas entered into cells) and apply the results.

If you want this to be more automated, you'll have to read up on
VLOOKUP and MATCH and stuff. I have to say you'll be a lot better off
in the long run using some other tool (like MatLab or R or SciLab) to do
this. Excel is very clunky when it comes to doing math.

Carl