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 |
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 |
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 |
All times are GMT +1. The time now is 06:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com