Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default 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.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default 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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,104
Default 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.



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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.





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,722
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to check if a date falls within a range(2 cells: Start/End Dat MarcusMac Excel Worksheet Functions 4 July 30th 08 04:21 PM
check for FALSE values in a range of cells in VBA Dave F[_2_] Excel Discussion (Misc queries) 2 August 7th 07 04:12 PM
Check if a cell contains text compared to a range of cells GD1226, Captain Ahab, manface Excel Discussion (Misc queries) 5 April 13th 07 01:00 AM
How do I get Excel to display a range of cells with a check box? bobm Excel Discussion (Misc queries) 2 October 21st 05 07:37 PM
Can a formula check for a certain value in a range? Lee IT Excel Discussion (Misc queries) 3 April 8th 05 07:36 AM


All times are GMT +1. The time now is 09:29 AM.

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

About Us

"It's about Microsoft Excel"