Graph look up

[Wendy]

I have created a graph in excel with some standard values plotting A against
B. Now I am doing experiments and have readings for A, which I want to type
into a spreasheet and have Excel look up the corresponding value of B. Is
this possible? If so, how please?

 

[Tomos]

As long as the data is somewhere in table form, you can use H
(horizontal) lookup or V (vertical) lookup formulas.

Your formula should look like this:
=VLOOKUP("A",B1:C2,5,FALSE)

'A' refers to the value you are looking for to 'read across' from
either vertically or horizontally respectively.

'B1:C2' refers to the area in which this value is to be found

'5' Refers to the number of colums along the value will be read from.

False means an exact match to 'A' must be found, otherwise the closest
match will be used.

Experiment with these lookups, because they solve most of my lookup
problems when used correctly.

Hope this helps. Google for some examples so you get the idea

 

[Wendy]

Many thanks, I will try this out.

 

[Tushar Mehta]

I have created a graph in excel with some standard values plotting A against
B. Now I am doing experiments and have readings for A, which I want to type
into a spreasheet and have Excel look up the corresponding value of B. Is
this possible? If so, how please?

All of the suggestions below rely on piecewise linear interpolation.
As long as that is acceptable...

If you want to read off values from the graph, see
Interactive Chart
http://www.tushar-
mehta.com/excel/software/interactive_chart_display/index.html

If you want to work off the data in the XL chart, you can do either of
the following. Suppose you have the following named ranges (Insert |
Name > Define...):
The *sorted* ascending x- and y- data are XVals and YVals respectively,
the cell containing the target value is TargetVal, and
an interim cell containing a match value (formula below) is named
MatchIdx.

Then, the MatchIdx cell should have the formula
=MATCH(targetVal,XVals,1)

and the result cell should contain the formula
=(INDEX(YVals,MatchIdx+1)-INDEX(YVals,MatchIdx))/(INDEX(XVals,MatchIdx+
1)-INDEX(XVals,MatchIdx))*(targetVal-INDEX(XVals,MatchIdx))+INDEX
(YVals,MatchIdx)

Note that this works for any target value within the range of X values.
For values outside the range (< min. value or > max. value) the result
will be an error.

If you use an UDF (user defined function) it would be easier
incorporating boundary conditions at the downside of all the cons
associated with a VBA macro. The basic code that would go into a
standard module is below. Note that it has no safety checks.

Option Explicit
Option Compare Text
Function linearInterp(xvals, yvals, targetVal)
Dim matchVal
On Error GoTo ErrXit
With Application.WorksheetFunction
matchVal = .Match(targetVal, xvals, 1)
If matchVal = xvals.Cells.Count _
And targetVal = .Index(xvals, matchVal) Then
linearInterp = .Index(yvals, matchVal)
Else
linearInterp = .Index(yvals, matchVal) _
+ (.Index(yvals, matchVal + 1) - .Index(yvals, matchVal)) _
/ (.Index(xvals, matchVal + 1) _
- .Index(xvals, matchVal)) _
* (targetVal - .Index(xvals, matchVal))
End If
End With
Exit Function
ErrXit:
With Err
linearInterp = .Description & "(Number= " & .Number & ")"
End With
End Function

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions