Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing a conditional formula to Search with number ranges
I'm trying to write a conditional formula that will indicate if the data in a
indiviual cell in column A falls within the range of the data in Columns B and C. See example data below. There are roughly twenty thousand lines that I need to search if a particular number falls within the range. Is there a formula that will capture range of data??? A B C 491987312 491987100 491987199 491987200 491987299 491987300 491987399 491987400 491987499 I've also listed the columns of range numbers on the same sheet as my data. Could this be on a different sheet? Could the program be written using data from seperate sheets? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing a conditional formula to Search with number ranges
If you want
A B C D 491987312 491987100 491987199 False Then it's a simple formula: =IF(AND((A1=B1),(A1<=C1)), TRUE, FALSE) If you want D1 to tell what row it falls into (e.g. D1 would equal "3") then you'd be better off writing a custom function like the one below. With this function you would enter into cell D1 the following formula: =WhereAmI(A1, C1:C4) And it would return the value 3. Function WhereAmI(FindMe As Range, InHere As Range) As Integer Dim myrow As Integer 'Assuming the FindMe value will 'fall into the first row. myrow = 1 For Each cell In InHere If FindMe.Value cell.Value Then 'Our search value is greater 'than the max for this row. myrow = myrow + 1 End If Next cell WHEREAMI = myrow End Function Hope this helps. Bob Tulk MOUS(XP/97) "AngieD" wrote: I'm trying to write a conditional formula that will indicate if the data in a indiviual cell in column A falls within the range of the data in Columns B and C. See example data below. There are roughly twenty thousand lines that I need to search if a particular number falls within the range. Is there a formula that will capture range of data??? A B C 491987312 491987100 491987199 491987200 491987299 491987300 491987399 491987400 491987499 I've also listed the columns of range numbers on the same sheet as my data. Could this be on a different sheet? Could the program be written using data from seperate sheets? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Writing Formula For conditional Formatting Using Excel 2000 | Excel Discussion (Misc queries) | |||
writing a formula to distinguish a letter and a number | Excel Worksheet Functions | |||
Writing Search Macros | New Users to Excel | |||
Conditional Formula to search ranges?? | Excel Discussion (Misc queries) | |||
Need help writing a conditional formula | Excel Programming |