Hi Khalil,
Here's a User Defined Function (UDF) that does what you require.
I added instructions in case you don't know (yet) how to implement a UDF
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
================================================
Pasting a User Defined Function (UDF)
Niek Otten, March 31, 2006
If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these
steps:
Select all the text of the function.
CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).
From the menu bar, choose Insert>Module. There should now be a blank module sheet in front of you. Click in it and then
press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
Press ALT+F11 again to return to your Excel worksheet.
You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
================================================
' =========================================================================
Function TableInterpol(ToFind As Double, Table As Range, ResultColumnNr As Long, _
Optional SortDir, Optional KeyColumnNr)
' Niek Otten, March 22 2006
' Works like Vlookup, but interpolates and has some extra options
' 1st argument: Key to look for. Numbers only!
' 2nd argument: Range to look in and get the result from. Numbers only!
' 3rd argument: Relative column number in the range to extract the result from
' Optional 4th argument: defaults to: "Ascending"; any supplied argument forces Descending
' Optional 5th argument: Relative column number in the range to search the key in,
' defaults to 1
Dim RowNrLow As Long
Dim RowNrHigh As Long
Dim ResultLow As Double
Dim ResultHigh As Double
Dim KeyFoundLow As Double
Dim KeyFoundHigh As Double
If IsMissing(SortDir) Then
SortDir = 1
Else
SortDir = -1
End If
If IsMissing(KeyColumnNr) Then
KeyColumnNr = 1
End If
RowNrLow = Application.WorksheetFunction.Match(ToFind, Intersect(Table, Table.Cells(KeyColumnNr). _
EntireColumn), SortDir)
ResultLow = Table(RowNrLow, ResultColumnNr)
If ToFind = ResultLow Then
TableInterpol = Table(RowNrLow, ResultColumnNr)
Exit Function ' avoid unnesssary second MATCH() call if already exact match found
End If
RowNrHigh = RowNrLow + 1
ResultHigh = Table(RowNrHigh, ResultColumnNr)
KeyFoundLow = Table(RowNrLow, KeyColumnNr)
KeyFoundHigh = Table(RowNrHigh, KeyColumnNr)
TableInterpol = ResultLow + (ToFind - KeyFoundLow) / (KeyFoundHigh - KeyFoundLow) _
* (ResultHigh - ResultLow)
End Function
' =========================================================================
| Hi,
| In sheet2 cell F14 I have the following formula:
| =LOOKUP($C$1,SHEET1$A$14:$A$58,SHEET1I14:I58)
|
| Instead of finding the correspondant value in colomn I, Is it possible to
| display the AVERAGE of the 2 cells I+J ?
| What changes should I make to the previous formula?
|
| Khalil
|
|
