If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Function help requested please
Hello to all
I have worked with functions in other programs, but I am totally new to Excel. Here's my situation - I have a table like the following that extends for 10 rows. A B C 1 0 50 700 2 50 100 1600 3 100 150 2500 4 150 200 3400 etc... F G 20 174 =??? I want a function in cell G20 that takes the value in F20 (in this example, 174), then determines what range that number falls in in columns A & B (in this case, 174 is between A4 and B4), then returns the corresponding value from C4 (in this case, 3400). I should mention as well that the range A4 to B4, for example, means greater than or equal to A4, but strictly less than B4. Much thanks for any help! -- cinnie |
#2
|
|||
|
|||
Function help requested please
You want Vlookup, as in:
=vlookup(f20,a1:c10,3,true) Regards, Fred "cinnie" wrote in message ... Hello to all I have worked with functions in other programs, but I am totally new to Excel. Here's my situation - I have a table like the following that extends for 10 rows. A B C 1 0 50 700 2 50 100 1600 3 100 150 2500 4 150 200 3400 etc... F G 20 174 =??? I want a function in cell G20 that takes the value in F20 (in this example, 174), then determines what range that number falls in in columns A & B (in this case, 174 is between A4 and B4), then returns the corresponding value from C4 (in this case, 3400). I should mention as well that the range A4 to B4, for example, means greater than or equal to A4, but strictly less than B4. Much thanks for any help! -- cinnie |
#3
|
|||
|
|||
Function help requested please
On Thu, 4 Mar 2010 06:06:02 -0800, cinnie
wrote: Hello to all I have worked with functions in other programs, but I am totally new to Excel. Here's my situation - I have a table like the following that extends for 10 rows. A B C 1 0 50 700 2 50 100 1600 3 100 150 2500 4 150 200 3400 etc... F G 20 174 =??? I want a function in cell G20 that takes the value in F20 (in this example, 174), then determines what range that number falls in in columns A & B (in this case, 174 is between A4 and B4), then returns the corresponding value from C4 (in this case, 3400). I should mention as well that the range A4 to B4, for example, means greater than or equal to A4, but strictly less than B4. Much thanks for any help! You can use VLOOKUP. Note that, since your ranges are contiguous, col B is not required, but you do need a test for values greater than your largest defined value -- if not for the "etc", it would be 200. The general formula would be: =VLOOKUP(F20,A1:C4,3) You can use either an IF statement to test for a value 200 (or max); or you could just add a line to your table: 0 700 50 1600 100 2500 150 3400 200 undefined and change the formula to refer to A1:C5 --ron |
#4
|
|||
|
|||
Function help requested please
Hi,
Try this =SUMPRODUCT((A1:A4=F20)*(B1:B4F20)*(C1:C4)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "cinnie" wrote: Hello to all I have worked with functions in other programs, but I am totally new to Excel. Here's my situation - I have a table like the following that extends for 10 rows. A B C 1 0 50 700 2 50 100 1600 3 100 150 2500 4 150 200 3400 etc... F G 20 174 =??? I want a function in cell G20 that takes the value in F20 (in this example, 174), then determines what range that number falls in in columns A & B (in this case, 174 is between A4 and B4), then returns the corresponding value from C4 (in this case, 3400). I should mention as well that the range A4 to B4, for example, means greater than or equal to A4, but strictly less than B4. Much thanks for any help! -- cinnie |
#5
|
|||
|
|||
Function help requested please
try this function in cell G:
=IF(A2=F2B2,0,C2) for G1 you should get 0 and for G20 you should get 3400 -NSNR "cinnie" wrote: Hello to all I have worked with functions in other programs, but I am totally new to Excel. Here's my situation - I have a table like the following that extends for 10 rows. A B C 1 0 50 700 2 50 100 1600 3 100 150 2500 4 150 200 3400 etc... F G 20 174 =??? I want a function in cell G20 that takes the value in F20 (in this example, 174), then determines what range that number falls in in columns A & B (in this case, 174 is between A4 and B4), then returns the corresponding value from C4 (in this case, 3400). I should mention as well that the range A4 to B4, for example, means greater than or equal to A4, but strictly less than B4. Much thanks for any help! -- cinnie |
#6
|
|||
|
|||
Function help requested please
Thanks to Fred, Mike, Ron and NSNR. These different appraoches work GREAT!
-- cinnie "Ron Rosenfeld" wrote: On Thu, 4 Mar 2010 06:06:02 -0800, cinnie wrote: Hello to all I have worked with functions in other programs, but I am totally new to Excel. Here's my situation - I have a table like the following that extends for 10 rows. A B C 1 0 50 700 2 50 100 1600 3 100 150 2500 4 150 200 3400 etc... F G 20 174 =??? I want a function in cell G20 that takes the value in F20 (in this example, 174), then determines what range that number falls in in columns A & B (in this case, 174 is between A4 and B4), then returns the corresponding value from C4 (in this case, 3400). I should mention as well that the range A4 to B4, for example, means greater than or equal to A4, but strictly less than B4. Much thanks for any help! You can use VLOOKUP. Note that, since your ranges are contiguous, col B is not required, but you do need a test for values greater than your largest defined value -- if not for the "etc", it would be 200. The general formula would be: =VLOOKUP(F20,A1:C4,3) You can use either an IF statement to test for a value 200 (or max); or you could just add a line to your table: 0 700 50 1600 100 2500 150 3400 200 undefined and change the formula to refer to A1:C5 --ron . |
#7
|
|||
|
|||
Function help requested please
Glad to help. Thanks for the feedback.
Fred "cinnie" wrote in message ... Thanks to Fred, Mike, Ron and NSNR. These different appraoches work GREAT! -- cinnie "Ron Rosenfeld" wrote: On Thu, 4 Mar 2010 06:06:02 -0800, cinnie wrote: Hello to all I have worked with functions in other programs, but I am totally new to Excel. Here's my situation - I have a table like the following that extends for 10 rows. A B C 1 0 50 700 2 50 100 1600 3 100 150 2500 4 150 200 3400 etc... F G 20 174 =??? I want a function in cell G20 that takes the value in F20 (in this example, 174), then determines what range that number falls in in columns A & B (in this case, 174 is between A4 and B4), then returns the corresponding value from C4 (in this case, 3400). I should mention as well that the range A4 to B4, for example, means greater than or equal to A4, but strictly less than B4. Much thanks for any help! You can use VLOOKUP. Note that, since your ranges are contiguous, col B is not required, but you do need a test for values greater than your largest defined value -- if not for the "etc", it would be 200. The general formula would be: =VLOOKUP(F20,A1:C4,3) You can use either an IF statement to test for a value 200 (or max); or you could just add a line to your table: 0 700 50 1600 100 2500 150 3400 200 undefined and change the formula to refer to A1:C5 --ron . |
Thread Tools | |
Display Modes | |
|
|