If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
INDIRECT reference or ?
Excel 2007 all MS updates
I need to search ANY column of numbers, say Column AL, to determine if any of the values are below or above a value that I'm wanting to find. Here's an example: in Column AL the values are 1.20, 1.50, 1.75, 1.10, 1.95, 1.55. I'd like to find all cells that have a value of less than 1.50 (not less than or equal in this example). Hence, for Column AM, I'd normally use something like =IF(AL11.50,"True","False") for each of the cells of AM1, AM2, AM3, AM4, AM5, AM6 etc... I'd get a "True" for 1.20, 1.10 and a "False" for 1.50, 1.75, 1.95, 1.55. I'd like to GENERALIZE this by setting up a cell for the OPERATION (less than, less than or equal to, equal to, greater than, greater than or equal to) and another cell for the VALUE I'm looking for (the 1.50 in the above example and a cell for the COLUMN I'm wanting to compare. I'm struggling with the structure of the formula. Here's what I currently have: =IF(H3675"",IF(AL3675=$A$3,"LE",""),""). [I change ALL 4000+ entries EACH time I want to do a comparison!) H3675 here is a cell that contains a date, AL3675 is cell #3675 in column AL that contains a value (like the 1.20, 1.10 etc above); = is my "less than or equal here" that I'd like to change to be a cell value in, say Cell AL1 where I put the =; $A$3 is my value that I use (the 1.50 used in the above example-I can do this!); the "LE" is less than or equal etc. (I'll probably need an LE, LT, EQ, GE, GT). Any ideas on approaching would be appreciated as the Excel FIND doesn't do value comparisons and I'd like to make my comparisons variable by COLUMN (AL etc), OPERATION (LE, LT etc.) and VALUE (1.50 etc.). TIA |
Thread Tools | |
Display Modes | |
|
|