A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Function help requested please



 
 
Thread Tools Display Modes
  #1  
Old March 4th, 2010, 03:06 PM posted to microsoft.public.excel.worksheet.functions
cinnie
external usenet poster
 
Posts: 67
Default 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  
Old March 4th, 2010, 03:15 PM posted to microsoft.public.excel.worksheet.functions
Fred Smith[_4_]
external usenet poster
 
Posts: 2,386
Default 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  
Old March 4th, 2010, 03:42 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default 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  
Old March 4th, 2010, 03:53 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 8,419
Default 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  
Old March 4th, 2010, 05:14 PM posted to microsoft.public.excel.worksheet.functions
NSNR[_2_]
external usenet poster
 
Posts: 5
Default 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  
Old March 4th, 2010, 05:22 PM posted to microsoft.public.excel.worksheet.functions
cinnie
external usenet poster
 
Posts: 67
Default 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  
Old March 4th, 2010, 07:55 PM posted to microsoft.public.excel.worksheet.functions
Fred Smith[_4_]
external usenet poster
 
Posts: 2,386
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:41 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.