Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlook up (average)
i have the following data:
A B 1 10 140 2 20 95 3 30 70 4 40 60 5 50 55 in C1 i enter 20, in D1 i have the formulae as follows: = vlookup(C1,A:B,2,FALSE) and the return result is 95. can the formula be changed so that it can look up a number (average) that is not in the A column. example vlookup 35 returns a value of 65, or 31 returns a value of 69? thank you, jat |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlook up (average)
On Mon, 23 Jun 2008 13:52:02 -0700, jatman
wrote: i have the following data: A B 1 10 140 2 20 95 3 30 70 4 40 60 5 50 55 in C1 i enter 20, in D1 i have the formulae as follows: = vlookup(C1,A:B,2,FALSE) and the return result is 95. can the formula be changed so that it can look up a number (average) that is not in the A column. example vlookup 35 returns a value of 65, or 31 returns a value of 69? thank you, jat Try this formula in D1: =IF(C1=A5,B5,INDEX(B1:B5,MATCH(C1,A1:A5,1))+ (INDEX(B1:B5,MATCH(C1,A1:A5,1)+1)-INDEX(B1:B5,MATCH(C1,A1:A5,1)))* (C1-INDEX(A1:A5,MATCH(C1,A1:A5,1)))/(INDEX(A1:A5,MATCH(C1,A1:A5,1)+1)- INDEX(A1:A5,MATCH(C1,A1:A5;1)))) This will give you the linearly interpolated B values for C1 values inbetween the values in column A. Is that what you wanted? Hope this helps. / Lars-Åke |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlook up (average)
If the value in Column A are always indexing by 10 then will this formula
return the proportional response that you are looking for? =IF(FLOOR(C1,10)=C1,VLOOKUP(C1,A2:B6,2,TRUE),VLOOK UP(C1,A2:B6,2,TRUE)-(C1-FLOOR(C1,10))/(CEILING(C1,10)-FLOOR(C1,10))*((INDEX(B2:B6,MATCH(FLOOR(C1,10),A2: A6,FALSE)))-(INDEX(B2:B6,MATCH(CEILING(C1,10),A2:A6,FALSE))))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "jatman" wrote in message ... i have the following data: A B 1 10 140 2 20 95 3 30 70 4 40 60 5 50 55 in C1 i enter 20, in D1 i have the formulae as follows: = vlookup(C1,A:B,2,FALSE) and the return result is 95. can the formula be changed so that it can look up a number (average) that is not in the A column. example vlookup 35 returns a value of 65, or 31 returns a value of 69? thank you, jat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlook up | Excel Discussion (Misc queries) | |||
VLOOK UP | Excel Worksheet Functions | |||
VLOOK-pivot table expanding want to update vlook automatically | Excel Worksheet Functions | |||
VLOOK-pivot table expanding want to update vlook automatically | Excel Worksheet Functions | |||
vlook up | Excel Discussion (Misc queries) |