=IF(C45"",if(Concatenate($AS$2,Row(),$AS$4,$AS$ 3),$AS$4,""),"")

Let's see if I understand what you're trying to "say" with that formula.

Let's assume the formula is entered on row 2 for the sake of the Row()
function.

If C45 is not blank then execute the inner IF. If C45 is blank (or empty)
return blank.

The inner IF:

If H2 = 0.4 return the string "=". If H2 is not = 0.4 return blank.

If that's what you're trying to do try it like this:

=IF(C45"",IF(COUNTIF(INDIRECT($AS$2&ROW()),""&$A S$4&$AS$3&""),$AS$4,""),"")

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
Ok. great idea... now here's what I just tried but I received the #Value
error:
=IF(C45"",if(Concatenate($AS$2,Row(),$AS$4,$AS$3 ),$AS$4,""),"")
where C45=04/23/2010 (a date)
$AS$2="H" (column)
Row() = Current row looking at (I guess I could change C45 to be the same)
$AS$3=0.4 (the value I'm looking to compare to that I entered for a
search)
$AS$4)="=" (the operational compare symbols)
Upon using the Evaluation Formula I get to
IF(TRUE,IF("H45=0.4",$AS$4,""),"") with the next Evaluate step showing
the
#VALUE! error with IF(TRUE,#VALUE!,"").
All "AS" cells [AS2, AS3 and AS4] are formatted as General including the
cell holding the above formula.
What am I doing wrong here?
TIA!!

"T. Valko" wrote:

See if this points you in the right direction.

You want to identify numbers in a range of cells that are 50.

E1 =
F1 = 50

A1 = 50
A2 = 99
A3 = 44
A4 = 66
A5 = 50.5

Enter this formula in B1 and copy down to B5:

=COUNTIF(A1,CONCATENATE("",E$1,F$1,""))

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
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



.