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

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

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

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

Function help requested please
 

try this function in cell G:
=IF(A2<=F2<B2,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

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
.

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
.