How do I write formula to check a range of cells?
 

Please I have the following:
COLUMN A COLUMN B
COLUMN C
ROW1 500 1000
5:00
ROW2 1001 1500
4:50
ROW3 1501 2000
4:25
ROW4 2001 2500
4:00

How can I write a formula for excel to check the value of a number in column
F check the row that the number will be fit in in the above table and use the
value in column C of the above table to multiply the number. For example, if
the value in column F is 1325, this number will fall in row 2, then I want my
formula to now use 1325*4:50 (i.e. the value of C2 to multiply the number).

Another example, if I have 2431 in column F, then the formula should be
2431*4:00.

How can I have a general formula that will cater for this?

All help will be appreciated please.
Thank you.

How do I write formula to check a range of cells?
 

You can use MATCH to find the row and then use INDEX with the match number

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tayo" wrote in message
...
Please I have the following:
COLUMN A COLUMN B
COLUMN C
ROW1 500 1000
5:00
ROW2 1001 1500
4:50
ROW3 1501 2000
4:25
ROW4 2001 2500
4:00

How can I write a formula for excel to check the value of a number in
column
F check the row that the number will be fit in in the above table and use
the
value in column C of the above table to multiply the number. For example,
if
the value in column F is 1325, this number will fall in row 2, then I want
my
formula to now use 1325*4:50 (i.e. the value of C2 to multiply the
number).

Another example, if I have 2431 in column F, then the formula should be
2431*4:00.

How can I have a general formula that will cater for this?

All help will be appreciated please.
Thank you.

How do I write formula to check a range of cells?
 

Thank you Luke.

that was a quick response and it answered my question. Thank you very much.

"Luke M" wrote:

Something like:

=F2*LOOKUP(F2,A$2:A$10,C$2:C$10)

Where A2:A10 contains your lower boundaries of each section. Note that this
formula does not handle errors, such as what to do it F2 is below lowest
limit, or greater than highest limit. You will need to add an IF function if
that is a possible issue.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Tayo" wrote:

Please I have the following:
COLUMN A COLUMN B
COLUMN C
ROW1 500 1000
5:00
ROW2 1001 1500
4:50
ROW3 1501 2000
4:25
ROW4 2001 2500
4:00

How can I write a formula for excel to check the value of a number in column
F check the row that the number will be fit in in the above table and use the
value in column C of the above table to multiply the number. For example, if
the value in column F is 1325, this number will fall in row 2, then I want my
formula to now use 1325*4:50 (i.e. the value of C2 to multiply the number).

Another example, if I have 2431 in column F, then the formula should be
2431*4:00.

How can I have a general formula that will cater for this?

All help will be appreciated please.
Thank you.

How do I write formula to check a range of cells?
 

either
=F1*INDEX(C1:C4,MATCH(F1,A1:A4,2))

or
=F1*INDEX(C1:C4,MATCH(F1,A1:A4,2))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Tayo" wrote in message
...
Please I have the following:
COLUMN A COLUMN B
COLUMN C
ROW1 500 1000
5:00
ROW2 1001 1500
4:50
ROW3 1501 2000
4:25
ROW4 2001 2500
4:00

How can I write a formula for excel to check the value of a number in
column
F check the row that the number will be fit in in the above table and use
the
value in column C of the above table to multiply the number. For example,
if
the value in column F is 1325, this number will fall in row 2, then I want
my
formula to now use 1325*4:50 (i.e. the value of C2 to multiply the
number).

Another example, if I have 2431 in column F, then the formula should be
2431*4:00.

How can I have a general formula that will cater for this?

All help will be appreciated please.
Thank you.

How do I write formula to check a range of cells?
 

What result do you expect from:

1325*4:50

You're multiplying a *time value* by an integer which evaluates as:

1325*0.201388888888889 = 266.840277777778

Is that the result you expect?

Try this:

=F2*VLOOKUP(F2,A2:C5,3)

--
Biff
Microsoft Excel MVP


"Tayo" wrote in message
...
Please I have the following:
COLUMN A COLUMN B
COLUMN C
ROW1 500 1000
5:00
ROW2 1001 1500
4:50
ROW3 1501 2000
4:25
ROW4 2001 2500
4:00

How can I write a formula for excel to check the value of a number in
column
F check the row that the number will be fit in in the above table and use
the
value in column C of the above table to multiply the number. For example,
if
the value in column F is 1325, this number will fall in row 2, then I want
my
formula to now use 1325*4:50 (i.e. the value of C2 to multiply the
number).

Another example, if I have 2431 in column F, then the formula should be
2431*4:00.

How can I have a general formula that will cater for this?

All help will be appreciated please.
Thank you.

How do I write formula to check a range of cells?
 

Something like:

=F2*LOOKUP(F2,A$2:A$10,C$2:C$10)

Where A2:A10 contains your lower boundaries of each section. Note that this
formula does not handle errors, such as what to do it F2 is below lowest
limit, or greater than highest limit. You will need to add an IF function if
that is a possible issue.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Tayo" wrote:

Please I have the following:
COLUMN A COLUMN B
COLUMN C
ROW1 500 1000
5:00
ROW2 1001 1500
4:50
ROW3 1501 2000
4:25
ROW4 2001 2500
4:00

How can I write a formula for excel to check the value of a number in column
F check the row that the number will be fit in in the above table and use the
value in column C of the above table to multiply the number. For example, if
the value in column F is 1325, this number will fall in row 2, then I want my
formula to now use 1325*4:50 (i.e. the value of C2 to multiply the number).

Another example, if I have 2431 in column F, then the formula should be
2431*4:00.

How can I have a general formula that will cater for this?

All help will be appreciated please.
Thank you.