Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Graph look up
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? -- Wendy |
#2
|
|||
|
|||
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 -- Tomos ------------------------------------------------------------------------ Tomos's Profile: http://www.excelforum.com/member.php...o&userid=26581 View this thread: http://www.excelforum.com/showthread...hreadid=398500 |
#3
|
|||
|
|||
Many thanks, I will try this out.
-- Wendy "Tomos" wrote: 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 -- Tomos ------------------------------------------------------------------------ Tomos's Profile: http://www.excelforum.com/member.php...o&userid=26581 View this thread: http://www.excelforum.com/showthread...hreadid=398500 |
#4
|
|||
|
|||
In article ,
says... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a combination graph with stackedColumns+lineGraph | Charts and Charting in Excel | |||
Graph Help- 12 month graph, but only want months that have passed | Charts and Charting in Excel | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
2 axis bar graph | Excel Discussion (Misc queries) | |||
Graph Axes | Excel Discussion (Misc queries) |