Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Between/ range function
How could I write a function that would return a number in one cell, if a
number in another cell falls between a certain range? (if A1 is between C1:D1 then E1, else "0") (if .23 is between .039:.002 then 15, else "0") |
#2
|
|||
|
|||
=IF(AND(A1=C1,A1<=D1),E1,0)
and =IF(AND(A1=.002,A1<=.039),E1,0) -- HTH RP (remove nothere from the email address if mailing direct) "sanpanico" wrote in message ... How could I write a function that would return a number in one cell, if a number in another cell falls between a certain range? (if A1 is between C1:D1 then E1, else "0") (if .23 is between .039:.002 then 15, else "0") |
#3
|
|||
|
|||
=if(and(a1<d1,a1c1),e1,"0")
"sanpanico" wrote: How could I write a function that would return a number in one cell, if a number in another cell falls between a certain range? (if A1 is between C1:D1 then E1, else "0") (if .23 is between .039:.002 then 15, else "0") |
#4
|
|||
|
|||
Use the "if" function. If your value in cell B1 is equal to or between the
value in C1 and the value in D1, you want the value in E1 placed in A1. If not, you want a text message that indicates your value to be above or below the range, such as "value BELOW range" or "value ABOVE range" to appear in A1. If that is want you want, the formula in cell A1 should read as follows: =if(B1=C1,if(B1<=D1,E1,"value BELOW range"),"VALUE above RANGE") In real talk is says 'if B1 is greater than or equal to C1, and if B1 is less than or equal to D1, then enter the value in E1 in cell A1. If not enter the appropriate message as to it being either above or below the range. Note that the text string must be in quotes. You can use the same message or different messages in the two text strings, or if you want the cell (A1) to be blank, simply put the quote marks with no space, like "", in both strings. This formula uses two "if" functions. You can use up to seven "if" functions in one formula, and use multiple cells to link more than seven. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
Function to determine if any cell in a range is contained in a given cell | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions |