| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to check if a date falls within a range(2 cells: Start/End Dat | Excel Worksheet Functions | |||
| check for FALSE values in a range of cells in VBA | Excel Discussion (Misc queries) | |||
| Check if a cell contains text compared to a range of cells | Excel Discussion (Misc queries) | |||
| How do I get Excel to display a range of cells with a check box? | Excel Discussion (Misc queries) | |||
| Can a formula check for a certain value in a range? | Excel Discussion (Misc queries) | |||
Linear Mode
